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.
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.
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.
To change the number format in both the Pivot Table and Pivot Chart, follow the steps below.
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:
TIP: If you've changed the units with number formatting, you might need to adjust the Axis Units too.
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,
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
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.
Last updated: March 15, 2021 8:31 PM