When you create a pivot table, you have your choice of three layouts. In this video, we’ll look at each layout. Once you have a pivot table, you can change layouts using the Report layout menu, on the Design tab of the pivot table tools ribbon. By default, each new pivot table you create will use the Compact layout. But you can easily switch to Outline layout, or to Tabular layout. You probably noticed the pivot table changing when I applied a new layout, but you might be wondering what the differences are? Let’s look at each layout more closely. First, the Compact layout. The Compact layout is the newest layout in Excel, and is enabled by default for all new pivot tables. Its primary purpose is to save horizontal space. So if you look closely at the first column, you’ll notice that both the Customer and Product fields are included in the same column. If I add another field, say “Category”, as the first row label, all three fields appear in the same column. When fields are grouped like this in the Compact layout, note that you can only filter on one field at a time. One more thing to note about the Compact layout is that the field headings are pretty much useless, so you may want to hide these. Now let’s look at the Outline layout. When I switch to the Outline layout, Customer and Product are placed into separate columns, and the structure of the pivot table resembles a traditional outline. Because the outline layout doesn’t group fields in a single column, each field can be filtered separately. The final layout option is the Tabular Layout, which is based on the original layout used by pivot tables up to Excel 2003. The Tabular layout is similar to the Outline layout in that all row labels appear in their own column. The Tabular layout works best when you want to copy the results of a pivot table to another worksheet, so you can analyze the data further. Once you’ve applied the tabular layout, you can enable an option to repeat labels at the bottom of the Report Layout menu. This fills in the data completely so you are ready to copy it to another location. There’s one last thing to notice. Pivot tables have an option to show subtotals at the top or bottom of a section. However, because of its structure, the Tabular layout always shows subtotals at the bottom of a section. The top option has no effect. If you want one field per column, and subtotals at the top, use the Outline layout.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
