How to Format a PivotTable Calculated Item as a Percentage
Question details
The user needs to apply a percentage format to a specific calculated item (like CAGR) in a PivotTable without altering the currency formatting of the main sales totals, while also suppressing division-by-zero errors.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Analyzing financial data in a PivotTable where standard sales data requires currency formatting, but a custom calculated growth rate requires percentage formatting.
- Observed behavior
- The goal is to ensure the calculated item displays cleanly as a percentage and avoids displaying #DIV/0! errors when the starting value in the calculation is zero or blank.
Ensure your PivotTable is fully set up with your base sales data and that you have already inserted the calculated item you wish to format.
Apply Manual Formatting and IFERROR Formula
Use Value Field Settings for the primary currency data and selectively apply Percentage formatting from the Home tab for the calculated item.
By default, a calculated item inherits the formatting of the value field it is placed in. To have mixed formatting, you must format the base field globally and override the calculated item locally.
Right-click any value in the main sales field of your PivotTable, select 'Value Field Settings', click the 'Number Format' button, and choose 'Currency'.
Using your mouse, click and drag to highlight only the specific cells in the PivotTable that contain the calculated item values (e.g., your CAGR column or row).
Navigate to the 'Home' tab on the top ribbon, locate the Number formatting section, and click the '%' (Percentage Style) icon to format the selected cells as percentages.
To fix division-by-zero errors, click on your PivotTable, go to 'Fields, Items & Sets', and edit your calculated item formula to include an error handler. For example: =IFERROR((Ending/Beginning)-1, 0) or use an IF statement to check for blank starting values.

Use Power Pivot for Advanced DAX Formatting (Windows Only)
For complex datasets on supported platforms, using Power Pivot allows you to create explicit measures with independent number formats.
Manage PivotTable Formatting Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful PivotTable capabilities, allowing you to easily add calculated items and customize individual cell formatting without disrupting the rest of your data. It perfectly handles complex formulas and mixed formatting environments.
- 1. Open your Data in WPS Spreadsheet: Launch WPS Spreadsheet, open your workbook, and click anywhere inside your existing PivotTable.
- 2. Insert a Calculated Item: Navigate to the 'PivotTable' tools tab, click on 'Fields, Items & Sets', and select 'Calculated Item' to input your formula (e.g., using IFERROR).
- 3. Apply Mixed Formatting: Highlight the specific cells for the calculated item, go to the 'Home' tab, and click the Percentage format icon to distinguish it from your currency data.

Frequently Asked Questions
Why does my entire PivotTable change when I format one calculated item?
If you change the format using 'Value Field Settings', it applies the number format to the entire field. To format only a calculated item, you must manually select those specific cells and apply the formatting from the Home tab.
How do I hide #DIV/0! errors in a PivotTable without changing the formula?
You can hide error values globally by right-clicking the PivotTable, selecting 'PivotTable Options', checking the box for 'For error values show:', and leaving the adjacent text box blank (or entering a zero).
Can I use Power Pivot on Mac to format calculated fields?
No, Power Pivot is currently not supported in spreadsheet applications on macOS. Mac users should use standard PivotTable calculated items and apply percentage formatting manually via the Home ribbon.




