Macro 69: Apply Number Formatting for All Data Items
A PivotTable does not inherently store number formatting in its pivot cache. Formatting takes up
memory; so in order to be as lean as possible, the pivot cache only contains data. Unfortunately, this
results in the need to apply number formatting to every field you add to a PivotTable. This takes from
eight to ten clicks of the mouse for every data field you add. When you have PivotTables that contain
five or more data fields, you’re talking about more than 40 clicks of the mouse!
Ideally, a PivotTable should be able to look back at its source data and adopt the number formatting
from the fields there. The macro outlined in this section is designed to do just that. It recognizes the
number formatting in the PivotTable’s source data and applies the appropriate formatting to each
field automatically.
How it works
Before running this code, you want to make sure that
➤ The source data for your PivotTable is accessible. The macro needs to see it in order to cap-
ture the correct number formatting.
➤ The source data is appropriately formatted. Money fields are formatted as currency, value
fields are formatted as numbers, and so on.
This macro uses the PivotTable SourceData property to find the location of the source data. It then
loops through each column in the source, capturing the header name and the number format of the
first value under each column. After it has that information, the macro determines whether any of the
data fields match the evaluated column. If it finds a match, the number formatting is applied to that
data field.
Sub Macro69()
‘Step 1: Declare your Variables
Dim pt As PivotTable
Dim pf As PivotField
Dim SrcRange As Range
Dim strFormat As String
Dim strLabel As String
Dim i As Integer
‘Step 2: Point to the PivotTable in the activecell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a
PivotTable.” Exit Sub
End If
‘Step 4: Capture the source range
Set SrcRange = _
Range(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1))
‘Step 5: Start looping through the columns in source
range For i = 1 To SrcRange.Columns.Count
‘Step 6: Trap the source column name and number format
strLabel = SrcRange.Cells(1, i).Value
strFormat = SrcRange.Cells(2, i).NumberFormat
‘Step 7: Loop through the fields PivotTable data area
For Each pf In pt.DataFields
‘Step 8: Check for match on SourceName then apply format
If pf.SourceName = strLabel Then
pf.NumberFormat = strFormat
End If
Next pf
Next i
End Sub
1. Step 1 declares six variables. It uses pt as the memory container for our PivotTable and pf as
a memory container for our data fields. The SrcRange variable holds the data range for the
source data. The strFormat and strLabel variables are both text string variables used to
hold the source column label and number formatting respectively. The i variable serves as a
counter, helping us enumerate through the columns of the source data range.
2. The active cell must be inside a PivotTable for this macro to run. The assumption is that when
the cursor is inside a particular PivotTable, we want to perform the macro action on that
pivot.
Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found.
We do this by using the ActiveCell.PivotTable.Name property to get the name of
the target pivot.
If the active cell is not inside a PivotTable, an error is thrown. This is why the macro uses the
On Error Resume Next statement. This tells Excel to continue with the macro if there is
an error.
3. Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt vari-
able is set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be
assigned to the variable. If this is the case, we tell the user in a message box, and then we exit
the procedure.
4. If the macro reaches Step 4, it has successfully pointed to a PivotTable. We immediately fill
our SrcRange object variable with the PivotTable’s source data range.
All PivotTables have a SourceData property that points to the address of its source.
Unfortunately, the address is stored in the R1C1 reference style — like this: ‘Raw
Data’!R3C1:R59470C14. Range objects cannot use the R1C1 style, so we need the
address to be converted to ‘Raw Data’!$A$3:$N$59470.
This is a simple enough fix. We simply pass the SourceData property through the
Application.ConvertFormula function. This handy function converts ranges to and
from the R1C1 reference style.
5. After the range is captured, the macro starts looping through the columns in the source
range. In this case, we manage the looping by using the i integer as an index number for the
columns in the source range. We start the index number at 1 and end it at the maximum
number of rows in the source range.
6. As the macro loops through the columns in the source range, we capture the column header
label and the column format.
We do this with the aid of the Cells item. The Cells item gives us an extremely handy
way of selecting ranges through code. It requires only relative row and column positions as
parameters. Cells(1,1) translates to row 1, column 1 (or the header row of the first col-
umn). Cells(2, 1) translates to row 2, column 1 (or the first value in the first column).
strLabel is filled by the header label taken from row 1 of the column that is selected.
strFormat is filled with the number formatting from row 2 of the column that is selected.
7. At this point, the macro has connected with the PivotTable’s source data and captured the
first column name and number formatting for that column. Now it starts looping through the
data fields in the PivotTable.
8. Step 8 simply compares each data field to see if its source matches the name in
strLabel. If it does, that means the number formatting captured in strFormat belongs
to that data field.
9. After all data fields have been evaluated, the macro increments i to the next column in the
source range. After all columns have been evaluated, the macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
