Home > Pivot > Format > Styles Excel Pivot Table FormatHow 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. |
Use PivotTable Style OptionsA quick way to make a pivot table style look different is to apply or remove the built-in pivot table style options. Tip: Making these simple changes might be all that you need, so try these steps, before going to the trouble of creating a new PivotTable style. The Style options are: Row Headers, Column Headers, Banded Rows, Banded Columns These style options, if turned on, can make it easier to read the data in a large, complex pivot table.
However, the row banding and column banding can also make the pivot table look cluttered, so experiment with your pivot table style settings, to see what works best for each pivot table. See PivotTable Style OptionsTo see the available PivotTable Style options, follow these steps:
In the PivotTable Style gallery, the style previews will change, to show the style option settings that you have selected. |
Modify a PivotTable StyleYou can't change any of the built-in PivotTable styles, but you can create a new 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 in the section below, to modify the Custom Style. 3. Finally, follow the steps below, to apply the new custom style to a pivot table. |
Copy an Existing PivotTable StyleUsually, the easiest way to create a custom pivottable style, is to start from one of the existing styles. Then, change the colours, or some of the formatting, to customize the style to what you need. First, look through the gallery of built-in pivot table styles, to see if you can find a good starting point. Then, 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. |
Name a PivotTable StyleAfter duplicating or creating a PivotTable style, follow these steps to name the style
Modify a PivotTable StyleNext, change one or more of the pivot table elements, that are listed in the Modify PivotTable Quick Style dialog box. In the Table Element list, some of the element names are in Bold font. Those elements have formatting applied. You can change the formatting for those bold elements, or clear the formatting. You can also add formatting to any of the other elements in the list, that are not already formatted.
|
Modify Custom StyleTo modify a table element in the pivot table custom style, follow these steps:
|
Format Cells Dialog BoxAfter you click the Format button, the Format Cells dialog box opens. Follow the steps below, to select the format options that you want:
In the screen shot below, you can see the revised color in the Preview section, at the right side of the dialog box..
|
Copy Custom Style to Different FileYou 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, and there are written steps below the video. Video Timeline
NOTE: For Excel 2010 pivot tables, see the instructions below. |
How to Copy Custom PivotTable Style to Different FileUse this method, in Excel 2016, or later, to copy a custom style into a different workbook: NOTE: If you want to avoid copying defined names, and custom cell styles, into the other workbook, follow the steps in the section below.
|
Workbooks with Defined Names and Custom Cell StylesWhen you copy a sheet from one file to another, it also copies defined names, and custom cell styles, from the original workbook. If you want to avoid that, use these extra steps: Use this method, in Excel 2016, or later, if your workbook also has custom Cell Styles.
|
Copy a Custom Style in Excel 2010 / 2007To 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. This video shows the steps, and the written instructions are below the video. Video Timeline
|
To copy a custom pivot table style to a different workbook, in Excel 2007 or Excel 2010, follow the steps below. 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. Select any pivot table in the workbook, and apply your custom PivotTable Style. ▲TOP |
Copy PivotTable Style Format and ValuesWhen 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 manually copy the formatting from a pivot table. Written instructions are below the video. There is also a macro to copy pivot table values and formatting, which makes the task easier. |
How to Copy PivotTable Style Format and ValuesYou 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. ▲TOP |
How to Preserve Pivot Table FormattingHere are three ways to preserve the formatting in a pivot table: How to Apply Number FormattingFor pivot table numbers, do NOT just select a few cells, or an entire worksheet column, and apply number formatting. That type of formatting might not stick, after you refresh the pivot table. Instead, are a couple of ways to apply Number Formatting that will stick:
|
Use the Field SettingsFor a single pivot field that contains numbers only, follow these steps to apply Number Formatting with the Field Settings:
|
Select and Format NumbersTo 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:
|
How to Apply Cell FormattingTo 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. |
Setting to Preserve Cell FormattingMost pivot table formatting can be preserved if you change the Format options in the PivotTable Options dialog box.
|
Change Pivot Table LabelsIf 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. NOTE: If you checked the box to "Add this data to the data model", when creating your pivot table, the pivot table is OLAP-based, instead of a Normal pivot table. For OLAP pivot tables, or to handle both types of pivot tables, use the macros on the Pivot Table Value Heading Macros page. Video: Change Pivot Table LabelsWatch this short video tutorial to see how to make these changes to the pivot table headings and labels. |
Get the Sample File
|
Clear Old Items in Pivot Table
Last updated: October 25, 2023 3:43 PM