Excel Pivot Table Format

Search Contextures Sites

Excel Pivot Table Format

Amazon.com

Amazon.com

Create a Custom PivotTable Style
Copy Custom PivotTable Style to Different File
Copy PivotTable Style Format and Values
Change the Default PivotTable Style
Keep Formatting in Excel 2007 Pivot Table

Pivot Table Tutorials and Videos

Create a Custom PivotTable Style

There are many built-in PivotTable styles, but you might not find one with the exact formatting you need. For example, you might like the heading colors in the Pivot Style Medium 9, but you would like a different color in the subtotal row.

Instead of using a built-in PivotTable style, you can create a custom PivotTable style with the formatting you prefer. You can 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, follow these steps to create a custom style, based on that 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.

Copy Custom PivotTable Style to Different File

You can create custom PivotTable Styles in an Excel workbook, to fine tune the appearance of your pivot tables. If you’ve invested a lot of time in create a custom style, you might like to copy that custom style to a different workbook.

Unfortunately, there isn’t a built-in command that will let you copy PivotTable Styles from one workbook to another. However, you can follow a few simple steps to copy your styles to any workbook.

Copy a PivotTable Style

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.

In the MyOld.xlsx workbook, select a cell in the pivot table that has the custom style applied.

On the Ribbon’s Options tab, in the Actions group, click Select, and then click Entire PivotTable

.

On the Ribbon’s Home tab, click Copy.

Switch to the MyNew.xlsx workbook.

Select a blank worksheet, or insert a new blank worksheet.

.

Select cell A1 on the blank worksheet.

On the Ribbon’s Home tab, click Paste.

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.

Watch the Video

Watch the video, to see the steps for copying a custom pivot table style to a different file.

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:

Select the original pivot table, and copy it.

Click the cell where you want to paste the copy.

On the Excel Ribbon's Home tab, click the Dialog Launcher button in the Clipboard group .

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.



Change the Default PivotTable Style

When you create a pivot table in Excel 2007, a default PivotTable Style is automatically applied. You can change the default PivotTable Style, if you prefer a different format.

Follow these steps to change the default PivotTable Style:

Select a cell in the pivot table.

On the Ribbon, under the PivotTable Tools tab, click the Design tab.

In the PivotTable Style gallery, right-click on the style that you want to set as the default.

In the context menu, click on Set As Default.

Keep Formatting in Excel 2007 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.

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.

Preserve the Formatting

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

Right-click a cell in the pivot table, and click PivotTable Options.

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.

Add a check mark to Preserve Cell Formatting on Update

Click OK.

Apply Manual Formatting

After changing these Pivot Table options, the formatting should stick. When you apply manual formatting to the Pivot Table, do the following:

Ensure that Enable Selection is turned on.

Unless you want to format a single cell, use the pivot table selection technique to select the elements you want to format (point to the top or left edge of the element, and then click when the black arrow appears).

Pivot Table Tutorials

Excel Pivot Table -- Introduction
Excel Pivot Table -- Clear Old Items
Excel Pivot Table -- Create a Pivot Table in Excel 2007
Excel Pivot Table -- Custom Calculations
Excel Pivot Table -- Data Field Layout
Excel Pivot Table -- Dynamic Data Source
Excel Pivot Table -- FAQs
Excel Pivot Table -- Field Settings
Excel Pivot Table -- Filter Source Data
Excel Pivot Table -- Filters, Top 10
Excel Pivot Table -- GetPivotData
Excel Pivot Table -- Grand Totals
Excel Pivot Table -- Grouping Data
Excel Pivot Table -- Layout, Excel 2007
Excel Pivot Table -- Multiple Consolidation Ranges
Excel Pivot Table -- Pivot Cache
Excel Pivot Table -- PivotTable Style
Excel Pivot Table -- Printing
Excel Pivot Table -- Protection
Excel Pivot Table -- Report Filters
Excel Pivot Table -- Running Totals
Excel Pivot Table -- Show and Hide Items
Excel Pivot Table -- Sorting
Excel Pivot Table -- Subtotals
Excel Pivot Table -- Summary Functions
Excel Pivot Table -- Unique Items

Pivot Table Books

Pivot Tables, Beginning (Excel 2007)
Pivot Tables, Recipe Book (Excel 2003)
Pivot Tables, Recipe Book (Excel 2007)

Pivot Table Add-Ins

Pivot Tables - Add-in -- Pivot Power
Pivot Tables - Add-in - Pivot Play PLUS

Pivot Table Videos

Pivot Tables - Clear Old Items
Pivot Tables - Copy a Custom PivotTable Style
Pivot Tables - Create in Excel 2007
Pivot Tables - Create from Multiple Sheets
Pivot Tables - Data Field Layout
Pivot Tables - Date Filters, Add
Pivot Tables - GetPivotData
Pivot Tables - Group Data
Pivot Tables - Layout, Excel 2007
Pivot Tables - Report Filters, Add
Pivot Tables - Running Totals
Pivot Tables - Select Sections
Pivot Tables - Subtotals, Create Multiple
Pivot Tables - Top 10 Filters

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

Privacy Policy

Contextures Inc., Copyright ©2012
All rights reserved.

Debra Dalgleish

Last updated: April 28, 2012

Amazon.com Amazon.com	Create a Custom PivotTable Style Copy Custom PivotTable Style to Different File Copy PivotTable Style Format and Values Change the Default PivotTable Style Keep Formatting in Excel 2007 Pivot Table Pivot Table Tutorials and Videos Create a Custom PivotTable Style There are many built-in PivotTable styles, but you might not find one with the exact formatting you need. For example, you might like the heading colors in the Pivot Style Medium 9, but you would like a different color in the subtotal row. Instead of using a built-in PivotTable style, you can create a custom PivotTable style with the formatting you prefer. You can 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, follow these steps to create a custom style, based on that 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. Copy Custom PivotTable Style to Different File You can create custom PivotTable Styles in an Excel workbook, to fine tune the appearance of your pivot tables. If you’ve invested a lot of time in create a custom style, you might like to copy that custom style to a different workbook. Unfortunately, there isn’t a built-in command that will let you copy PivotTable Styles from one workbook to another. However, you can follow a few simple steps to copy your styles to any workbook. Copy a PivotTable Style 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. In the MyOld.xlsx workbook, select a cell in the pivot table that has the custom style applied. On the Ribbon’s Options tab, in the Actions group, click Select, and then click Entire PivotTable . On the Ribbon’s Home tab, click Copy. Switch to the MyNew.xlsx workbook. Select a blank worksheet, or insert a new blank worksheet. . Select cell A1 on the blank worksheet. On the Ribbon’s Home tab, click Paste. 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. Watch the Video Watch the video, to see the steps for copying a custom pivot table style to a different file. 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: Select the original pivot table, and copy it. Click the cell where you want to paste the copy. On the Excel Ribbon's Home tab, click the Dialog Launcher button in the Clipboard group . 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. Change the Default PivotTable Style When you create a pivot table in Excel 2007, a default PivotTable Style is automatically applied. You can change the default PivotTable Style, if you prefer a different format. Follow these steps to change the default PivotTable Style: Select a cell in the pivot table. On the Ribbon, under the PivotTable Tools tab, click the Design tab. In the PivotTable Style gallery, right-click on the style that you want to set as the default. In the context menu, click on Set As Default. Keep Formatting in Excel 2007 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. 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. Preserve the Formatting Most pivot table formatting can be preserved if you change the Format options in the PivotTable Options dialog box. Right-click a cell in the pivot table, and click PivotTable Options. 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. Add a check mark to Preserve Cell Formatting on Update Click OK. Apply Manual Formatting After changing these Pivot Table options, the formatting should stick. When you apply manual formatting to the Pivot Table, do the following: Ensure that Enable Selection is turned on. Unless you want to format a single cell, use the pivot table selection technique to select the elements you want to format (point to the top or left edge of the element, and then click when the black arrow appears).