Macro to Quickly Format PivotTable Values
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.
Objective
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.
Detailed Steps
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.
QAT button
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.
Clean up
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!
Resources
- Sample file with macro: PTValueFormat
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
This is great teaching tool. Perfectly communicated, give this guy a raise. How do you create the screen shots and dialogue for a presentation like this?
Thanks Rudy! I just use the snipping tool built-in to Windows…and I appreciate the kind comment!
Thanks
Jeff
Is there a way to modify this so that negative numbers are formatted with parentheses, instead of the “-” symbol?
Hi Amanda!
Yes…absolutely! You can simply update the format code as desired. For example, changing the code to “#,##0;(#,##0)” would format negative numbers with parentheses. You can edit the macro by hand and insert any of the standard Excel format codes…which are actually the same ones used in the Format Cells dialog, Custom format. You can also define the desired format code when you initially record the macro. Either way should work just fine.
Hope it helps!
Thanks,
Jeff
Actually, figured that out – use df.NumberFormat = “#,##0_);(#,##0)”
Can you also create a macro to change ALL numbers in a worksheet to a format, not just those in a pivot table? Thanks!
Great…glad you got it!
Regarding an additional macro to change all number formats…I’ll add that topic to my “things to blog about” list 🙂
Thanks
Jeff
Thanks, that would be great. I found this to be very helpful! I will keep an eye out for that one!
Great blog, thank you! Is there a way to set negative numbers to be displayed in red?
Yes…to do that update the format code to include [Red] for negatives, something like this:
#,###;-#,###[red]
Thanks
Jeff
Thank you, it worked!
🙂
I created the Macro and used it for months. Every time I opened Excel, the Personal.XLSB workbook would open. One morning last week, it didn’t open. I can’t figure out how to get it back. I could find no file when clicking on the Unhide button. Can it be recovered?
Thanks,
Cynthia
I found the workbook! Yay! I just don’t know why it’s not opening when excel is opened. Thank yo
Cynthia