Pivot Table Issues

How to Show Dollar Signs Only on Excel Pivot Table Totals

Emma Brown Oct 1, 2026 869 views

Question details

The user wants to display a dollar sign exclusively on category subtotals or grand totals in a Pivot Table, leaving the standard value cells formatted as regular numbers.

How to Show Dollar Signs Only on Pivot Table Totals

Product: Microsoft Excel
Device & OS: not provided
Scenario: Highlighting financial totals in a Pivot Table summary report so that the grand totals stand out better.
Observed behavior: Applying standard currency formatting through Pivot Table Field Settings changes all values in the column, rather than isolating the formatting to the total rows only.

Before you start

Ensure your Pivot Table layout is finalized before applying custom cell formatting, as adding new fields or heavily restructuring the table can reset manually applied cell formats.

Solution 1Recommended

Use the Format Painter Tool

This is the quickest way to apply currency formatting to specific total cells without altering the Field Settings for the entire Pivot Table.

By formatting a separate cell first and copying its format, you can safely override the default number styling of specific Pivot Table cells.

Format a blank cell

Select an empty cell outside of your Pivot Table, type a dummy number, and apply the Currency or Accounting format from the Home tab.

Activate Format Painter

With the formatted cell selected, click the 'Format Painter' icon located in the Clipboard group on the Home tab.

Apply to totals

Click and drag your cursor over the specific category total or grand total cells in the Pivot Table to apply the dollar sign formatting.

Reapplying Formatting: If you refresh the Pivot Table after adding new rows of data, the cell formatting usually persists. However, changing the structure (like pivoting columns to rows) will require you to reapply the Format Painter.

Solution 2

Manually Select and Format Total Cells

You can manually select just the total cells and apply standard cell formatting directly from the context menu.

Advanced Pivot Table Features

Easily Format Pivot Tables with WPS Spreadsheet

WPS Spreadsheet offers powerful Pivot Table functionalities with complete compatibility for Microsoft Excel formats. You can quickly customize number formatting for specific cells, subtotals, and grand totals to create professional financial reports without affecting your entire dataset.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your Pivot Table.
2. Select total cells: Hold the Ctrl key and click precisely on the grand total or category total cells you want to format.
3. Access Format Cells: Right-click the selected area and choose 'Format Cells' from the context menu.
4. Choose the Currency format: Under the Number tab, select 'Currency' or 'Accounting' to add the dollar sign.
5. Apply changes: Click OK to instantly update only the selected total cells, leaving the rest of your Pivot Table intact.

High compatibility with Microsoft Excel (.xls, .xlsx) files and existing Pivot Table structures.Intuitive Format Painter and direct cell formatting tools for precise data presentation.Free, lightweight, and fast-loading spreadsheet application.

microsoft office alternative - wps office

Frequently Asked Questions

Why does applying a currency format change all values in the Pivot Table?

When you change the format using 'Value Field Settings' in a Pivot Table, the format is applied to the entire data field uniformly by design. To isolate formatting to totals, you must apply standard cell formatting directly to those specific cells instead of the field itself.

Will my custom dollar signs disappear if I refresh the Pivot Table?

Simple data refreshes usually retain direct cell formatting. However, if you restructure the Pivot Table by adding or removing fields, the cell references will shift, and you may need to reapply the custom formatting to the new locations of the total cells.

Can I automatically format all new subtotals with dollar signs?

Excel Pivot Tables do not have a built-in feature to automatically assign currency symbols only to subtotals. If your data expands frequently, you can use Conditional Formatting as an advanced workaround to automatically apply currency formats to any row containing the word 'Total'.

Excel & Data Problems