Automatically Formatting Numbers Within Excel Pivot Tables

Spreadsheets and graphs on a desk
xfgiro/istock

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:

  1. Click any cell within your source data. Make sure that each column has a unique title that appears within a single cell.
  2. Select Excel’s Insert menu.
  3. Click the PivotTable command.
  4. Click OK within the Create PivotTable dialog box.
  5. A new worksheet with a blank pivot table will appear.

Pivot Macro 1

Figure 1: The PivotTable command appears on Excel’s Insert menu.

The next step is to add fields the pivot table:

  1. Select Product.
  2. Select Cases Sold. Notice how this field does not have any number formatting.
  3. Right-click on Sum of Cases Sold.
  4. Choose Number Format. If you choose Format Cells you’ll only format a single cell, instead of the entire column.
  5. Choose Number.
  6. Optionally set the number of decimal places to 0.
  7. Check the Use 1000 Separator checkbox.
  8. Click OK.
  9. The numbers in the Sum of Cases Sold column are now formatted.

Pivot Macro 2

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:

  1. Select Excel’s View menu.
  2. Click the bottom-half of the Macros button.
  3. Click Record Macro.
  4. Select Personal Macro Workbook from the drop-down menu.
  5. Click OK in the Record Macro dialog box. For this purpose no changes are necessary.
  6. Click the Stop Recording button, which will be a square next to the word Ready at the bottom of the Excel screen.

Pivot Macro 3

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:

  1. Choose Excel’s View menu.
  2. Click the Unhide command.
  3. Click OK to unhide the PERSONAL.XLSB workbook.
  4. Activate Excel’s View menu again.
  5. Click the top half of the Macros command.
  6. Type a name for your macro, such as PivotTableNumberFormat. The first character must be a letter and you cannot have any spaces.
  7. Click the Create button.

Pivot Macro 4

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

Pivot Macro 5

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:

  1. Click the arrow at the end of the Quick Access Toolbar.
  2. Choose More Commands.
  3. Choose Macros from the Choose Commands From list.
  4. Double-click on your macro, which will have a name like PERSONAL.XLSB!PivotTableNumberFormat.
  5. Optional: CIick Modify to change the icon associated with this command, as well as the caption.
  6. Click OK.

Pivot Macro 6

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:

  1. Click any cell within the pivot table.
  2. Click the icon you added to the Quick Access Toolbar.
  3. The numbers in the Sum of Cases Sold column should be formatted again.
  4. Select Total Sales from the Pivot Table field.
  5. The new Sum of Total Sales column should appear on your pivot table and be automatically formatted.

Pivot Macro 7

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 Ringstrom

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.

Replies

Please login or register to join the discussion.

avatar
By KStrube
Sep 13th 2016 20:00

I tried to copy/paste this code in to VB, making sure I used the same exact title you recommended and that the "o's" were zeros.

Sub PivotTableNumberFormat()

On Error Resume Next
ActiveSheet.PivotTables(ActiveCell.PivotTable.Name).DataBodyRange.NumberFormat = “#,##0;(#,##0)”
On Error GoTo 0
End Sub

I'm getting two errors.
1) My "ActiveSheet..." row is red and is highlighting the first comma near the end of the row with "Compile error: Expected: end of statement"
2) When I try to run the macro, the top row is highlighted in yellow with the error "Compile error: Syntax error."

Where am I going wrong here?

Thanks (1)
Sep 13th 2016 20:40

You didn't do anything wrong, it's a nuance of Excel/Word. In the writing/editing process Microsoft Word uses what are considered "curly" quotes. Excel's Visual Basic Editor requires "straight" quotes. Try copying and pasting this and you should be fine:

On Error Resume Next

ActiveSheet.PivotTables(ActiveCell.PivotTable.Name).DataBodyRange.NumberFormat = "#,##0;(#,##0)"

On Error GoTo 0

Thanks (1)
avatar
By SBBS
to dringstrom
Sep 13th 2016 21:23

I had the same problem, however I deleted that line and entered it maually and it works just fine.
Thank you, this is very useful.

Thanks (2)
avatar
By samrey
Jul 6th 2017 16:07

Anyway to make this similar to the "Accounting" number format where a cell with a value of zero is shown as a hyphen?

Thanks (0)
to samrey
Jul 6th 2017 22:16

Yes, you can use any format code that you want in place of "#,##0;(#,##0)". One way to get there:

1. Format any cell in a spreadsheet with the number format of your choice.

2. Click the arrow in right-hand corner of the Number section of Excel's Home menu.

3. Choose Custom within the Number tab of the Format Cells dialog box.

4. Copy the number format shown to the clipboard.

5. Click OK to close the Format Cells dialog bxo.

6. Paste the number format into the macro in place of the aforementioned "#,##0;(#,##0)". Make sure to keep the double-quotes before and after the number format.

Thanks (0)
avatar
Jul 28th 2017 17:50

I'm an IT gal and constantly dumping data for the Accounting and Finance folks to use. I did a little happy dance when I put this is place. Thank you so very much for making my job a whole lot easier.

Thanks (1)
to clare.smith
Jul 28th 2017 18:09

Thank you, Clare! Your feedback made my day.

Thanks (0)
avatar
Jul 31st 2018 13:54

Hi, thank you very much for your genius idea but I would know if this macro could do the sum in the same time.
Thank you

Thanks (1)