Home > Pivot > Charts > Format

Pivot Chart Number Formatting

When a pivot chart is created in Microsoft Excel, the numbers on the chart's axis are formatted the same as the pivot table's numbers. See how to change the number format in both places, or in just the pivot table. For example, show pivot chart numbers in thousands.

Video: Change Pivot Chart Number Format

When you create a pivot chart from a pivot table, the numbers on the chart's axis have the same number format as the pivot table's numbers. This short video shows the steps for changing the pivot chart number format, and there are written steps below the video.

Video Timeline:

• 0:00 Introduction
• 0:22 Create a Pivot Chart
• 0:41 Change Number Format
• 1:16 Number Format Chart Only
• 1:35 Number Options
• 1:53 Custom Number Format
• 2:36 Get the Workbook

Default Pivot Chart Number Format

When you create a pivot chart from a pivot table, the numbers on the chart's axis have the same number format as the pivot table's numbers. In the screen shot below, the numbers are in General format. There is no comma separator, and no decimals.

Follow the instructions below, to change the formatting in both places, or in just the pivot chart. There is also a sample macro that will change the number formatting for all the pivot charts in the active workbook.

Change Pivot Table and Chart Number Format

To change the number format in both the Pivot Table and Pivot Chart, follow the steps below.

1. In the pivot table, right-click on a cell in the value field. In this example, the Cases field is used.
2. In the popup menu, click Value Field Settings, and then click the Number Format button
   • 
3. In the Format Cells dialog box, select the formatting that you want.
4. In the screen shot below, the Number category is selected, with decimal places set to 0, and a check mark was added to "Use 1000 Separator".
   • 
5. Click OK, twice, and the number format is applied to both the pivot table and the pivot chart.
   • 

Different Number Format in Pivot Chart

In some cases, you might want the pivot chart to have a number format that is different from the pivot table. In this example, the pivot chart will get a custom format to show the numbers as thousands, so the numbers take less room.

Follow these steps to change the pivot chart number format, without affecting the pivot table:

• In the pivot chart, right-click a number in the axis, and then click Format Axis.

• In the Format Axis pane, go to the Axis Options tab, and click the arrow to the left of Number, to see the options.

• From the Category drop down, select Custom.

• This automatically removes the check mark from Linked to Source, and disconnects the axis labels from the formatting in the pivot table.

Select Chart Number Format

Next, follow these steps to set up the new number format for the pivot chart, to replace the default format.

• In the Format Code box, type a code for the formatting, such as: #,"K";-#,"K"

• Or, select a format from the Type drop down

• If you created a new format, click Add, to create the custom number format code.

• Close the Format Axis pane, and only the pivot chart number format has changed. The pivot table numbers are still in the previous format

Adjust Axis Units

If you've changed the units with number formatting, you might need to adjust the Axis Units too.

• Click the arrow to the left of Axis Options, to see the settings.
• In the Units section, enter a number for Major that works with your number formatting.
  • For example, I entered 1000, because my pivot chart axis shows the values in thousands.

Macro-Change Number Format All Pivot Charts

To change the number formatting for all the pivot charts in the active workbook, you can use an Excel macro, like the Excel VBA code shown in the next section.

You can test this macro in the sample workbook, where there are two pivot charts, on two different worksheets.

• The first pivot chart's axis has numbers formatted as thousands
• The second pivot chart's axis has numbers formatted as as general (no separator, no decimals)

Test Macro in Sample Workbook

To test this macro, download the sample workbook, unblock the zipped folder, then unzip the folder. Then, follow these steps, to test the macro:

• First, MAKE A BACKUP of your file, before running the macro.
• Next, open the Excel file, and in the security warning bar, click the Enable Content button, to allow macros to run
• Then, to run the macro, go to the View tab on the Excel Ribbon
• At the right end, click the Macros command, to open the Macros dialog box.
• In the list of macros, select FormatChartNums
• Then, click the Run button, to apply the number format to all pivot charts

After you run the macro, the axis number format in each chart changes to:

• Number, with zero decimal places, and a thousand separator
• "#,##0"

Macro Code: Change Pivot Chart Number Format

The following Excel macro changes the axis number format for all pivot charts in the workbook.

To use this code in your own Excel files, copy the code, and paste it into a regular code module. Then, follow the steps in the previous section, to run the macro.

• Tip: You can use a different number format for the strFmt variable.
  • For example, percentage, no decimals:      "0%"
  • Tip: Record a macro while you apply number formatting, then get the number format string from the recorded code.

Get the Sample File

You can download a copy of the Pivot Chart number formatting sample file, to test the number formatting macro. The zipped file is in xlsm (macro-enabled) format. After you unzip the file and open it, enable macros, so you can use the macros.