Excel Pivot Table Layout Styles: Compact vs Outline vs Tabular Explained

Say you are trying to copy a pivot table’s output into a report that other people will read without touching Excel themselves, and the row labels come out stacked oddly, with subtotals in places nobody asked for and blank-looking cells where a category name should repeat. Nothing about the underlying data is wrong. You’ve just inherited whatever layout setting was active the last time someone built a pivot table on this machine, and it happens to be the one layout that looks the most confusing outside of Excel itself.

This comes up constantly because the three layout options — Compact, Outline, and Tabular — sit under Design, then Report Layout, a menu most people click once, pick something that looks reasonable, and never revisit. Below are the questions worth answering before you settle on one.

What actually changes when I switch between the three layouts?

All three layouts display the exact same underlying data and the same totals. What changes is purely how the row fields are arranged and labeled — nothing about the numbers themselves is recalculated or altered.

Compact layout nests every row field into a single column, indenting each level so that a Region-then-Product hierarchy shows Product names tucked underneath their Region, with a small expand/collapse arrow next to each group. It’s the default in modern Excel, and it’s the most space-efficient of the three.

Outline layout gives each row field its own column, but it still groups items visually and places subtotals directly above each group rather than below it, with a blank row separating one group from the next.

Tabular layout also gives each row field its own column, but it repeats every field’s label on every single row, no indentation, no blank separator rows, and subtotals sit at the bottom of each group instead of the top. This is the layout that looks the most like a flat, traditional spreadsheet table.

Which layout should I use if I need to paste this into a report or share it with someone outside Excel?

Tabular, almost without exception. Because it repeats every label on every row and skips the blank separator rows that Outline inserts, a Tabular pivot table pastes into Word, PowerPoint, or a plain data extract in a form that reads correctly without any of Excel’s grouping logic attached to it. Someone opening that pasted table has no way to expand a collapsed group or interpret an indentation level, so a layout that depends on those visual cues to make sense is the wrong choice for anything leaving Excel.

Compact, by contrast, is built for staying inside Excel. The collapsible groups are genuinely useful when you’re the one navigating the report, but that interactivity doesn’t survive a copy-paste, and what’s left behind is a single indented column that looks unfinished to anyone who wasn’t there to expand it first.

Why does my Tabular layout still have subtotals I don’t want?

Switching to Tabular changes where subtotals sit, not whether they exist. If subtotals were already turned on before you switched layouts, they’ll still be there — now at the bottom of each group instead of the top, which is expected Tabular behavior, but not the same thing as removing them.

To get rid of them, go to Design, then Subtotals, and choose “Do Not Show Subtotals.” That setting is independent of the layout choice, so you can mix and match freely: Tabular with subtotals, Tabular without them, Compact with subtotals, and so on. If your report is meant to feed directly into another tool as a flat data source — say, a query pulling values in for further analysis — you’ll almost always want Tabular with subtotals off, since a subtotal row sitting in the middle of what should be a clean list of records will get pulled in as if it were a real record and throw off any downstream calculation.

What’s the point of Outline layout if Tabular does everything it does, minus the blank rows?

Outline earns its keep in one specific situation: when you want each row field visibly separated into its own column, but you also want a visual break between groups to make a printed or on-screen report easier to scan at a glance. The blank row between groups that seems like clutter in a data-extraction context can actually help a human reader’s eye track where one region’s numbers end and the next one begins, especially in a report with several nested fields and a lot of rows.

It’s also the layout some people default to out of habit from older versions of Excel, where Outline was closer to the standard behavior before Compact became the default. If you inherited a workbook built years ago and the layout looks unfamiliar, there’s a decent chance it was set up in Outline back when that was the norm, not because anyone made a deliberate choice for this specific report.

Outside of that print-friendly, human-scanning use case, there’s rarely a strong reason to pick Outline over the other two — it sits in a middle ground that’s less compact than Compact and less clean for data extraction than Tabular.

Does the layout choice affect how grouping or filtering works?

No — grouping, filtering, sorting, and every calculation option behave identically no matter which of the three layouts is active. Layout is purely presentational. You can switch between Compact, Outline, and Tabular as many times as you like while building a report, and nothing about your Base Field for a running total, your subtotal settings, or your filter selections will change as a side effect.

The one place layout does interact with something functional is copy-paste behavior, as covered above, and readability for anyone viewing the report without interacting with it. If a pivot table needs to stay fully interactive and only you or people comfortable with Excel will ever touch it, the layout choice is close to a matter of preference. If it’s headed anywhere else — a printed handout, a slide, a plain data table feeding another process — the choice stops being cosmetic and starts determining whether the output makes sense to the person receiving it.

Can I set one of these layouts as my default so I don’t have to change it every time?

Yes. Go to File, then Options, then Data, and look for “Edit Default Layout” under PivotTable options — the exact wording varies slightly by Excel version. From there you can set your preferred report layout, along with a handful of other defaults like subtotal placement, so every new pivot table you build starts in the layout you actually want instead of whatever Excel ships with.

This is worth doing the moment you notice yourself manually switching layout on every new pivot table you create. A five-minute setting change once saves that repeated click every single time afterward, and it removes one small but recurring source of inconsistency if multiple people build pivot tables in the same shared workbook.

Quick Comparison

If you’re not sure which one fits your situation, ask what happens to this report the moment it leaves your screen. Staying in Excel and staying interactive points to Compact. Getting printed or scanned by a reader points to Outline. Getting pasted, exported, or fed into another process points to Tabular. That one question resolves the decision faster than weighing the visual differences on their own.