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
    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
    Application.ScreenUpdating = True
End If
End Sub

Download excel *.xlsm file

Normalize data.xlsm