Create a Custom PivotTable Style

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.

pivotcustomstyle

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:

  1. Select a cell in the pivot table, and on the Ribbon, click the Design tab.
  2. In the PivotTable Styles gallery, right-click the style you want to duplicate.
  3. In the context menu, click Duplicate.

pivotcustomstyle02

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.

  1. Select a cell in the pivot table, and on the Ribbon, click the Design tab.
  2. In the PivotTable Styles gallery, click New PivotTable Style (at the bottom of the PivotTable Styles gallery)

pivotcustomstyle03

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.

  1. 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.)
  2. 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.)pivotcustomstyle04
  3. In the Format Cells dialog box, select the Font, Border, and Fill settings you want for the selected element.
  4. 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.pivotcustomstyle05
  5. Repeat Steps 2 to 4, for any other elements that you want to format.
  6. (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.pivotcustomstyle06
  7. 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.

  1. Select a cell in the pivot table that you want to format.
  2. Open the PivotTable Styles gallery.
  3. The custom PivotTable Style you created is added to a Custom section at the top of the PivotTable Styles gallery.
  4. Click your custom style, to apply it to the active pivot table.

pivotcustomstyle07

__________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in Excel Pivot Tables, Pivot Table, pivot table formatting and tagged , . Bookmark the permalink.

15 Responses to Create a Custom PivotTable Style

  1. Hugo says:

    Great tip… As a begginer to pivot tables, I find this site extremelly helpful!!!

    Ty

  2. joseph says:

    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”

    • Debra Dalgleish says:

      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.

  3. Debra Dalgleish says:

    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

  4. rob says:

    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.

    • Debra Dalgleish says:

      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.

  5. Kathy says:

    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.

  6. 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.

  7. Felicity says:

    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?

  8. Vanessa says:

    @felicity.. I find if you creat a custom pivot then you can use it when you open up excel.. I think..:)

  9. Suwanna says:

    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?

  10. Greg says:

    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

  11. julio says:

    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.

  12. Richard says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>