Author: Oscar Cronquist Article last updated on February 25, 2018

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.

The following animated picture demonstrates the macro. To view macros in your workbook simply press Alt + F8 then click on NormalizeData macro and click on "OK" button to start the macro.

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