How to design your PivotTable

We are going to use the following dataset as our example:

When we create our PivotTable, we have a few areas where we can slot in the fields from the source data that we want to use:

The basic options we have are to decide where each field from the source data shows up. As a reminder, the ways to include fields are:

• Filters – decide which items in the source data are used in the pivot at all
• Rows/Columns – labels; each unique value is shown once
• Values – data; summarised into a single value with a method of calculation we specify

For any given summary, the only design decision we really have at this stage is whether to put things as row labels or column labels. This is driven by preference, but in general I would say to use row labels for fields with lots of different values, and columns only for fields with a small number. We don’t want the user to have to scroll in two directions if possible as this makes the pivot very hard to read.

Here’s a simple design for a possible report pivot:

Here we have used the Account names field as a row label, a grouped version of the Date field as a column label, and the NET field as the values.

Things get more interesting however if we choose to include multiple row labels. Taking the dates out for a moment, here’s a look at how adding the Approver initials as a secondary row label looks:

We see each Account name, with a subtotal at the top, followed by a listing of each approver and their individual total. Note that all the labels appear in a single column.

Incidentally, you can swap the order of the fields in the above menu to reverse the hierarchy, i.e. to make a listing of approvers broken down by account names instead of vice versa.

Shaking up the layout options

If you don’t like the default layout option, you actually have quite a lot of control over it – from the special PivotTable Design ribbon menu:

This is one of those special contextual menus that only appears while you have the pivot selected. There are some Styles options that affect the colouring over on the right, but we’re going to focus on the first box of options, the Layout options.

Subtotals

This lets you decide if group-by-group subtotals are shown at all, and if so where they appear relative to the group.

“Include Filtered Items in Totals” is greyed out here because I didn’t choose to add my data to Excel’s data model when creating my pivot; if you do use that option, then that button would allow your totals to always reflect the total of the entire group, even if a filter is applied.

Grand Totals

This is pretty self-explanatory. Turning off grand totals can be a real benefit for busier PivotTables, or in cases where a summary function is being used where grand totals don’t really make sense (like an average).

Report Layout

Here’s the real value option. “Compact form” is the Excel default, showing all layers of row labels in a single column.

Here’s our same example from before in Outline form:

And again in Tabular form:

In both these cases, the two layers of row labels are each shown in their own column (if you’ve been using PivotTables for a long time, this might be familiar as the original layout used by PivotTables).

Depending on the situation, this layout can be clearer, or it can be busier and more clunky. But knowing where the option to switch lives is definitely worth it, so you can experiment as appropriate.

The other options on this menu let you decide whether the higher-order row labels are repeated for each subsequent row or (as we’ve done above) only included once.

Blank Rows

This rather esoteric option lets you space out your data, if you really want to:

I personally think this is a big waste of space, but it’s there if you want it.

Combining all these options can be very powerful – for example, if we want to show the account codes as well as the account names, we can do that by using a Tabular layout, and removing the subtotals to prevent duplication:

This rather esoteric option lets you space out your data, if you really want to:

Finally, it’s worth noting that from Excel 2019 onwards, you can actually update which layout settings are used in your PivotTables by default, from File => Options => Data => Edit Default Layout:

You can even import the settings from a pivot that you got just the way you want it, to be reused for later.

• You can see the data and experiment with different layouts yourself

Related resources

• Previous post - Spreadsheet Competency Framework: Five years on
• TOTW index
• Next time – Custom data types!