Excel Pivot Table Format

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.

Change the Default PivotTable Style

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:

1. Select a cell in any pivot table.
2. On the Ribbon, under the PivotTable Tools tab, click the Design tab.
3. In the PivotTable Style gallery, right-click on the style that you want to set as the default.
4. In the context menu, click on Set As Default.   ▲TOP

NOTE:

Modify a PivotTable Style

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::

• copy an existing style, to start with its formatting
• create a new style, to start from scratch

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.

Copy an Existing PivotTable Style

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:

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.

Next, follow the steps in the Modify the PivotTable Style section (below), to name and modify the new style.   ▲TOP

Create a New PivotTable 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.

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)

Next, follow the steps below, to name and modify the new style.

Modify the PivotTable 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.

Modify a 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.)

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 screen shot below, you can see the revised color in the Preview section.

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.

7. Click OK, to close the New PivotTable Quick Style dialog box.

Apply the Custom PivotTable Style

The new PivotTable style that you created is not 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.   ▲TOP

Copy Custom Style to Different File

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.

NOTE: For Excel 2016 pivot tables, see the instructions below.

The written instructions are below the video.

How to Copy Custom PivotTable Style to Different File

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.

NOTE: For Excel 2016 pivot tables, see the instructions below.

1. In the MyOld.xlsx workbook, select a cell in the pivot table that has the custom style applied.
2. On the Ribbon's Options tab, in the Actions group, click Select, and then click Entire PivotTable
   
   .
   
   
3. On the Ribbon's Home tab, click Copy.
4. Switch to the MyNew.xlsx workbook.
5. Select a blank worksheet, or insert a new blank worksheet.
   
   .
6. Select cell A1 on the blank worksheet.
7. On the Ribbon's Home tab, click Paste.
8. Delete the sheet that contains the pasted copy of the pivot table.

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 a Custom Style in Excel 2016 or Later

In Excel 2016, the custom pivot table style is not copied, if you use the above technique to copy and paste a pivot table. I found a different way to copy the custom style, and this method also works in Excel 2013.

In Excel 2016, follow these steps to copy a custom style into a different workbook:

1. Open the old workbook with the pivot table that has the custom style applied.
2. Open the new workbook where you want to add that custom style
3. Position the workbooks, so you can see the sheet tabs in both files
4. Press the Ctrl key, and drag a copy of the pivot table sheet from the old workbook, into the new workbook.
5. The custom style is now copied into the new workbook, and you can delete the sheet that you copied into it, from the old workbook.

Copy PivotTable Style Format and Values

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.

How to Copy PivotTable Style Format and Values

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:

1. Select the original pivot table, and copy it.
2. Click the cell where you want to paste the copy.
3. On the Excel Ribbon's Home tab, click the Dialog Launcher button in the Clipboard group .

4. In the Clipboard, click on the pivot table copy, in the list of copied items..

The pasted copy looks like the original pivot table, without the link to the source data.   ▲TOP

  

Keep Formatting in Excel Pivot Table

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.

Formatting Disappears

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.

How to Preserve Pivot Table Formatting

Here are three ways to preserve the formatting in a pivot table:

• How to Apply Number Formatting
• How to Apply Cell Formatting
• Setting to Preserve Cell Formatting

How to Apply Number Formatting

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:

• To apply number formatting to a single field in a pivot table, use the Field Settings, if possible.
• To format multiple fields at once, or if Field Settings does not have a Number Format button, follow the steps to carefully select and format the numbers.

Use the Field Settings

For a single pivot field that contains numbers only, follow these steps to apply Number Formatting with the Field Settings:

1. Right-click a value in the pivot field that you want to format
2. Click Field Settings
3. At the bottom left of the Field Settings dialog box, click Number Format.
   • NOTE: If there are non-numeric items in the pivot field, the Number Format box might not appear. Use the formatting steps in the "Select and Format" section.



4. In the Format Cells dialog box, select the number formatting that you want, and click OK
5. Click OK, to close the Field Settings dialog box

Select and Format Numbers

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:

1. To select the numbers, do NOT click the worksheet column headings -- use one of the following methods to select the cells
   • In the pivot table, point to the top of a value field heading. When the pointer changes to a black arrow, click to select the entire pivot table column, including the grand totals.

   

   • OR, carefully select all the numbers in multiple fields that you want to format, including any grand totals (row and column).

   

2. With the cells selected, click the Home tab on the Excel Ribbon.
3. Use the formatting commands and Number format options to format the selected cells.

How to Apply Cell Formatting

To apply other types of cell formatting to the Pivot Table, do the following:

1. Ensure that Enable Selection is turned on.
2. To format a single cell, select that cell, and use the formatting commands on the Excel Ribbon
3. To format a specific part of the pivot table, such as the subtotal rows, select that part -- point to the top or left edge of the element, and then click when the black arrow appears.   ▲TOP

Watch this video to see how to enable selection and use the Selection Arrow.

Setting to Preserve Cell Formatting

Most pivot table formatting can be preserved if you change the Format options in the PivotTable Options dialog box.

1. Right-click a cell in the pivot table, and click PivotTable Options.
2. On the Layout & Format tab, in the Format options, remove the check mark from Autofit Column Widths On Update. This will prevent column widths from changing, if you have manually adjusted them.
3. Add a check mark to Preserve Cell Formatting on Update
4. Click OK.

Change Pivot Table Labels

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.

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.

Download the Sample File

1. To experiment with pivot table styles and formatting, download the sample file. The zipped file is in xlsx format, and and does NOT contain any macros.
2. To experiment with pivot table styles macros, download the Styles Macros sample file. The zipped file is in xlsm format, and contains macros to work with the PivotTable Styles. Print a list with the default pivot style and table style, print a list of pivot tables with style info, or change the default pivot style, based on the selected pivot table.  ▲TOP

Pivot Table Tools

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:

• copy the formatting from one pivot table, and apply it to another pivot table.
• change all the values from Count to Sum
• remove the "Sum of" from all the headings

and much more!

More Pivot Table Resources

Add-In: Pivot Power Premium

Pivot Table Introduction

Pivot Table Macros -Remove "Sum Of"

Summary Functions

Manually Move Pivot Items

Running Totals

Clear Old Items in Pivot Table