How to change the default PivotTable Style in a workbook, or create a new PivotTable Style. Copy a style to a different workbook, or copy and paste a pivot table as values, and keep the formatting.
When you create a pivot table, a default PivotTable Style is automatically applied. You can change the default PivotTable Style in a workbook, so a different style is automatically applied.
Follow these steps to change the default PivotTable Style for a workbook:
You can't change any of the built-in PivotTable styles, but you can create a Custom PivotTable style in your workbook, with the formatting that you need.
1. First, choose one of these ways to get started::
2. Next, follow the instructions here, to modify the Custom Style.
3. Finally, follow the steps to apply the new custom style to a pivot table.
If you find a PivotTable style that’s close to what you need, follow these steps to create a custom style, based on that style:
Next, follow the steps in the Modify the PivotTable Style section (below), to name and modify the new style.
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.
Next, follow the steps below, to name and modify the new style.
In this short video, see how to create a custom PivotTable style, based on an existing style, and modify the style. The border on the subheading is changed, to make it clearer where each section starts. Written instructions for modifying a style are below the video.
After duplicating or creating a PivotTable style, follow these steps to name the style and set the formatting.
The new PivotTable style that you created is not automatically applied to the active pivot table. Follow these steps to apply your custom style.
You can create custom PivotTable Styles in an Excel workbook, to fine tune the appearance of your pivot tables. This video show the steps to copy that custom style to a different workbook.
The written instructions are below the video.
To copy a custom PivotTable style, you can temporarily copy a formatted pivot table to a different workbook. In this example, we'll copy a custom PivotTable Style -- MyMedium2 -- from the MyOld.xlsx workbook to the MyNew.xlsx workbook.
Your custom PivotTable style now appears in the PivotTable Styles gallery, in the MyNew.xlsx workbook.
When you try to copy the pivot table formatting and values to another location, the PivotTable Style formatting isn't copied. This video shows how to successfully copy the formatting from a pivot table. Written instructions are below the video.
You might want to send someone a copy of a pivot table, without the link back to its source data. It's easy to copy a pivot table, and paste it as values, but if you try to paste the values and source formatting, you'll be disappointed by the results.
As you can see in the screen shot below, the values are pasted in column D, but not the PivotTable Style formatting.
Fortunately, John Walkenbach discovered that you can paste from the Office Clipboard, instead of using the Paste Values command, and the PivotTable Style formatting is pasted too.
Follow these steps to copy a pivot table's values and formatting:
The pasted copy looks like the original pivot table, without the link to the source data.
A pivot table is automatically formatted with a default style when you create it, and you can select a different style later, or add your own formatting. For example, in the pivot table shown below, colour has been added to the subtotal rows, and column B is narrow.
However, some of that pivot table formatting might be lost if you refresh the pivot table, select a different item in a report filter, or change the layout. For example, here's what the same pivot table looks like, after selecting an order date from the report filter.
Here are three ways to preserve the formatting in a pivot table:
Do NOT just select a few cells, or an entire worksheet column, and apply number formatting. There are a couple of ways to apply Number Formatting that will stick:
For a single pivot field that contains numbers only, follow these steps to apply Number Formatting with the Field Settings:
To format multiple fields at once, or if Field Settings does not have a Number Format button, follow these steps to carefully select and format the numbers:
To apply other types of cell formatting to the Pivot Table, do the following:
Watch this video to see how to enable selection and use the Selection Arrow.
Most pivot table formatting can be preserved if you change the Format options in the PivotTable Options dialog box.
If you add fields to a pivot table's value area, the field labels show the summary function and the field name. For example, when you add a field named Quantity, it appears as "Sum of Quantity".
Excel won't let you remove the "Sum of" in the label, and just leave the field name, Quantity. However, you can add a space character, at the beginning or end of the field name, to work around this limitation.
Also, if there are blank cells in the source data, you might see (blank) in the row labels. You can't delete that text, but you can replace it with a space character.
Watch this short video tutorial to see how to make these changes to the pivot table headings and labels.
To experiment with pivot table styles and formatting, download the sample file. The zipped file is in xlsx format, and does not contain any macros.
To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:
and much more!
Last updated: February 19, 2018 4:42 PM