PivotTable users frequently spend time assigning the same number format to PivotTable values. To my knowledge, there isn’t a built-in setting that allows us to define a default value field format. But, it is pretty easy to set up a macro that instantly assigns a desired format. This post walks through the steps of creating such a macro.
Generally, PivotTable value fields are automatically assigned the General format, as shown below.
To clean up the report, we manually change the format of the value field, to something such as a number, no decimals, with a comma, as shown below.
Rather than perform this task manually for each value field in each PivotTable we create, we can set up a macro to apply a specific format.
Overall, we’ll use the macro recorder to have Excel prepare a basic starter macro and save it in the Personal Macro Workbook. Then, we’ll modify the macro so it can be used on new PivotTables going forward.
Our steps will be:
- Record a macro that formats a cell
- Edit the macro
- Set up a QAT button
- Clean up
Let’s get started.
Record a macro
We will ask Excel to watch us apply the desired format to any random cell and save the recording in the Personal Macro Workbook. To do this, we simply start the macro recorder by clicking the following ribbon command:
- View > Macros > Record Macro
In the resulting Record Macro dialog box, we assign a name, avoiding spaces and funky characters, and opt to store the macro in the Personal Macro Workbook, as shown below.
Now…I mean right now…Excel is watching everything you do. So, simply apply the desired number format to the cell that is currently active. You can use any of the standard methods to apply your desired format.
Here, I opened the Format Cells dialog with Ctrl+1, and then assigned a number format, no decimals, with a comma.
Then, STOP the recorder. You can do this with the following ribbon command:
- View > Macros > Stop Recording
Step one is now complete. Since our macro simply formats the active cell, we need to make it a little bit smarter.
Edit the macro
We need to update the macro and tell it to apply this format to all PivotTables on the active worksheet. Since the macro is stored in the Personal Macro Workbook, we’ll need to first unhide this workbook in order to edit the macro.
To unhide the Personal Macro Workbook, simply click the following ribbon icon:
- View > Unhide
In the resulting Unhide dialog box, select PERSONAL.XLSB as shown below.
With the Personal Macro Workbook visible, we can now edit any macros it contains. To do so, we’ll open the Macro dialog by clicking the following ribbon icon.
- View > Macros > View Macros
In the resulting Macro dialog box, as shown below, we simply pick our new macro and click the Edit button.
Doing so opens the Visual Basic Editor and places the cursor inside our new macro, as shown below.
The macro simply applies the specified number format to the active cell. We want our macro to apply this formatting to all value fields in all of the PivotTables on the active worksheet.
Thus, we’ll make a few changes, as follows.
Sub PTValueFormat() For Each pt In ActiveSheet.PivotTables For Each df In pt.DataFields df.NumberFormat = "#,##0" Next df Next pt End Sub
Note: you can copy the code above and paste it into your visual basic editor instead of typing it.
Or, we could add an additional loop so that the macro assigns the format to all value fields, in all PivotTables, on all sheets in the active workbook. Here is the code for that version.
Sub PTValueFormatAllSheets() For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables For Each df In pt.DataFields df.NumberFormat = "#,##0" Next df Next pt Next ws End Sub
Now, anytime we run this macro, Excel will apply the desired format to the PivotTable value fields. Let’s make the macro easy to run by setting up a QAT icon.
To set up a QAT (Quick Access Toolbar) icon, we simply open the Excel Options dialog with the following:
- File > Options
Click the Quick Access Toolbar category on the left side of the dialog, and then you want to Choose commands from Macros, as shown below.
Select the new macro in the left list box and then click the Add button. The macro will appear in the right list box, and then you click OK to close the dialog.
Now, anytime you want to assign the desired format to your PivotTable value fields, just click the new QAT icon.
Since we previously used the Unhide command to show the Personal Macro Workbook, we’ll probably want to hide it again. To do so, make it the active workbook and then click the following ribbon icon:
- View > Hide
Also, since we want to save our new macro, we’ll want to be sure to click Save to the following dialog when existing Excel.
And that is how to set up a macro to apply a desired format to PivotTable value fields. If you have any other fun PivotTable formatting tricks or formatting macros, please share by posting a comment below…thanks!
- Sample file with macro: PTValueFormat