You don’t have to stick with the default formats for your pivot tables. You can create a pivot table style with your own colours, and other formatting options that you like. Here’s how to get started, and a video with a simple formatting change that you can make.
Built-In Styles
There is a big collection of pivot table styles, and you might be satisfied with something from that collection.
For example, I like “Pivot Style Light 8”, so I’ll apply it to my pivot table.
Tweak the Built-In Styles
The style you apply might be almost perfect, but maybe you’d like to make a little tweak to it.
If I’m printing this pivot table, the grey shading on the subheading rows will make them hard to read. I’d like to get rid of that shading.
Can’t Modify the Built-In Styles
If I right-click on the style, to try to change it, the Modify command isn’t available. Those styles are locked in stone!
So, to make changes, you can click the Duplicate command, to create a Custom Style. Then, modify that Custom Style, with the formatting that you want.
But before you go to all that trouble, try something easier – it might help in some cases.
Change the Style Options
There are PivotTable Style Options that you can turn on or off, so try changing those, before you create a Custom Style.
- Select a cell in the pivot table
- Click the Design tab (under PivotTable Tools)
- In the PivotTable Style Options group, add or remove the checkmarks, for the Style Options:
- Row Headers
- Column Headers
- Banded Rows
- Banded Columns
When I removed the check mark for Row Headers, it took out the grey shading. It took out the Bold text too, but that’s fine. You could select the subheadings and their values, and make those Bold, with the commands on the Ribbon’s Home tab.
Create a Pivot Table Style
If you need more changes than the Style Options provide, then duplicate one of the built-in PivotTable Styles, and modify it. I’ll turn the Row Headers option back on, and make a duplicate of the Pivot Style Light 8 style.
As soon as you click the Duplicate command, the Modify PivotTable Style window opens.
- Give your style a name, e.g. MY PivotStyleLight8
- Next, click on one of the items in the list of Table Elements. I’ve clicked Row Subheading1, and its formatting details show in the bottom section of the window. (NOTE: Elements in Bold text have formatting applied)
- Click the Formatting button to change or add formatting, or click the Clear button, to clear the existing formatting. There are more details on my Pivot Table Formatting page.
- After you’re finished, click OK
And nothing changes! There’s one more step to do.
Apply the Custom Style
After you create a Custom Style, it isn’t applied automatically, to the active pivot table. It’s funny, because Excel makes other decisions without a second thought (I’m looking at you, default Compact Layout!)
Anyway, until they add a check box that lets us apply the Custom Style after creating it, we have to apply it ourselves.
- Select a cell in the pivot table
- Click the More button, at the bottom right of the PivotTable Styles list.
- In the Custom section, click on your new Custom Style
Now the pivot table uses that style, and shows the formatting that you created for that Custom Style.
Video: How to Create a Pivot Table Style
Here’s a short video that shows another example of how to create a Pivot Table Style.
________________________