There are many built-in PivotTable styles in Excel 2007, but you might not see one that has the exact formatting you need. For example, you might like the heading colors in the Pivot Style Medium 9, but you’d like a different color in the subtotal row.
Instead of using one of the built-in PivotTable styles, you can create a custom PivotTable style with the formatting you prefer. You can either duplicate an existing Pivot Table style, and modify the duplicate, or you can create a new style from scratch.
Copy an Existing PivotTable Style
If you find a PivotTable style that’s close to what you need, you can duplicate that style, and modify the duplicate. Follow these steps to create a custom style, based on an existing style:
- Select a cell in the pivot table, and on the Ribbon, click the Design tab.
- In the PivotTable Styles gallery, right-click the style you want to duplicate.
- In the context menu, click Duplicate.
Next, follow the steps in the Modify the PivotTable Style section (below), to name and modify the new style.
Create a New PivotTable Style From Scratch
If you don’t see anything similar to what you need, you can create a new style from scratch. Follow these steps to create a new style.
- Select a cell in the pivot table, and on the Ribbon, click the Design tab.
- In the PivotTable Styles gallery, click New PivotTable Style (at the bottom of the PivotTable Styles gallery)
Next, follow the steps in the Modify the PivotTable Style section (below), to name and modify the new style.
Modify the PivotTable Style
After duplicating or creating a PivotTable style, follow these steps to name the style and set the formatting.
- In the Modify PivotTable Quick Style dialog box, type a name for the new PivotTable style. (If you created a new style, the dialog box will be named New PivotTable Quick Style.)
- To modify an element’s formatting, click it to select it, and then click the Format button. (Note: In the Table Element list, the elements in bold font have formatting applied. You can modify or clear that formatting. You can also select unformatted elements and format them.)
- In the Format Cells dialog box, select the Font, Border, and Fill settings you want for the selected element.
- Click OK, to return to the New PivotTable Quick Style dialog box, where the formatted element is listed with a bold font. In the screenshot below, you can see the revised color in the Preview section.
- Repeat Steps 2 to 4, for any other elements that you want to format.
- (optional) If you want your custom style to be the default PivotTable style, add a check mark to Set As Default PivotTable Quick Style For This Document.
- Click OK, to close the New PivotTable Quick Style dialog box.
Apply the Custom PivotTable Style
The new PivotTable style that you created will not be automatically applied to the active pivot table. Follow these steps to apply your custom style.
- Select a cell in the pivot table that you want to format.
- Open the PivotTable Styles gallery.
- The custom PivotTable Style you created is added to a Custom section at the top of the PivotTable Styles gallery.
- Click your custom style, to apply it to the active pivot table.
__________
Great tip… As a begginer to pivot tables, I find this site extremelly helpful!!!
Ty
Thanks Ty, I’m glad the articles are helping you.
Debra
I want to make a 2007 pivot look exactly as the default 2003 table style. where is that template? also is there a way to make the default data setting to “sum” instead of “count”
Joseph, this won’t be exactly the same, but change the report layout to Tabular, and for the PivotTable Style, click None, which is the first option in the Light category.
Joseph, you can’t change the default setting for Sum, but there are a couple of suggestions in this article: Pivot Table Defaults to Sum or Count
Unfortunately, my list of fonts is greyed-out when I select “format” from the “Modify PivotTable Quikc Style”. Would you have a suggestion as to why this would be? I can select bold, italic, colour’s, fills, borders etc. just not the font itself.
Many thanks.
Rob, I don’t know why the styles have that font limitation, but it’s similar to the Conditional Formatting restrictions. They don’t let you change settings that would affect the size of the cell.
When I type in a cell and I click on the color that I want to use for the font, the color reverts to black when I go to another cell. I have MS 2007. My instructor said it may have something to do with a template, but I don’t know how to check out this problem.
Kathy, select all the words that you want in the different colour, before you change the font colour. Or, go back to the cell, and click on it once, to select it, then change the font colour for the entire cell.
Is there a way to keep the formatting of a pivot I have modified in one sheet for future pivots when I open up a new spreadsheet?
@felicity.. I find if you creat a custom pivot then you can use it when you open up excel.. I think..:)
what if the number of Column strip is not alway the same? For example If have 3 columns on Strip1,2 columns for stripe2, but then my next strip is not 3 columns but 4 columns instead. Is there the way to add manual stripe number?
Do you know how to apply the customised pivot style for all Excel files?
I have a format i’d like to make available to all my excel files, but presently have to open the master and copy a sheet into the new spread sheet to inherit the format … not ideal, have you any suggestions?
thanks
Greg
I am trying to customise a pivot table with stripes, but they do not seem to work. they show up on the preview, but do not actually show up on the table.
Hi, wondered of anyone could advise. Is there a way of haveing a custom designed pivot table design, set as a default for all reports I create without having to re-create it every time I create a new rereport table.
Many thanks
Richard