1. Formatting Numbers in Pivot Tables
Steps:
- To change the data format, right-click any value >> choose Number Format from the shortcut menu.
- Use the Format Cells dialog box to change the number format of your pivot data.
- All the cells of the group will be formatted as accounting.
2. Pivot Table Designs
There are several built-in styles that you can apply to your pivot table.
- Select any cell in your pivot table and then choose Design > PivotTable Styles to select a style.
2.1 More Pivot Table Designs
- If these styles don’t fulfill your purpose, you can choose more styles by clicking the following button.
- You will find many built-in styles for you to use in your pivot tables.
2.2 Tuning Pivot Table Styles
You can fine-tune your styles using controls in the Design > PivotTable Style Options group.
2.3 Layout Controls in Excel Pivot Tables
You can also use the controls from the Design > Layout group to control various elements in your pivot table. You can use any of the following controls:
- Subtotals: Using this control, you can add/ hide subtotals and choose where to display them (above or below the data).
- Grand Totals: Using this control, you can choose which types, if any, to display.
- Report Layout: There are three report layouts. Using this control, you can choose any of the three different layout styles (compact, outline, or tabular). You can also choose to hide/active repeating labels.
- Blank Row: You can add a blank row between items to improve readability.
3. Field Controls of Pivot Tables
The PivotTable Analyze > Show group contains more options that affect the appearance of your pivot table. For example, you use the Show +/- Button to toggle the display of +/- sign in expandable items.
4. PivotTable Options Dialog Box
Steps:
- To display this dialog box, choose PivotTable Analyze > Options (in the PivotTable section) > Options.
- Or you can right-click any cell in the pivot table and choose PivotTable Options from the shortcut menu.
- The PivotTable Options dialog box will be visible.
Experimenting with styling features is the best way to become familiar with all these layout and formatting options.
5. Copying a Pivot Table Format
Steps:
- Select the Pivot Table >> right-click on the mouse >> select copy from the menu.
Select a range in your existing or new worksheet where you want to paste your data. You can paste the data directly into the available paste option or use the paste special feature.
- Select any cell >> Right-click on the mouse >> Choose Paste Special.
- In the Paste Special feature, click on the paste option that you want to use and click OK. Here, I selected All from Paste.
- Excel has copied the Pivot table data with all formatting.
6. Locking Pivot Table Format
Steps:
- Select the entire pivot table and right-click your mouse >> click the Format Cells option.
- In the protection option of the Format Cells, Uncheck the Locked option and press OK.
- In the Review Tab on top, click on the Protect Sheet
- Put a tick mark on the Select unlocked cells and set a password.
- A dialogue box will appear whenever you want to edit the pivot table, as in the picture below.
- If you want to unlock the protected cells, click the Review on top and then click on the Unprotect Sheet.
- It will ask for a password (if you set one). Type the password. You will see the sheet is unlocked now.
7. Pivot Table Column Side by Side in Excel
Steps:
- Select a cell in Pivot Table >> go to the Design tab >> click the dropdown of Report Layout >> select Show in Tabular Form.
Your Pivot Table will rearrange itself by putting the columns side by side.
8. Pivot Table Formatting Not Preserved in Excel
Sometimes, an unwanted situation like cell formatting in Pivot Table is not preserved and keeps changing, which may bother you. You may have accidentally changed a configuration, which you need to fix now.
- Right-click on a cell in the Pivot Table >> Select Pivot Table Options from the menu list.
- The Pivot Table Options dialog box will appear.
- Put a checkmark on Preserve cell formatting on update field if not checked.
- Click OK.
Download the Practice Workbook
You can download the practice book from the link below.
Pivot Table Formatting: Knowledge Hub
- Pivot Table Conditional Formatting Based on Another Column
- How to Remove Gridlines in Excel Pivot Table
- Pivot Table Date Format
- Blank in Pivot Table
<< Go Back to Pivot Table in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Every time I click on a slicer the pivot table formatting changes in ugly weird ways. The pivot table option for “Perserve Formating On Update” is checked I have tried to use the Pivot Table Styles, but can’t get what I need.
I came to the site to see if you had a solution, but nobody seems to know what the options in the custom styles mean or are,
Hello Gordan,
It’s a common issue with Excel pivot tables and slicers. You can follow the steps given below to maintain the formatting of your pivot table when using slicers:
Use Cell Styles:
Instead of relying on pivot table styles, try applying cell styles to the pivot table.
Select the cells you want to format, go to the “Home” tab, and choose “Cell Styles“. This approach can help preserve formatting when slicers are used.
Use a VBA Code:
If you’re comfortable with VBA, you can use a VBA script to reapply formatting whenever the pivot table is updated.
Right-click the sheet tab with your pivot table, choose “View Code,” and paste this code into the worksheet module.
Create a Custom Pivot Table Style:
You can create a custom pivot table style that includes all your desired formatting. To do this:
Click on your pivot table.
Go to “Design” > “PivotTable Styles” > “New PivotTable Style.”
Define your style, including fonts, colors, borders, etc.
Apply this custom style to your pivot table.
By trying these methods, you should be able to maintain your pivot table’s formatting even when using slicers. Let me know if you need more specific guidance on any of these topic.
Regards
ExcelDemy