A long-standing frustration with pivot tables in Microsoft Excel is that any fields that contain numbers must always be manually formatted to add commas and/or remove decimal places. Further, the formatting changes must be made one field at a time in order to ensure that subsequent changes don’t cause the number formatting to vanish when you refresh your pivot table.
In this article I’ll show how a single line of programming code can cause Excel to automatically format numbers as you add new fields to a pivot table. Let’s first create a pivot table, as shown in Figure 1. The underlying data includes three columns: Product, Cases Sold, and Total Sales. To create a pivot table:
- Click any cell within your source data. Make sure that each column has a unique title that appears within a single cell.
- Select Excel’s Insert menu.
- Click the PivotTable command.
- Click OK within the Create PivotTable dialog box.
- A new worksheet with a blank pivot table will appear.
Figure 1: The PivotTable command appears on Excel’s Insert menu.
The next step is to add fields the pivot table:
- Select Product.
- Select Cases Sold. Notice how this field does not have any number formatting.
- Right-click on Sum of Cases Sold.
- Choose Number Format. If you choose Format Cells you’ll only format a single cell, instead of the entire column.
- Choose Number.
- Optionally set the number of decimal places to 0.
- Check the Use 1000 Separator checkbox.
- Click OK.
- The numbers in the Sum of Cases Sold column are now formatted.
Figure 2: By default pivot tables do not have any number formatting.
Carrying out these steps every time you add a new field to a pivot table can be mind-numbing, so instead we’ll automate this by way of a macro in Excel. The first step is to determine if you have a Personal Macro Workbook. To do so, determine if the Unhide command is disabled on Excel’s View menu. If so, carry out these steps:
- Select Excel’s View menu.
- Click the bottom-half of the Macros button.
- Click Record Macro.
- Select Personal Macro Workbook from the drop-down menu.
- Click OK in the Record Macro dialog box. For this purpose no changes are necessary.
- Click the Stop Recording button, which will be a square next to the word Ready at the bottom of the Excel screen.
Figure 3: Turn the macro recorder on and then off to create a personal macro workbook.
Macros that you place within the Personal Macro Workbook are available for use in any Excel workbook, so these can become extensions of Excel itself. The next step is to create the macro itself, as shown in Figure 4:
- Choose Excel’s View menu.
- Click the Unhide command.
- Click OK to unhide the PERSONAL.XLSB workbook.
- Activate Excel’s View menu again.
- Click the top half of the Macros command.
- Type a name for your macro, such as PivotTableNumberFormat. The first character must be a letter and you cannot have any spaces.
- Click the Create button.
Figure 4: Macros within the Personal Macro Workbook can be used within any other Excel workbook.
At this point the Visual Basic Editor will appear on screen. Copy and paste these lines of code into the macro, as shown in Figure 5:
On Error Resume Next
ActiveSheet.PivotTables(ActiveCell.PivotTable.Name).DataBodyRange.NumberFormat = "#,##0;(#,##0)"
On Error GoTo 0
Figure 5: One line of programming code will set a pivot table to automatically format new columns that you add.
If you choose to type this out by hand, the last character On Error GoTo 0 is a zero. Both On Error lines are related to an instruction to Excel to skip programming code that fails; such as if you were to run this macro when your cursor is not within a pivot table.
At this point you can close the Visual Basic Editor, which will return you to Excel. The Personal Macro Workbook should still appear on screen. Click the Hide command on Excel’s View menu to hide this workbook, which launches automatically when you open Excel, but remains visually hidden to stay out of your way.
The next steps involve adding an icon to your Quick Access Toolbar so that you can run the macro with one click:
- Click the arrow at the end of the Quick Access Toolbar.
- Choose More Commands.
- Choose Macros from the Choose Commands From list.
- Double-click on your macro, which will have a name like PERSONAL.XLSB!PivotTableNumberFormat.
- Optional: CIick Modify to change the icon associated with this command, as well as the caption.
- Click OK.
Figure 6: You can run a macro with one mouse click when you assign it to a Quick Access Toolbar icon.
To test your work, first clear the number formatting from the Sum of Cases Sold column. One way to do so is to select the numbers and then choose General from the list in the Number section of Excel’s Home menu. As shown in Figure 7:
- Click any cell within the pivot table.
- Click the icon you added to the Quick Access Toolbar.
- The numbers in the Sum of Cases Sold column should be formatted again.
- Select Total Sales from the Pivot Table field.
- The new Sum of Total Sales column should appear on your pivot table and be automatically formatted.
Figure 7: The macro puts a pivot table in a state where new fields are automatically assigned number formatting.
You must run this macro on each pivot table that you create, but once you add any number fields they will be automatically formatted. If you wish to include decimal places, change the format code in the macro to "#,##0.00;(#,##0.00)" or use "$#,##0;($#,##0)" if you wish to have dollar signs with no decimal places.
When you close Excel be sure to click Yes or Save when prompted whether to save changes you made to the Personal Macro Workbook. If you wish to remove the Quick Access Toolbar icon you created, right-click on the icon and choose Remove From Toolbar.
About David Ringstrom, CPA
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.