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.
The sample file contains two macros for number formatting in a pivot table.
In the free workbook that you can download, there are buttons to run the macros.
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
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.
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
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.
Last updated: May 10, 2021 3:53 PM