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. 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:
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"
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 then 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
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.
If you do lots of work with charts in Excel, save time with Jon Peltier's Excel Chart Utility.
Last updated: November 23, 2017 10:35 AM