Pivot Chart Number Formatting

When a pivot chart is created, 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.

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. 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".
   • 
4. 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:

1. In the pivot chart, right-click a number in the axis, and then click Format Axis.
• 
2. In the Format Axis pane, go to the Axis Options tab, and click the arrow to the left of Number, to see the options.

1. 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.
2. In the Format Code box, type a code for the formatting, such as: #,"K";-#,"K"
• Or, select a format from the Type drop down
3. If you created a new format, click Add, to create the custom number format code.
• 
4. Close the Format Axis pane, and only the pivot chart number format has changed. The pivot table numbers are still in the previous format
   • 

TIP: 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, use can use and Excel macro. In the example below, all the pivot charts have their Value Axis number formatting changed to Number, with zero decimals, and a separator -- "#,##0"

TIP: To use a different number format, change the setting for the strFmt variable, near the top of the macro code.

To use this macro,

• Copy the code to a regular code module.
• Next, MAKE A BACKUP of your file, before running the macro.
• Then, to run the macro, click the View tab on the Ribbon, and click the Macros command.
• In the list of macros, select FormatChartNums, and click Run.

Sub FormatChartNums()
Dim ws As Worksheet
Dim pc As ChartObject
Dim strFmt As String
On Error Resume Next
strFmt = "#,##0"

For Each Ws In ActiveWorkbook.Worksheets
  For Each PC In ws.ChartObjects
    With pc.Chart
      'test for pivot charts
      If Not .PivotLayout Is Nothing Then
        .Axes(xlValue).TickLabels _
          .NumberFormat = strFmt
      End If
    End With
  Next PC
Next Ws

End Sub

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.

Related Tutorials

FAQs - Pivot Tables

Pivot Chart Field List Macro

Pivot Chart Compare Years

Pivot Table Introduction

Pivot Chart Source Data