PivotTable layouts in Excel - Step-by-Step Guide

Introduction

PivotTable layouts help you organize and display your summary data in different ways. They let you change how rows, columns, and subtotals appear to make your data easier to understand.

When you want to see your sales data grouped by product categories in rows or columns

When you need to compare monthly totals side by side in a clear format

When you want to show or hide subtotals for better report clarity

When you want to change the look of your PivotTable to fit a report style

When you want to switch between compact, outline, or tabular views for easier reading

Steps

Step 1: Click

- any cell inside the PivotTable

The PivotTable Tools tabs appear in the ribbon

💡 Make sure your cursor is inside the PivotTable to see the options

Step 2: Click

- Design tab under PivotTable Tools

Layout options for the PivotTable become visible

Step 3: Select

- Report Layout dropdown in the Layout group

A menu with layout choices appears: Compact, Outline, Tabular

Step 4: Click

- Compact Form

PivotTable changes to show data in a compact layout with nested row labels

Step 5: Click

- Report Layout dropdown again

Menu reappears for another layout choice

Step 6: Click

- Outline Form

PivotTable changes to show each field in its own column with subtotals at the bottom

Step 7: Click

- Report Layout dropdown again

Menu reappears

Step 8: Click

- Tabular Form

PivotTable changes to show each field in its own column with subtotals at the bottom

Step 9: Click

- Subtotals dropdown in the Layout group

Options to show or hide subtotals appear

Step 10: Select

- Do Not Show Subtotals

All subtotals disappear from the PivotTable

Before vs After

Before

PivotTable shows sales data in a compact layout with nested row labels and subtotals at the bottom

After

PivotTable shows sales data in a tabular layout with each field in its own column and subtotals at the bottom

Settings Reference

Report Layout

📍 Design tab > Layout group > Report Layout dropdown

Changes how row fields are arranged and displayed in the PivotTable

Default: Show in Compact Form

Subtotals

📍 Design tab > Layout group > Subtotals dropdown

Controls whether subtotals appear and where they are placed

Default: Show All Subtotals at Bottom of Group

Grand Totals

📍 Design tab > Layout group > Grand Totals dropdown

Controls whether grand totals appear for rows and columns

Default: On for Rows and Columns

Common Mistakes

Changing the layout without selecting a cell inside the PivotTable

The PivotTable Tools tabs do not appear, so layout options are not available

Click inside the PivotTable first to activate the PivotTable Tools tabs

Expecting subtotals to disappear after changing layout without adjusting Subtotals setting

Layout changes do not automatically hide subtotals

Use the Subtotals dropdown to hide or show subtotals explicitly

Summary

PivotTable layouts let you change how data is arranged and displayed for easier reading.

You can switch between Compact, Outline, and Tabular forms to suit your report style.

Remember to select a cell inside the PivotTable to access layout options in the Design tab.