Excel tip: Pivot table number format macro

spreadsheet
istock_stratum
Share this content

David Ringstrom, an Excel trainer from across the pond, has concocted a single line of programming code which can automatically format numbers as you add new fields to a pivot table, first published on our US sister site, AccountingWEB.com.

By adding Ringstrom’s code, Excel users escape the frustration caused by manually formatting pivot table fields to add commas or remove decimal places – one field at a time.

First, Ringstrom advises Excel enthusiasts to banish this manual burden by locating their personal macro workbook, so the created macro is available in any Excel workbook.

For those unsure where to find this, simply click the unhide command and fire up Macros. Name your macro without any spaces (for example, PivotTableNumberFormat) and hit create. This launches the visual basic editor.

From here, Ringstrom instructs that you copy and paste these lines of code into the macro (as shown in the image below):

Source: David Ringstrom
Source: David Ringstrom

(The digit at the end is a zero)

Source: David Ringstrom
Source: David Ringstrom

Once added, you can close the visual basic editor.

Next up, Ringstrom suggests adding an icon to your quick access toolbar, so you can run the macro with one click.

To do this, click on the quick access toolbar in the top left corner, select more commands and choose macros from the choose commands list. Select the macro you have named (e.g PivotTableNumberFormat), and from here you can access the macro with one click.

Testing this, select any cell within the pivot table and click on your freshly created quick access icon. This should automatically format the figure, adding the decimal.  

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 Accounting WEB

AccountingWEB logo

Contributions from the AccountingWEB.co.uk editorial team.

Replies

Please login or register to join the discussion.

23rd Sep 2016 14:46

I know this first appeared on the US site, but might UK users not want to have pound signs, not dollar signs.

Thanks (0)
avatar
23rd Sep 2016 15:08

Did you not think to provide us with the code in a format we could copy and paste?

Thanks (0)
23rd Sep 2016 15:41

Here we go, folks. This is a copy and paste version of the macro.

On Error Resume Next

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

On Error GoTo 0

Thanks (0)
to Richard Hattersley
23rd Sep 2016 20:23

#,##0_);(#,##0);-?

Would line the numbers up better and show zeros as a dash. More explanation here:

https://www.accountingweb.co.uk/tech/excel/ez-guide-to-number-formatting

Thanks (2)
avatar
26th Sep 2016 07:31

How do you add double quotes to the format without those double quotes being interpreted as part of the macro code?
Eg, say you wanted the format to be
#,##0_);"("#,##0")"

I am sure this was explained to me once, but I need these things repeated to me every so often due to lack of use.

Thanks
With kind regards
Clint Westwood

Thanks (0)
avatar
to nogammonsinanundoubledgame
26th Sep 2016 10:27

Hi Clint,

In VBA to represent a double quote you need to put it twice, i.e. "". So to add double quotes to the format you specified, in VBA you would write:
#,##0_);""(""#,##0"")""

Curiously I think it might have been me that explained it last time, given that this is one of the few topics I feel qualified to advise on here :)

Thanks (0)
avatar
to Ben Lauritson
27th Sep 2016 15:27

Thanks. This rings a bell now.
So, if you wanted a macro code to string 3 x " together as a string then something like
Dim MyString
Set MyString = """"""""

Thanks (0)
avatar
to nogammonsinanundoubledgame
27th Sep 2016 11:09

That's correct :)

Thanks (0)
to nogammonsinanundoubledgame
26th Sep 2016 10:47

Just as a general suggestion, sometimes the easiest way to sort out the correct syntax for any macro code is to record a temporary macro that does what you want and then go to View, Macros, View Macros, Edit to review the code that Excel generates, and then adapt or copy and paste from there to get the code you need.

Thanks (0)
avatar
26th Sep 2016 20:26

Format code for pounds is:

"[$£-en-GB]#,##0"

which you might expand to

"[$£-en-GB]#,##0;([$£-en-GB]#,##0)"

Whenever you want the format code for a given number format, format a cell in the usual fashion, and then press Ctrl-1 to display the Format Cells dialog box. On the Number tab choose Custom and copy and paste the format code where you need it.

Thanks (1)