How to Display Currency Symbols in Grouped Excel PivotTable Values
Question details
The user needs to restore or display currency symbols for numeric values that have been grouped into ranges within a PivotTable.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating numeric groups (such as price or sales ranges) in a PivotTable where the formatting is lost.
- Observed behavior
- Currency symbols disappear when values are grouped, as the newly grouped PivotTable fields default to general formatting instead of retaining the original currency format.
Ensure your source data is correctly formatted as numbers or currency before creating the PivotTable. Refresh your PivotTable to capture the latest data changes before applying new formatting rules.
Apply Number Formatting via Value Field Settings
Directly format the grouped field using the built-in Value Field Settings to force the display of currency symbols.
When Excel creates a group, it treats the new group labels as a separate entity from the source numbers. You can manually re-apply the currency formatting directly to the PivotTable field.
Right-click on one of the grouped values in your PivotTable and select 'Value Field Settings' or 'Field Settings' from the context menu.
In the dialog box that appears, click the 'Number Format' button located in the bottom left corner.
Select 'Currency' or 'Accounting' from the category list. Choose your desired currency symbol, adjust the decimal places if necessary, and click 'OK' twice to apply the changes.

Create a Helper Column in the Source Data
If the built-in grouping drops the symbol entirely, you can create a new column in your raw data that manually groups and formats the text.
Use Data Model and DAX Measures
For advanced users, adding the data to the Data Model allows you to create custom DAX measures that retain complex formatting.
Format PivotTable Currency Easily with WPS Spreadsheet
WPS Spreadsheet offers a highly compatible and intuitive interface for managing PivotTables, allowing you to seamlessly group data and retain essential currency formats without complex workarounds.
- 1. Open Your Spreadsheet: Launch WPS Spreadsheet and open the workbook containing your PivotTable.
- 2. Select Grouped Values: Highlight or right-click the grouped cells in your PivotTable where currency symbols are missing.
- 3. Access Field Settings: Choose 'Value Field Settings' from the right-click context menu.
- 4. Apply Formatting: Click 'Number Format', select the 'Currency' category, pick your desired symbol, and click 'OK' to instantly apply the changes.

Frequently Asked Questions
Why do my currency symbols disappear when grouping PivotTable data?
When numeric data is grouped into ranges, a new field is created for the group labels. By default, the software assigns a 'General' format to this new text-based field, which strips away the underlying Currency or Accounting number formats from the original data.
Can I use grouped text labels for other calculations?
No. Once numeric values are converted into text strings (like '$10 - $20') using helper columns, they are strictly treated as text. You cannot perform mathematical operations like sum or average on these text labels.
Does the Accounting format work differently than Currency in PivotTables?
Both formats will display currency symbols, but the Accounting format aligns the currency symbols and decimal points perfectly in a vertical column, while the Currency format places the symbol directly next to the number. You can select either in the Value Field Settings depending on your visual preference.
How do I ungroup PivotTable data if I make a mistake?
Simply right-click on any cell within the grouped range in your PivotTable and select 'Ungroup' from the context menu. The data will revert to its original individual line items.




