How to Change Pivot Table Layout in Excel

A default Pivot Table in Excel is powerful, but let's be honest - it often looks cluttered and is hard to read. Knowing how to change its layout transforms that jumbled block of data into a clean, professional-looking report. This guide will walk you through exactly how to control your Pivot Table's structure, from switching between major layouts to the small tweaks that make a big difference.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First, Understand Excel's Default Layout: Compact Form

When you first create a Pivot Table, Excel automatically arranges it in Compact Form. This layout is designed to save horizontal space by placing multiple row fields into a single column. You'll recognize it by the indented, tree-like structure it uses for an inner field (like "Product") underneath an outer field (like "Region").

While this is great for conserving screen real estate, it can be difficult to read and even harder to use as a data source for other formulas or charts. The merged-cell feeling of having "Region" and "Product" in the same column quickly becomes a limitation.

The Three Main Pivot Table Report Layouts

Excel gives you three primary layouts to structure your report. You can find these options by clicking anywhere inside your Pivot Table, which brings up the PivotTable Tools tab in the ribbon. From there, navigate to Design > Report Layout.

Let's break down each one.

1. Compact Form (The Default)

As mentioned, this is the default layout. It keeps related data from spreading out too wide, making it useful for dashboards or when you're trying to fit a lot of information on one screen.

• Best for: A quick overview, dashboards, and conserving horizontal space.
• Key Feature: Multiple row fields are displayed in a single column ("Column A" in our example).

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

2. Outline Form

Outline Form starts to give your data some breathing room. It separates each row field into its own column, creating a more traditional, hierarchical outline. The outer field ("Region") gets its own column, and the inner field ("Product") gets the next one.

One key feature of Outline Form is that subtotals appear at the top of each item grouping by default.

• Best for: Displaying a clear hierarchy and preparing your data for the Classic PivotTable view (more on that later). It's a good middle ground between compact and fully tabular.
• How to apply it: Click on your Pivot Table, go to Design > Report Layout > Show in Outline Form.

3. Tabular Form

Tabular Form is often the go-to layout for creating clear, easy-to-read reports. It places every field in its own column and arranges the data like a standard database table. The subtotals are shown at the bottom of each group by default, which many users find more intuitive for summing up categories.

This layout is the cleanest for reports you plan to share and is the easiest to read if a stakeholder is unfamiliar with Pivot Tables. It’s also the best format if you need to copy and paste your summary data into another report or application, as its structure is simple and clean.

• Best for: Final reports, readability, and compatibility with other data tools.
• How to apply it: Click on your Pivot Table, go to Design > Report Layout > Show in Tabular Form.

Fine-Tuning Your Layout for Professional Reports

Switching between Compact, Outline, and Tabular layouts is a great first step, but the real magic comes from the smaller adjustments that turn your report from functional to professional. These options are also found in the Design tab.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Repeating All Item Labels

One of the most common annoyances with Outline and Tabular layouts is the empty space. When "East" is the region for multiple products, Excel only shows "East" on the first line and leaves the cells below it blank. This can be confusing and creates problems if you try to sort or filter the data.

To fix this, simply have Excel fill in those blanks:

1. Select your Pivot Table.
2. Go to Design > Report Layout.
3. Click on Repeat All Item Labels.

This single click makes your report immensely more readable and usable. To turn it off, just select Do Not Repeat Item Labels from the same menu.

Adding or Removing Blank Rows

To visually separate different sections of your report, you can add a blank row after each major grouping (e.g., after all the "East" region data). This can improve readability by breaking up dense blocks of numbers.

• To add blank rows: Go to Design > Blank Rows > Insert Blank Line After Each Item.
• To remove them: Follow the same path and select Remove Blank Line After Each Item.

Showing or Hiding Subtotals and Grand Totals

Sometimes you need subtotals, and sometimes they just add clutter. Excel gives you full control over both subtotals (totals for inner fields, like "East Total") and grand totals (the total for the entire table).

Controlling Subtotals

Navigate to Design > Subtotals. Here you have three choices:

• Do Not Show Subtotals: Removes them entirely for a cleaner look.
• Show all Subtotals at Bottom of Group: The classic reporting style, best used with Tabular Form.
• Show all Subtotals at Top of Group: Default for Outline Form, can be useful for seeing the total before looking at the details.

Controlling Grand Totals

Similarly, go to Design > Grand Totals. You can choose to show grand totals for rows only, columns only, both, or turn them off completely. This is handy when you only need a column total (like total sales per month) but not a row total, or vice versa.

Advanced Tip: Using the Classic PivotTable Layout

For those who have been using Excel for years, the modern method of dragging fields into the four boxes at the bottom of the "PivotTable Fields" pane can feel restrictive. The "Classic" layout offers a more direct, drag-and-drop experience right on the Pivot Table itself.

Many experienced users prefer it because it feels faster and more intuitive for rearranging fields.

To enable it:

1. Right-click anywhere inside your Pivot Table and choose PivotTable Options...
2. Go to the Display tab.
3. Check the box that says Classic PivotTable layout (enables dragging fields in the grid).
4. Click OK.

Your Pivot Table will now have distinct, outlined areas for dropping rows, columns, and values directly, which can make re-organizing complex tables a breeze.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Putting It All Together: From Messy to Polished

Let's walk through transforming a standard, messy Pivot Table into a clear, presentation-ready report.

Imagine your boss asks for a Q4 sales report by region and by product category. You create a Pivot Table and get this default output:

It’s functional but not very readable. Here are the steps to clean it up:

1. Switch to Tabular Form: Click the table, go to Design > Report Layout > Show in Tabular Form. This immediately separates "Region," "Category," and "Product" into their own columns.
2. Repeat Item Labels: With the layout changed, you now have gaps. Go to Design > Report Layout > Repeat All Item Labels to fill in the regions and categories.
3. Adjust Subtotals: You might not need a subtotal for every product category. You can turn them off or configure them field by field. For this report, we'll hide them. Go to Design > Subtotals > Do Not Show Subtotals.
4. Format the Numbers: Finally, select your sales numbers, right-click, and choose Number Format... to format them as currency.

Which one would you rather hand to your boss? The second one is cleaner, more intuitive, and infinitely more professional.

Final Thoughts

Mastering Pivot Table layouts in Excel is a fundamental skill for anyone doing data analysis. By moving beyond the default Compact Form and using Tabular or Outline views combined with tweaks like repeating labels and strategic subtotals, you elevate your reports from raw data summaries to clear, actionable insights.

Spending hours manually adjusting layouts and wrangling data in spreadsheets is a common routine for many of us. We built Graphed to eliminate that entire process. Instead of creating a Pivot Table and then carefully stepping through layout options, you can just ask a question in plain English like, "Show me a table of Q4 sales by region and product" and get a perfectly formatted, live-updating report in seconds. It allows you to skip straight to the insights without all the manual setup.