When building pivot tables, all users face the same problem every time. The formats for displaying data in the summary need to be configured each time – by default, all fields are displayed in the format General (General), which leads to the appearance of long “tails” after a decimal point of different lengths, incorrect display of monetary amounts and percentages:
Renowned author of numerous books on Excel and VBA programming Mike Alexander on his blog, he offers a simple and useful macro that sets up the number formats of the fields in the pivot table, taking them from the source table. Also, this macro replaces the cumbersome standard field names like “Amount by the Revenue field” with simple and compact ones:
Absolutely brilliant stuff. I can’t resist reposting his macro with minimal corrections here for the speaking audience.
Sub AdoptSourceFormatting() 'Mike Alexander 'www.datapigtechnologies' 'Make sure the active cell is inside the pivot before running the macro. Dim oPivotTable As PivotTable Dim oPivotFields As PivotField Dim oSourceRange As Range Dim strLabel As String Dim strFormat As String Dim i as Integer On Error GoTo MyErr 'Define the data source of the current pivot table Set oPivotTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name) Set oSourceRange = Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1)) 'Refresh the pivot table format for the first cell in the column strLabel = oSourceRange.Cells(1, i).Value strFormat = oSourceRange.Cells(1, i).NumberFormat 'Loop through the fields of the pivot table For Each oPivotFields In oPivotTable.DataFields 'Apply the number format of the data to a field in a pivot table If oPivotFields.SourceName = strLabel Then oPivotFields.NumberFormat = strFormat'Bonus: Change field name to source data column header oPivotFields.Caption = strLabel & " " End If Next oPivotFields Next i Exit Sub 'Error Handling MyErr: If Err.Number = 2 Then MsgBox "First set active cell to PivotTable." Else MsgBox Err.Number & vbCrLf & Err.Description End If End Sub
To use this code, press the keyboard shortcut Alt + F11to enter the Visual Basic Editor. Then insert the new module through the menu Insert – Module and copy the code of this macro there. It remains to set the active cell to any place in your pivot table and run the macro through the dialog box Macros (Alt + F8).
- What are pivot tables and how to build them