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:

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

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)