How to Preserve Percentage and Currency Formatting in an Excel PivotTable
Question details
The user needs to prevent custom percentage and currency formatting from reverting to default numbers when an Excel PivotTable is refreshed or modified.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Formatting financial or statistical data within a PivotTable to display properly as currency or percentages.
- Observed behavior
- Percentage and currency formats disappear when the PivotTable is refreshed because the formats were applied directly to worksheet cells instead of the PivotTable fields.
Ensure your PivotTable is connected to an active data source and verify that no conflicting conditional formatting rules are applied to the worksheet cells.
Apply Number Formatting via Value Field Settings
Applying formatting directly to the PivotTable field instead of the worksheet cells ensures the format is bound to the data and persists across all refreshes.
When you format cells using the standard Home tab ribbon, Excel only formats the static grid cells. Because PivotTables dynamically rebuild themselves during a data refresh, these static cell formats are overwritten. To permanently lock in a format, it must be applied at the field level.
Right-click on any numeric value within the target data column of your PivotTable.
From the right-click context menu, select "Value Field Settings".
In the Value Field Settings dialog box, click on the "Number Format" button located at the bottom left.
Select either "Percentage" or "Currency" from the Category list, adjust your decimal places, and click "OK" twice to apply the changes.
Format Fields Located in the Rows or Columns Area
If your data fields are placed in the Rows or Columns area instead of the Values area, the formatting must be applied through Field Settings.
Create and Format PivotTables in WPS Spreadsheet
WPS Spreadsheet provides robust and intuitive PivotTable features that allow you to lock in number and currency formatting with just a few clicks. It offers full compatibility with Microsoft Excel files, ensuring your data formats remain intact when refreshing or sharing documents.
- 1. Open your file: Launch WPS Spreadsheet and open the .xlsx file containing your PivotTable.
- 2. Access field settings: Right-click the data field you wish to format and select "Value Field Settings".
- 3. Modify the number format: Click on the "Number Format" button in the dialog box.
- 4. Apply and save: Select "Currency" or "Percentage", configure your preferences, and click "OK" to apply the permanent formatting.
Frequently Asked Questions
Why does my PivotTable lose its formatting when refreshed?
This happens when standard cell formatting (using the formatting tools on the Home tab) is applied to the static worksheet grid. When a PivotTable refreshes, it clears and reconstructs the data structure, which wipes out standard cell formats. Using "Value Field Settings" to format the data binds the format to the PivotTable field itself.
Can I apply conditional formatting to a PivotTable that survives a refresh?
Yes. Apply the conditional formatting rule to a cell in the PivotTable, then click the Formatting Options icon that appears next to the cell. Choose the option to apply the rule to "All cells showing [Field] values" rather than just the currently selected cells.
Where is the Number Format option if my field is in the Rows area?
If your data field is placed in the "Rows" or "Columns" area instead of the "Values" area, right-click the field label header and select "Field Settings". You will find the "Number Format" button at the bottom of this dialog box.
How do I change the default currency symbol in a PivotTable?
When adjusting the format via Value Field Settings -> Number Format, select the "Currency" or "Accounting" category. You can then use the "Symbol" drop-down menu to select a different currency symbol, such as Euros or Pounds, before clicking OK.
