Chart & Visualization Issues

How to Format PivotChart Data Labels in Millions in Excel

John Wilson Oct 10, 2026 872 views

Question details

The user wants to format PivotChart data labels to display large values in millions (such as $1MM), but Excel rejects the attempted custom number format.

How to Format PivotChart Data Labels in Millions in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Customizing data labels in an Excel PivotChart to make large financial numbers easier to read.
Observed behavior: Excel rejects the custom data-label format entered by the user, preventing the values from displaying correctly in millions.

Before you start

Ensure that your PivotChart is already created and that data labels are enabled and visible on the chart before attempting to apply custom number formatting.

Solution 1Recommended

Apply the Correct Custom Number Format Code

Use a specific custom format string containing correct spacing and syntax to display values in millions with positive, negative, and zero formats.

Excel relies on specific syntax to understand custom number formats. If quotation marks or commas are misplaced, Excel will reject the format. Applying the format strictly through the data-label settings ensures your underlying PivotTable data remains untouched.

Select the Data Labels

Click directly on the data labels within your PivotChart to select the entire data label series.

Open Format Data Labels Pane

Right-click one of the selected data labels and choose 'Format Data Labels' from the context menu.

Navigate to Number Settings

In the Format Data Labels task pane on the right side of your screen, scroll down and expand the 'Number' category.

Enter the Custom Format Code

Select 'Custom' in the Category dropdown menu. In the Format Code text box, exactly type `#," MM";(#," MM");-` and click the 'Add' button to apply it.

Apply the Correct Custom Number Format Code

Format Applied Successfully: Your data labels will now instantly abbreviate values to millions (e.g., 1,000,000 becomes 1 MM) while keeping the actual data intact.

Seamless Data Visualization

Format Chart Labels Easily in WPS Office

WPS Spreadsheet offers powerful, intuitive charting capabilities that fully support advanced custom number formats. You can effortlessly scale large numbers in data labels to millions without modifying your original dataset.

1. Insert a PivotChart: Open your dataset in WPS Spreadsheet, go to the Insert tab, and create a PivotChart.
2. Enable Data Labels: Click the Chart Elements icon next to the chart and check the box for Data Labels.
3. Access Number Formatting: Right-click the data labels, select 'Format Data Labels', and locate the Number options.
4. Apply Custom Format: Choose 'Custom' and input `#," MM";(#," MM");-` to automatically scale the display values into millions.

100% format compatibility with Microsoft Excel (.xlsx) filesEasily apply advanced custom number formats to chart data labelsIntuitive drag-and-drop PivotChart builderFree and lightweight alternative for advanced data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why is Excel rejecting my custom number format?

Excel will reject a custom format if the syntax is incorrect. Common errors include missing quotation marks around text strings like " MM" or omitting semicolons that separate the positive, negative, and zero conditions.

How do I format data labels in thousands instead of millions?

To format values in thousands, you only need one comma after the number placeholder in your format code. For example, use `#," K";(#," K");-` to display thousands.

Will changing the data label format affect my PivotTable source data?

No. Modifying the custom number format of PivotChart data labels only changes how the data is visually represented on the chart. Your underlying PivotTable values and source data remain completely unchanged.

Can I add a currency symbol to the millions format?

Yes. To include a currency symbol, place it at the beginning of the format code. For example, use `$#," MM";($#," MM");-` to display values like $1 MM.

Excel & Data Problems