Click any cell in the pivot table, go to Design → PivotTable Styles and pick a style from the gallery. Use the Row Headers, Column Headers, Banded Rows and Banded Columns boxes beside it to switch those parts on or off.
To format the numbers, right-click a value, choose Value Field Settings → Number Format and set a format such as #,##0 or 0.0%. Setting it on the field keeps the format when you rearrange the pivot, which formatting the cells with Ctrl + 1 does not always do.
For the overall shape, use Design → Report Layout to switch to Tabular Form or Outline Form, Design → Subtotals and Grand Totals to control the totals, and Design → Blank Rows to add a gap after each group.
To build your own look, open the style gallery and choose New PivotTable Style, or right-click a style and pick Duplicate, then format each table element. Right-click any style and choose Set As Default to use it for new pivots in this workbook. Custom styles live in the workbook, so to use one elsewhere copy a sheet that uses it into the other workbook, then delete the copied sheet.
If your formatting disappears when you refresh, right-click the pivot, choose PivotTable Options and, on the Layout & Format tab, tick Preserve cell formatting on update. Untick Autofit column widths on update on the same tab if the columns keep resizing.
Working on a pivot table is quite interesting, but sometimes it feels boring to use the same built-in colors and layouts. If you are very particular about the look and color layout of your report, here are a few things you need to know about formatting a pivot table.
Default Style Options
There are a few options available on the ribbon once you have a pivot table on your worksheet. Click anywhere in the pivot table, and you get the Design tab (called PivotTable Design in Microsoft 365).

You can choose between many colors and themes (light, medium and dark) from the little drop-down arrow.
Next to the gallery, you have the PivotTable Style Options: Row Headers, Column Headers, Banded Rows and Banded Columns. Tick or untick them to add or remove the bold headers and the stripes, and the styles in the gallery update to show the result.

Quick Tip: The style previews in the gallery change as you change the PivotTable Style Options and the layout.
Change Pivot Table Formatting
Now, to choose a particular style as the default for your PivotTables, use the following steps.
- First, click anywhere on the PivotTable to activate the Design tab in the ribbon.

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

- In the menu, click “Set As Default
”. - This will make the selected style the default style for new pivot tables in this workbook.
Duplicate and Modify
Let’s begin by creating a new style from scratch.
- Click anywhere on the PivotTable to activate the Design tab.
- Now, click on the small drop-down arrow of the styles gallery and scroll to the end.

- Here click on the “New PivotTable Style”.
- Now, a pop-up window will open “New PivotTable Style”.
- Type a name for your new style in the “Name” box.
- Select an element to format and click on the “Format” button.
- In the end, click OK to create your style.
- In case you want to use the style for future pivots in this workbook, just tick the “Set as default PivotTable style for this document” option at the end, as shown in the screenshot.

Now let’s edit one of the built-in styles. You can’t change a built-in style directly, so you duplicate it and change the copy. This is almost the same as creating a new style.
- To start with, right-click on the style that matches your requirement.

- Now click on Duplicate to open the “Modify PivotTable Style” dialog box.

- Now, give the copy a name, select the elements you want to change, format them with the Format button, and click OK, just like you did for a new style.
Copy the Layout to a Different Workbook
Since we have invested a lot of time in creating our custom style, let’s copy it to another workbook to save energy. A custom style is saved in the workbook, so you copy it by copying a sheet with a pivot table that uses it.
- To start with, open the workbook with a custom layout.
- Next, open the workbook where you want to copy the custom layout.
- Now place both the workbooks adjacent so that both are visible on the screen (Tip: Use the Arrange All option on the View tab of the ribbon).

- On the View tab, click “Arrange All” and pick how you want the windows arranged.

- In the end, hold the Ctrl key and drag the sheet with the styled pivot table to the other workbook.
- Boom!! The custom style is copied to the other workbook, and you can find it in the styles gallery there.
- Now you can delete the sheet you dragged to the workbook; the style stays.
Change the Report Layout
Apart from colors, the layout decides how your pivot table looks. On the Design tab, you have these options in the Layout group:
- Report Layout: Compact Form (all row fields in one column), Outline Form or Tabular Form (each row field in its own column). Here you also have Repeat All Item Labels to fill the blank labels.
- Subtotals: show subtotals at the top or bottom of each group, or don’t show them.
- Grand Totals: turn them on or off for rows and columns.
- Blank Rows: insert a blank line after each item to make a long report easier to read.
Keep the Formatting When You Refresh
If your column widths or cell formatting change every time you refresh the pivot table, change these two settings:
- First of all, right-click on the pivot table and open PivotTable Options.
- After that, on the Layout & Format tab, untick “Autofit column widths on update” and keep “Preserve cell formatting on update” ticked.
- In the end, click OK.
For numbers, don’t format the cells directly. Right-click a value, open Value Field Settings, click Number Format and pick the format there. This way, the format stays with the field even when the pivot table changes.
Frequently Asked Questions
How do I remove the style from a pivot table?
Open the styles gallery on the Design tab and click Clear at the bottom of it. The pivot table goes back to plain formatting, and you can pick another style any time.
Does Set As Default work for all my workbooks?
No, the default style is saved in the current workbook only. To use your style in another workbook, copy it with a sheet as shown above.
Can I delete a custom PivotTable style?
Yes. Your custom styles show at the top of the gallery under Custom. Right-click the style and choose Delete. Built-in styles can’t be deleted.
More on Pivot Table
Sort a Pivot Table | Refresh a Pivot Table | Pivot Table Keyboard Shortcuts |Move a Pivot Table | Filter a Pivot Table | Count Unique Values in a Pivot Table | Change Pivot Table Data Source| Add or Remove Grand Total in a Pivot Table | Add Ranks in Pivot Table | Insert Calculation in Pivot Table | Refresh All Pivot Tables | Automatically Update a Pivot Table | Running Total in a Pivot Table | Conditional Formatting to a Pivot Table | Pivot Table from Multiple Worksheets | Group Dates in a Pivot Table | Connect a Slicer with Multiple Pivot Tables | Pivot Table Timeline