To be able to use a Pivot Table the source data you have must be arranged in way that a Pivot Table can handle. For example, the data set above in cell range B2:F5 has headers both horizontally and vertically. In other words, the data set is a two-dimensional table.

Months are arranged horizontally in row 2 and items are arranged vertically in column B, a Pivot Table can't work with data arranged in this way. The data must be arranged row by row meaning values on the same row belong together. Debra has a great post and video about normalizing data for excel pivot table.

This article describes and demonstrates a macro that normalizes data so you can use it in an Excel Pivot Table, meaning it rearranges a dataset layout from two dimensional to row by row. The following animated picture demonstrates the macro.

To view macros in your workbook simply press Alt + F8 to open the Macro dialog box, then press with left mouse button on NormalizeData macro and press with left mouse button on "OK" button to start the macro.

Now you will be prompted for a cell range that the macro can work with and rearrange the data, press with left mouse button on OK button when finished.

The macro creates a new worksheet and populates it with values from the cell range you selected.

Where to put the code

1. Copy VBA code below.
2. Press Alt+ F11 to open the Visual Basic Editor.
3. Press with right mouse button on on your workbook in the Project Explorer.
4. Press with left mouse button on Insert.
5. Press with left mouse button on Module to insert a code module to your workbook.
6. Paste code to module.
7. Exit VB Editor and return to Excel.

VBA code

The VBA code below contains comments so you know what each line does. A comment begins with a ' which is a single quote or apostrophe, you can safely remove the comments if you don't want them in your code.

'Name macro
Sub NormalizeData()

'Dimension variables and declare data types
Dim Rng As Range
Dim WS As Worksheet

'Enable error handling
On Error Resume Next

'Show inputbox so the user can select a cell range and save result to object Rng
Set Rng = Application.InputBox(Prompt:="Select a range to normalize data" _
, Title:="Select a range", Default:=ActiveCell.Address, Type:=8)

'Disable error handling
On Error GoTo 0

'Check if object variable Rng is empty
If Rng Is Nothing Then

'If object variable Rng is not empty then continue with the following lines
Else

    'Don't show changes on screen
    Application.ScreenUpdating = False

    'Add a worksheet and save it to object variable WS
    Set WS = Sheets.Add

    'Save value 0 (zero) to variable i
    i = 0

    'Iterate through 1 to the number of rows in the cell range the user selected
    For r = 1 To Rng.Rows.Count - 1

        'Go through 1 to the number of columns in the cell range the user selected
        For c = 1 To Rng.Columns.Count - 1

            'Save values from cell range to worksheet
            WS.Range("A1").Offset(i, 0) = Rng.Offset(0, c).Value
            WS.Range("A1").Offset(i, 1) = Rng.Offset(r, 0).Value
            WS.Range("A1").Offset(i, 2) = Rng.Offset(r, c).Value

            'Add 1 to variable i
            i = i + 1

        'Continue with next column 
        Next c

    'Continue with next row
    Next r

    'Adjust column widths
    WS.Range("A:C").EntireColumn.AutoFit

    'Show changes to the user
    Application.ScreenUpdating = True
End If

'Stop macro
End Sub

Get the Excel file

Normalize-data.xlsm

If your data is concatenated to a cell then this article may interest you:

Normalize data, part 2