Learn about the many ways you can customize and format your PivotTables in Excel, going beyond the default bland appearance. Discover how to use specific formatting tools found on the Design tab, allowing you to make your data more visually appealing and easier to read.
Key Insights
- PivotTables in Excel have a default appearance that lacks color and borders, but this can be changed using various formatting tools.
- Standard worksheet formatting tools, such as changing font and cell color, can be applied to PivotTables.
- Specific PivotTable formatting tools found on the Design tab can further customize the appearance, including options for subtotals, grand totals, and report layout.
- The Design tab allows you to choose whether to show column and row headings, and whether to have banded columns and rows for easier readability.
- PivotTable styles, which include groups of colors and borders, change based on your Style Options choices, and can be previewed before applying.
- Changes to the PivotTable, such as adding or rearranging fields, will reset column widths and row heights to default, so it's best to make these adjustments after finalizing the PivotTable layout.
Pivot Format Tools
-
PivotTable Styles
Pre-built color and border combinations in Design tab.
-
Number Format
Right-click value > Value Field Settings > Number Format.
-
Banded Rows
Toggle to add subtle row striping for readability.
-
Subtotals
Show or hide subtotals at each grouping level.
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.
