Creating a PivotTable style

The built-in PivotTable Style gallery contains a lot of good color schemes, but they are by design somewhat generic. When you want to create your own PivotTable style to reflect your personal aesthetic or your company's graphic art guidelines, you can do so using the new PivotTable Quick Style dialog box. To display that dialog box, click any PivotTable cell and then on the Design tab of the Ribbon click the More button at the bottom-right corner of the PivotTable Styles gallery and then click New PivotTable Style.

The first thing you should do is type the name for your new PivotTable style. So if you were creating a style to reflect your company's graphic standards, you could call it something like Graphics Standards 2011. Next you click the table element you want to change and then click the Format button to display the formatting tools to apply to that element. So for example let's say that I want to change the header row's formatting. To do that, I click the header row table element from the list and then click Format.

Then, this is the familiar Format Cells dialog box. You can change any aspect of the formatting that you want. So in this case, we will make the Font Style Bold and the Color White. So to do that and to have the text show up, we need to have a dark background. So we will click Fill and then I will select Purple. When I'm done in the Format Cells dialog box I can click OK. And when I do, Excel displays a preview of what my style will look like after I save it. That's the only change I will make but you can make changes to any other elements that you like.

just feel free to experiment using the elements in the Table Element list. When you are ready, go ahead and click OK and you have created your style. To apply that style, click the More button on the Design contextual tab and you'll see your new style at the top of the list in the custom area. To apply it, just click the style and Excel applies it to your PivotTable. If you want to edit a custom style, just right-click it within the gallery, so click the More button to display the entire gallery and then right-click your style and then click Modify.

The Modify PivotTable Quick Style dialog box is essentially the same in all but name as the Create PivotTable Quick Style dialog box. So for example, let's say that I wanted to add a second row stripe. To do that, I can click Second Row Stripe, click Format, and I will make every second row a dusty purple. So I have selected the color that I want, click OK, and we see the preview here. Click OK. Now you will notice that even though I edited the style, the second row stripe doesn't appear and that's because the Banded Rows option isn't turned on.

So when I go up to the Design tab and check the Banded Rows box, Excel applies the full style which includes the Second Row Stripe that I defined when I modified the PivotTable Style. Now let's say that you want to edit a built-in style. You actually can't, but what you can do is duplicate an existing style and then make changes based on the duplicate. So let's say for example that I wanted to create a style that was based on Pivot Style Light 1. To do that I right-click the style that I want to work with and then click Duplicate.

When I do, Excel creates a new style and I can change its name, change any of its values, any of its formatting, and so on. It's exactly the same as creating or modifying another PivotTable style so I won't work through all the details. When you're done you can click OK to save your changes or do as I will do, which is click Cancel to exit the dialog box without saving your changes. If you want to delete a custom PivotTable style, you can right-click the style, and this is the custom style I created earlier, and then on the Shortcut menu that appears click Delete.

Excel asks if you are sure and you can click OK. When you do, Excel reverts to the previous style applied to the PivotTable. Now finally, if you want to remove all formatting from your PivotTable, click any cell on the PivotTable, and then on the Design contextual tab, click the More button at the bottom- right corner of the Pivot Styles Gallery and then click Clear. Excel gives you the tools to control exactly how your PivotTable appears in your workbook. Creating your own formats enables you to control how your PivotTable appears in your company's documents and presentations, enhancing your corporate identity while making the data easier for viewers to comprehend.