How to Create a Pivot Table Style

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.

pivotstyles02

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.

pivotstyles04

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!

pivotstyles03

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.

  1. Select a cell in the pivot table
  2. Click the Design tab (under PivotTable Tools)
  3. 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.

pivotstyles05

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.

  1. Give your style a name, e.g. MY PivotStyleLight8
  2. 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)

    pivotstyles06

  3. 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.
  4. 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.

  1. Select a cell in the pivot table
  2. Click the More button, at the bottom right of the PivotTable Styles list.

    pivotstyles07

  3. In the Custom section, click on your new Custom Style

    pivotstyles08

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.

________________________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.