Contextures

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.

Default Pivot Chart Number Formatting

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.

numberformat01

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. numberformat01

  4. 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".
  5. numberformat01

  6. Click OK, and the number format is applied to both the pivot table and the pivot chart.
  7. numberformat01

Use 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. numberformat01

  3. In the Format Axis pane, to to the Axis Options tab, and click the arrow to the left of Number, to see the options.
  4. numberformat01

  5. From the Category drop down, select Custom. This automatically removes the check mark from Linked to Source, which disconnects the axis labels from the formatting in the pivot table.
  6. In the Format Code box, type a code for the formatting, such as: #,"K";-#,"K", or select a format from the Type drop down
  7. If you created a new format, click Add, to create the custom number format code.
  8. numberformat01

  9. Close the Format Axis pane, and only the pivot chart has changed -- the pivot table numbers are still in the previous format
  10. numberformat01

Change Number Format in All Pivot Charts With VBA

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

Download 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.

Excel Chart Utility

If you do lots of work with charts in Excel, save time with Jon Peltier's Excel Chart Utility.

Peltier Tech Charts for Excel 3.0

Related Tutorials

FAQs - Pivot Tables

Pivot Chart Field List Macro

Pivot Table Introduction

Pivot Chart Source Data

30 Excel Functions in 30 Days

 

 

Contextures
Excel news
by email

 

 

 

 

pivot power premium

 

 

 

pivot xtreme

 

pivot power premium

 


Last updated: September 11, 2017 11:13 AM