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):
(The digit at the end is a zero)
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
Contributions from the AccountingWEB.co.uk editorial team.