Learn how to format a Pivot Table.
Formatting Pivot Tables
The appearance of a Pivot Table is, by default, pretty bland. There are no colored fills, no borders, and with the exception of bolding headings in multiple-tiered column and row lists of data from your source fields, there isn’t really any formatting applied at all. But it doesn’t need to stay this way!
Any formatting tools you’d typically use for your worksheet content can be applied to your PivotTables – you can apply fill colors to cells, change fonts, font sizes, and apply bold and italics to any cell content. You can apply any number, text, and date formats you need, too – regardless of formatting that was or wasn’t applied to the source data.
Assuming you know how to apply those formatting features from the Home tab, I want to focus on some formatting tools that are specific to PivotTables – the tools found on the Design tab while you’re in any Pivot Table.
Working from left to right, your options are:
Subtotals. Choose to show them or not, and whether they appear above or below your data. This pertains to functions such as sums, averages, and counts – all the functions that are chosen for your Values box fields.
Grand Totals. Again, choose whether or not to see them, and if you’re going to see them, where they’ll appear.
Report Layout. Choose from Compact, Outline, and Tabular layouts, and choose whether or not your headings and labels appear over and over again if you chose Outline as your layout.
Blank Rows. This gives you the chance to spread things out and put a blank row between sections of your Pivot Table.
Your Pivot Table Style Options include whether or not you see your column and row headings, and if you want banded – filled – columns and rows. Banding is typically applied to every other row or column or both, to make it easier to read across a series of columns without losing your place visually.
Based on your Style Options choices, the Pivot Table styles – groups of colors and borders – change to allow for banding. Click the drop arrow to see thumbnails of each style and click on the one you want to apply. You can keep “trying them on” until you like what you see.
While the layouts you choose can tighten or loosen your overall Pivot Table layout, you can also make adjustments to row heights and column widths – just as you would any other worksheet. Just remember that when you make any changes to the Pivot Table – adding or rearranging fields, changing a field from being in the Rows box to Columns, changing functions for a Values field – all column widths and row heights are automatically reset to the defaults. So put this sort of formatting off until you’re sure there won’t be any changes to your Pivot Table – that way the widths and heights will remain in place until and unless you make any changes in the future.