Contextures

Pivot Table Report Layouts

In Excel, Pivot tables have a defined basic structure, called a Report Layout (Form). In a new installation of Excel, pivot tables are in Compact Layout by default. See how to change to Outline or Tabular layout, and compare the features of each layout type. Or, change the default report layout, in newer versions of Excel,

NOTE: For macros to change the report layout, see Pivot Table Layout VBA.

Pivot Table Layout

In Excel, Pivot tables have a defined basic structure, called a Pivot Table Report Layout, or Pivot Table Form.

On this page, you'll find information about the 3 types of pivot table report layouts:

- Compact, Outline, Tabular

With the following details about each report layout type:

  • report layout examples
  • features and limitations
  • when to use each layout type

More Pivot Layout & Formatting Tips

There are many other ways to change pivot table layout and formatting, so check out the following pages:

  • Pivot Field Layout Changes: Add or remove fields in pivot table. Move fields to different locations in pivot table. Change pivot field headings. Show Value headings at the left, with row labels
  • Pivot Table Format: Apply formatting scheme from PivotTable Styles gallery. Create custom PivotTable Style. Copy custom styles to different Excel file. Change pivot table labels.
  • PivotTable Field List: Use the pivottable field list to add, remove and move pivot fields. Sort the field list, so it's easier to find fields in a long list.
  • Pivot Field Settings: Hide and show subtotals. Show items with no data. Repeat all item labels for pivot field. Insert blank rows for spacing. Sort pivot items. Show new items in drop down.
  • Pivot Table Options: Change pivot table name. Change value for all error cells. Show zero in empty cells. Show or hide Values row. Set pivot table layout and formatting options.

Report Layouts - Introduction

When you create a new pivot table, it is automatically formatted with the Compact Form layout, by default.

After you create a pivot table, you can change to one of the other report layouts, at any time.

Three report layouts are available for pivot tables, and there are details on each type below:

-- Compact Form

-- Outline Form

-- Tabular Form

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

Change Pivot Table Report Layout

After you create a pivot table, you can change it to a different report layout, at any time.

Follow these steps, to change the layout:

  1. Select a cell in the pivot table.
  2. On the Ribbon, click the Design tab
    • In some versions of Excel, Design is under the PivotTable Tools 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 report layout command

Compact Form

When Excel is freshly installed, the default report layout for a pivot table is Compact Form, shown below.

In the pivot table shown below, there are two fields in the Row area:

  1. Customer - Corner Cabin, Food Franchise, Mega Mart
  2. Date - Jan, Feb, Mar

Both Row fields are shown in column A, with the Date items slightly indented, below each Customer name.

pivot table compact layout

Why Use Compact Report Layout?

The Compact Form layout may be useful when you:

  • want to reduce the pivot table width
  • don't need to about see the individual Row field headings

Compact Form 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

Compact Form 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

Compact Form 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.

In the pivot table shown below, there are two fields in the Row area:

  1. Column A: Customer - Corner Cabin, Food Franchise, Mega Mart
  2. Column B: Date - Jan, Feb, Mar

NOTE: In this pivot table, column A has been narrower, so it spills slightly into column B

pivot table outline layout

Why Use Outline Report Layout?

The Outline Form layout may be useful when you:

  • want to show all the field names as heading labels
  • aren't concerned about the width of the pivot table

Outline Form 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.

Outline Form Row Fields

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

Outline Form 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

In the pivot table shown below, there are two fields in the Row area:

  1. Column A: Customer - Corner Cabin, Food Franchise, Mega Mart
  2. Column B: Date - Jan, Feb, Mar

In Tabular Form, multiple fields can start in the same row

  • Row 4: Customer 1 begins, Date begins
  • Row 8: Customer 2 begins, Date begins
  • Row 12: Customer 3 begins, Date begins

pivot table tabular layout

Why Use Tabular Report Layout?

The Tabular Form layout may be useful when you:

  • want to show all the field names as heading labels
  • aren't concerned about the width of the pivot table
  • want to reduce the number of pivot table rows

Tabular Form 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.

Tabular Form Row Fields

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

Tabular Form 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.

Change Default Pivot Table Report Layout

To change the automatic report layout in new pivot tables, you can change a pivot table default setting, in Excel 2019, or Excel for Office 365. Then, when you create new pivot tables, they will automatically have the report layout that you prefer.

Follow these steps to change the default report layout setting.

  • At the top of Excel, click the File tab
  • At the left, click Options
  • You might need to click More, then click Options
  • In the Excel Options window, in the Category list at the left, click Data
  • In the Data Options section, click Edit Default Layout button

Edit Default Layout button

Edit Default Layout dialog box

  • In the Edit Default Layout dialog box, at the left, click the drop down arrow for Report Layout
  • Click on the Report Layout that you want to use as the default, for all new pivot tables
  • Click OK, to close the Edit Default Layout window
  • Click OK, to close the Excel Options window.

select default pivot table report layout

Change Pivot Table Layout

Create a quick pivot table with Excel's Recommended Pivot Tables feature. Then, make simple changes to the layout, to get a different view of your data. This video shows the steps, and there are written instructions on the Pivot Table Field Layout page.

Video Timeline

  • 0:00 Introduction
  • 1:04 Create an Instant Pivot Table
  • 1:26 Pivot Table Field List
  • 2:12 Add and Remove Fields
  • 2:51 Add a Column Field
  • 3:31 Add Another Value Field
  • 4:11 Move the Value Fields
  • 4:55 Reposition the Value Fields
  • 5:15 Change Your Pivot Table

Change Report Filters Layout 

Report filters let you focus on a specific part of your data, when it is summarized in a pivot table. When you add Report Filters, by default they appear in a single column, at the top of the pivot table.

You can change to a multi-column layout, to reduce the number of rows that the Report Filters use.

Watch this video to see how to change the vertical Report Filter layout to horizontal.

. The written instructions are on the Pivot Table Report Filters page.

Get 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.

More Tutorials

Pivot Table Layout VBA

FAQs - Pivot Tables

Pivot Table Intro

Summary Functions

Clear Old Items in Pivot Table

About Debra

 

Last updated: January 15, 2022 2:45 PM