Pivot Table Report Layouts

A Pivot Table is a great tool for summarising and analysing data in Excel. Choosing the right layout can help you read the Pivot Table more easily and share it with others. In this article, you will learn all about Pivot Table Report layouts.

Key Takeaways

• Pivot Tables offer three report layouts: Compact, Outline, and Tabular.
• Changing the layout only changes the appearance, not the calculations.
• Compact Form saves space and is the default layout in Excel.
• Outline and Tabular Forms are better for reports and further analysis.
• You can switch between layouts anytime from the Design tab.

 

What Are Pivot Table Report Layouts?

A Pivot Table report layout determines how the row fields display in the report. It changes the appearance of the Pivot Table without affecting the underlying data or calculations. Based on your requirements, you can easily switch between layouts at any time.

The three available layouts are:

• Compact Form
• Outline Form
• Tabular Form

COMPACT LAYOUT

Compact Form is the default Pivot Table layout in Excel. It displays multiple row fields in a single column.

Advantages: Optimizes for readability; Keeps related data in one column

Disadvantages: If you copy and paste the data into a new worksheet, it will be harder to do further analysis

OUTLINE LAYOUT

Outline Form displays each row field in a separate column while preserving the hierarchy of the data.

Advantages: Includes Field headers in each column; Can Repeat All Item Labels; Can reuse the data of the Pivot Table to a new location for further analysis; Classic Pivot Table style

Disadvantages: Takes too much horizontal space

TABULAR LAYOUT

Tabular Form displays the Pivot Table in a traditional table format with each row field in its own column.

Advantages: Includes Field headers in each column; Can Repeat All Item Labels; See all data in a traditional table format used in Pivot Tables since their invention; Can reuse the data of the Pivot Table to a new location for further analysis

Disadvantages: Takes too much horizontal space; Subtotals can never appear at the top of the group

 

How to Change the Report Layout

Changing the report layout does not modify your source data or the values in the Pivot Table. It only changes how the information is organised and displayed on the worksheet.

• Click anywhere inside the Pivot Table.
• Go to the Design tab on the Ribbon.
• In the Layout group, click Report Layout.
• Choose one of the available layouts.

 

Which layout to use?

• Use a compact layout if you want to save space and have a quick summary.
• Use an outline layout if you want to view field hierarchy clearly.
• Use a tabular layout if you are creating reports, printing, or exporting data.

 

Frequently Asked Questions

What is the difference between Compact, Outline, and Tabular layouts?

Compact uses a single column for multiple fields, Outline separates each into its own column, and Tabular adds repeated item labels for better readability.

How to change the Pivot Table layout?

To change the Pivot Table layout,

• Click inside the Pivot Table
• Go to the Design tab
• Select Report Layout
• Choose your preferred format

Which layout is best for printing or exporting data?

Tabular Form is the most suitable layout for printing or exporting. This is because it can clearly display all data in a flat structure.

How to make a default Pivot Table layout for all new Pivot Tables?

To make a Pivot Table layout the default one,

• Go to the File tab.
• Select Options
• Click on Data > Edit Default Layout
• Configure your preferred layout and settings for future Pivot Tables.

Why are my row labels nested in one column?

In the Compact Form layout, row labels are nested in one column. It is the default layout, and you can switch to Outline or Tabular if you prefer labels in separate columns.