Home > Pivot > Charts > Format Pivot Chart Number FormattingWhen 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. |
Default Pivot Chart Number FormatWhen 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. |
Select Chart Number FormatNext, follow these steps to set up the new number format for the pivot chart, to replace the default format.
|
Adjust Axis UnitsIf you've changed the units with number formatting, you might need to adjust the Axis Units too.
|
Macro-Change Number Format All Pivot ChartsTo 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.
|
Test Macro in Sample WorkbookTo test this macro, download the sample workbook, unblock the zipped folder, then unzip the folder. Then, follow these steps, to test the macro:
After you run the macro, the axis number format in each chart changes to:
|
Macro Code: Change Pivot Chart Number FormatThe 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.
|
Sub FormatChartNums() Dim ws As Worksheet Dim pc As ChartObject Dim strFmt As String On Error Resume Next strFmt = "#,##0" ' no decimals 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 FileYou 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: August 13, 2023 12:39 PM