Debra has a great post and video about normalizing data for excel pivot table. This post describes a macro that normalizes data for excel pivot tables.

VBA macro

  1. Press Alt+ F11
  2. Right click your workbook in project explorer
  3. Click Insert
  4. Click Module
  5. Paste macro code below to module
  6. Exit VB Editor
Sub NormalizeData()
Dim Rng As Range
Dim WS As Worksheet
On Error Resume Next
Set Rng = Application.InputBox(Prompt:="Select a range to normalize data" _
, Title:="Select a range", Default:=ActiveCell.Address, Type:=8)
On Error GoTo 0
If Rng Is Nothing Then
Else
    Application.ScreenUpdating = False
    Set WS = Sheets.Add
    i = 0
    For r = 1 To Rng.Rows.Count - 1
        For c = 1 To Rng.Columns.Count - 1
            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
            i = i + 1
        Next c
    Next r
    WS.Range("A:C").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End If
End Sub

Download excel *.xlsm file

Normalize data.xlsm

Related posts:

Normalize data, part 2

Toggle a macro on/off using a button

Excel vba: Save invoice data

Excel vba: Edit invoice data

Change chart data range using a drop down list (vba)