Home > Pivot > Format > Numbers Format Pivot Table Numbers Use macros to format pivot table numbers -- apply General format, or copy number formatting from the source data. Download the free workbook to test the macro, which works with pivot tables that are based on a named Excel table, or a named range.

Introduction

The sample file contains two macros for number formatting in a pivot table.

• The first macro sets all the value fields to the General format. You could modify this code, to apply a different format.
• The next macro sets the number formatting for each field in the Values area, by copying the formatting from the matching field in the source data.

In the free workbook that you can download, there are buttons to run the macros.

General Format Macro

The first macro sets all the value fields to the General format. You could modify this code, to apply a different format.

Store this macro in a regular code module, and run it when you want to reset the pivot table number formatting, before testing the Source Formatting macro.

Before you run the macro, select a cell in the pivot table. If a pivot cell is not selected, the macro shows a message, and then exits the macro.

Sub NumFormatPTGen()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveCell.PivotTable
If pt Is Nothing Then
   MsgBox "Please select a cell in a pivot table"
   Exit Sub
End If

For Each pf In pt.DataFields
   pf.NumberFormat = "General"
Next pf

End Sub

Source Format Macro

This macro sets the number formatting for each field in the Values area, by copying the formatting from the matching field in the source data. The formatting from the first cell below the heading row is used, so make sure that row has the formatting that you want applied.

Store this macro in a regular code module, and run it when you want to format the Value fields, based on the number formatting in the source data.

Before you run the macro, you must select a cell in the pivot table. If a pivot cell is not selected, the macro shows a message, and then exits the macro.

• The code checks the source data, to see if it is a named range.
• If it isn't a named range, it goes to each worksheet, and looks through all the tables on each sheet, to see if there is a table with the same name as the source data.
• If the source data isn't a named range or table, the macro shows a message, and then exits the macro.
• If the macro finds the source data, if formats each field in the Values area, based on that field's formatting in the source data.

Sub NumFormatPTSource()
Dim pt As PivotTable
Dim pf As PivotField
Dim pc As PivotCache
Dim strSD As String
Dim nmSD As Name
Dim rngSD As Range
Dim rngHead As Range
Dim strSDType As String
Dim strWs As String
Dim lst As ListObject
Dim ws As Worksheet
Dim lApos As Long
Dim lCol As Long
Dim strNF As String
Dim c As Range
Dim strSD2 As String
Dim strSD3 As String

On Error Resume Next
Set pt = ActiveCell.PivotTable

If pt Is Nothing Then
   MsgBox "Please select a cell in a pivot table"
   GoTo exitHandler
End If

Set pc = pt.PivotCache
strSD = pc.SourceData
Set nmSD = ActiveWorkbook.Names(strSD)

Set rngSD = ActiveWorkbook.Names(strSD).RefersToRange
If Not rngSD Is Nothing Then
   strSDType = "Name"
   Set rngHead = rngSD.Rows(1)
End If

If strSDType = "" Then
   For Each ws In ActiveWorkbook.Worksheets
      For Each lst In ws.ListObjects
         If lst.Name = strSD Then
            Set rngHead = lst.HeaderRowRange
            strWs = lst.Parent.Name
            strSDType = "Table"
            Exit For
            Exit For
         End If
      Next lst
   Next ws
End If

If strSDType = "" Then
   MsgBox "Pivot table is not based on table or named range."
   GoTo exitHandler
End If

For Each pf In pt.DataFields
   For Each c In rngHead.Cells
      If c.Value = pf.SourceName Then
         pf.NumberFormat = c.Offset(1, 0).NumberFormat
         Exit For
      End If
   Next c
Next pf

exitHandler:
 Set pf = Nothing
 Set pt = Nothing
 Exit Sub
 
End Sub

Download the Sample File

To see how the macro works, download the Pivot Table Number Formatting file. The workbook is zipped, and in xlsm format, and the workbook contains macros.

More Pivot Table Resources

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table