Repeating Values in Pivot Tables

Often I’m using a PivotTable to aggregate some data to use elsewhere. I’ll take a PivotTable that looks like this

and make it look like a grid so that I can copy and paste it somewhere else. To do that, I first go to the PivotTable Options – Display tab and change it to Classic PivotTable layout.

Then I’ll go to each PivotItem that’s a row and remove the subtotal

and check the Repeat item labels checkbox.

And I get a PivotTable that’s ready for copying and pasting.

After about 50 times of doing that, I got sick of it. Now I just run this code.

10 thoughts on “Repeating Values in Pivot Tables

  1. Note that you don’t have to change it to Classic View to do this. Just go Design/Report Layout/Show in Tabular Format. At which stage the Report Layout menu ‘helpfully’ vanishes. So then you need to click on it again, and select Repeat All Item Labels.

    But I too use code.

  2. It is incredible how microsoft makes our life harder.
    Pivot tables are now so much more difficult to use. It’s a shame.
    Why is there not a default format for pivots just to work as it always did? Not all pivots are to do fancy presentations.
    Thanks for sharing.

  3. How nice to see! In my personal custom tab in Excel 2010 a button runs this macro:

    Find the differences. Default I don’t repeat the row labels, but you could add the line pt.RepeatAllLabels xlRepeatLabels. After installing I changed the Sub into a Function so it won’t show in Alt-F8.
    Hope this helps.
    – Frans

  4. Hi

    I am trying to use this code in Excel 2007 but give error “Runtime error 438”. But it works fine in excel 2010. Please let me know if any workaround for 2007?

    Sub PivotGrid()

    Dim pt As PivotTable
    Dim pf As PivotField

    On Error Resume Next
    Set pt = ActiveCell.PivotTable
    On Error GoTo 0

    If Not pt Is Nothing Then
    With pt
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    End With

    For Each pf In pt.PivotFields
    If pf.Orientation = xlRowField Then
    pf.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
    pf.RepeatLabels = True
    End If
    Next pf
    End If

    End Sub

  5. Raghu: I don’t have 2007 to test on anymore. If you record a macro in 2007 that does all this stuff, you should be able to see what syntax changed between the two versions. It has to be one of: InGridDropZones, .RowAxisLayout, .Subtotals, or .Orientation. If you can’t figure it out, paste the recorded macro in a comment here (don’t forget the <code> tags) and someone will see the difference.

  6. I can not find a way to repeat the row labels (for the values, i.e. revenue, sales volume etc.) in my PT
    I’ve already tried all the simple fixes for this issue.
    FYI – my data is OLAP

  7. Quite similar functionality can be found in an addin that integrates into the Pivot table ribbon and is freely available via the website link. For the classic table layout the “Colorbands” button allows some nice coloring for the currently selected rowfields. Unfortunatley there is no documentation available for this addin. Some of the buttons have extra functionality by pressing the Shift or Ctrl key when clicking the ribbon control.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.