Contextures

Pivot Table Report Layouts

New Pivot tables are in Compact Layout by default. See how to change to Outline or Tabluar layout, and compare the features of each layout type. For macros to change the layout, see Pivot Table Layout VBA.

NOTE: If you have my PivotPower Premium add-in installed, you can select a layout in your Preference Settings.

Report Layouts

When you create a new pivot table, it is automatically formatted with the Compact Form layout, by default. After you create the pivot table, you can change to one of the other layouts. Three layouts are available for pivot tables:

Compact Form

Outline Form

Tabular Form

NOTE: There is no way to change this setting, to make one of the other layouts the default.

Watch this short video, to see the 3 Report Layout options, and the different settings available in each layout. The layout details are described in the sections below

To change the layout:

  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.

pivot table without running totals

Compact Form

The default report layout for a pivot table is Compact Form, shown below. There are two Row fields -- Customer and Date.

The Compact Form layout may be useful when you want to reduce the pivot table width, and aren't concerned about the Row field headings.

pivot table compact layout

Row Labels

  • In Compact Form, each Row label is in a separate row.
  • The Row field label is always above the labels for the inner fields
  • Each Row label is slightly indented, from the field above it, to differentiate the fields
  • The Row labels cannot be repeated.
  • The indentation can be changed, in PivotTable Options, on the Layout & Format tab

    pivot table compact layout

Row Fields

  • In Compact Form, all the Row fields are in a single column.
  • There is a generic heading, Row Labels, in the Row Field column

Subtotals

  • In Compact Form, Subtotals can be shown at the Top or Bottom of each group.
  • This setting affects all the Row fields
  • For Column fields, Subtotals are always shown at the Bottom
  • The Row label remains at the top, even if Subtotals are at the bottom.

Outline Form

In Outline Form, each Row field is in a separate column, as you can see in the pivot table below. There are two Row fields -- Customer and Date.

The Outline Form layout may be useful when you want to show all the field names as heading labels and aren't concerned about the width of the pivot table

pivot table outline layout

Row Labels

  • In Outline Form, each Row label is in a separate row.
  • The Row field label is always above the labels for the inner fields
  • The Row labels can be repeated.

Row Fields

  • In Outline Form, each Row field is in a separate column.
  • Each Row field shows its name in the column heading

Subtotals

  • In Outline Form, Subtotals can be shown at the Top or Bottom of each group.
  • This setting affects all the Row fields
  • For Column fields, Subtotals are always shown at the Bottom
  • The Row label remains at the top, even if Subtotals are at the bottom

Tabular Form

In Tabular Form, each Row field is in a separate column, as you can see in the pivot table below. There are two Row fields -- Customer and Date. The Row labels are not in a separate row.

The Tabular Form layout may be useful when you want to show all the field names as heading labels and aren't concerned about the width of the pivot table but want to reduce the number of rows

pivot table tabular layout

Row Labels

  • In Tabular Form, Row labels for the outer fields are on the same row as the first label for the related inner fields.
  • The Row labels can be repeated.

Row Fields

  • In Tabular Form, each Row field is in a separate column.
  • Each Row field shows its name in the column heading

Subtotals

  • In Tabular Form, Subtotals can only be shown at the Bottom of each group.
  • This setting affects all the Row fields
  • For Column fields, Subtotals are always shown at the Bottom.

Download the Sample File

To follow along with the tutorials, you can download the pivotlayout.zip file. The zipped file is in xlsx format, and does not contain macros.

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search Contextures

Search Contextures Sites

More Tutorials

Pivot Table Layout VBA

FAQs - Pivot Tables

Pivot Table Intro

Summary Functions

Clear Old Items in Pivot Table

Search Contextures Sites

 

 

 

30 Excel Functions in 30 Days

 

Excel Data Entry Popup List

 

 

 

 

Excel UserForms for Data Entry

 

Last updated: July 10, 2016 1:14 PM