Change Pivot Table to Outline Layout With VBA

When you create a new pivot table in Excel, it uses the Compact Layout, by default. That layout has all the row fields in a single column, with each field slightly indented from the previous field. You can read more about the Compact Layout on my Contextures website.

NOTE: If you have my PivotPower Premium add-in installed, you can select a layout in your Preference Settings. That makes it easy to apply all your favourite settings to a new pivot table, or any existing pivot tables.

Compact Layout

Here’s what a pivot table looks like in Compact Layout, when it is created. All the row fields are in Column A, and the heading says “Row Labels”

Outline Layout

You can manually change the pivot table settings, so it uses the Outline Layout instead, use change it with a macro, like the one below. I prefer the Outline Layout, or Tabular Form Layout, because they put each row field in a separate column, and show the field names, Customer and Date.

Here is the same pivot table in Outline Layout.

The Tabular Form Layout is slightly different, with the row labels in separate columns, but all starting in the same row.

Change the Layout Manually

If you want to change the layout manually, follow these steps:

1. Select a cell in the pivot table.
2. On the Ribbon, under the PivotTable Tools tab, click the Design tab.
3. At the left, in the Layout group, click the Report Layout command
4. Click the layout that you want to uses, e.g. Show in Outline Form

The pivot table will change to the selected layout.

Change the Pivot Table Layout With a Macro

If you want to change a pivot table’s layout with programming, use the following macro, which applies the Outline Layout to the first pivot table on the active sheet.

There are other code examples on the Excel Pivot Tables Report Layout VBA page on my Contextures website. You can also download the sample file there, with all the code examples for changing the pivot table layout, or determining the current layout.

Sub ChangeToOutline()
Dim pt As PivotTable
On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)

If Not pt Is Nothing Then
    pt.RowAxisLayout xlOutlineRow
Else
    MsgBox "No pivot tables on this sheet"
End If

End Sub

__________________