Pivot Table Issues

How to Display Currency Symbols in Grouped Excel PivotTable Values

Tauseeq MagsiTauseeq Magsi Nov 10, 2026 870 views

Question details

The user needs to restore or display currency symbols for numeric values that have been grouped into ranges within a PivotTable.

How to Display Currency Symbols in Grouped Excel PivotTable Values
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.
Before you start

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.

Solution 1Recommended

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.

1
Access Field Settings

Right-click on one of the grouped values in your PivotTable and select 'Value Field Settings' or 'Field Settings' from the context menu.

2
Open Number Format

In the dialog box that appears, click the 'Number Format' button located in the bottom left corner.

3
Apply Currency Symbol

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.

Apply Number Formatting via Value Field Settings
WPS Spreadsheet Solution

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. 1. Open Your Spreadsheet: Launch WPS Spreadsheet and open the workbook containing your PivotTable.
  2. 2. Select Grouped Values: Highlight or right-click the grouped cells in your PivotTable where currency symbols are missing.
  3. 3. Access Field Settings: Choose 'Value Field Settings' from the right-click context menu.
  4. 4. Apply Formatting: Click 'Number Format', select the 'Currency' category, pick your desired symbol, and click 'OK' to instantly apply the changes.
Highly compatible with Microsoft Excel (.xlsx) PivotTables and formatting.Intuitive Value Field Settings for quick currency symbol adjustments.Retains complex number formatting even after grouping numeric ranges.Free to use with comprehensive data analysis tools.
microsoft office alternative - wps office

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.