Pivot Table Issues

How to Format a PivotTable Calculated Item as a Percentage

Tauseeq MagsiTauseeq Magsi Nov 3, 2026 869 views

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.

How to Format a PivotTable Calculated Item as a Percentage
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.
Before you start

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.

Solution 1Recommended

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.

1
Set Base Field to Currency

Right-click any value in the main sales field of your PivotTable, select 'Value Field Settings', click the 'Number Format' button, and choose 'Currency'.

2
Select the Calculated Item

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

3
Apply Percentage Formatting

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.

4
Suppress #DIV/0! Errors

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.

Apply Manual Formatting and IFERROR Formula
Error Handling Tip: Using IFERROR directly in the calculated item formula ensures that your PivotTable stays clean and calculations remain accurate even when source data changes.

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. 1. Open your Data in WPS Spreadsheet: Launch WPS Spreadsheet, open your workbook, and click anywhere inside your existing PivotTable.
  2. 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. 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.
Fully compatible with Microsoft Excel (.xlsx) PivotTables and custom formatting.Intuitive and easy-to-use interface for modifying Value Field Settings and Number Formats.Advanced formula support, including IFERROR, to keep your data analysis clean.
microsoft office alternative - wps office

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.