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 click on NormalizeData macro and click 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, click 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
Copy VBA code below.
Press Alt+ F11 to open the Visual Basic Editor.
Right-click on your workbook in the Project Explorer.
Click Module to insert a code module to your workbook.
Paste code to module.
Exit VB Editor and return to Excel.
Note, make sure you save the workbook with the file extension *.xlsm next time you save the workbook. This allows you to attach the code to the workbook which is great the next time you open the workbook and want to use the macro.
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.
'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
'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
'Continue with next row
'Adjust column widths
'Show changes to the user
Application.ScreenUpdating = True
Download Excel file
If your data is concatenated to a cell then this article may interest you: