Let’s take a look. The easiest way to create a custom pivot table style is to first apply a built-in style and then duplicate and customize that style as needed. For example, let’s apply a medium style to this pivot table. You can check that this style is applied by selecting any cell in the pivot table and then checking the PivotTable styles group. The applied style will be highlighted. Once an existing style is applied, duplicate that style. You can duplicate a style by right-clicking, and choosing Duplicate from the menu. Then, give the duplicated style a descriptive name. Let’s use Blue Gray. The new style will appear in the upper left of the PivotTable styles group. It will not be applied to the pivot table, so it’s important to apply the new style next. Once applied, the new style will be highlighted in the styles group, and Excel will display its name when you hover over the style. Once you have a new pivot table style created and applied to your pivot table, you can easily customize the style as you like. For example, let’s change the style so that the shading on the category subtotal rows is gray instead of blue. First, right-click the style and choose Modify from the menu. You’ll see that pivot table styles are somewhat complex - there are a lot of individual elements that can be styled. However, notice that only the bolded elements are in use for any given style. If we click through the list, we see the formatting defined for each bolded table element. Non-bolded elements have no formatting defined. In this case, the element we want to style is Row Subheading 1. We want to switch the shading from blue to gray, so we need to change the fill. Notice that the small preview thumbnail is updated as we work. After we click OK twice, the new setting takes effect. You can use this same approach to customize any defined element in the table. If we add the region as a row label above Category, we see that we still get the gray shading on the first row subheading, but not the second. Let’s edit the style again to make the second row subheading shaded orange. Now our style correctly applies formatting for both levels. To test that the new style works correctly on a new pivot table, let’s clear and rebuild this pivot table from scratch. When we apply our custom style, we see all the formatting we defined.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
