By Chris Wood, CPA
I have an issue with the number formatting in a pivot table. Amounts in the data field of a pivot table always default to “general”.
The problem is I very rarely want to view numbers this way. I prefer comma separated, two decimal places, parenthesis for negative numbers. Like this:
So every time I create a pivot table, I drag the desired value into the data area and click on the comma button in the Home tab to format the number. No big deal, except when I download data from my general ledger software package. Whatever the reason, when I try to format the amount in the pivot table using my comma button, I get nothing. Instead, I have to right click from a cell within the pivot table to access the Field Settings dialog box. Then click the Number Format button, then click on several selections in the Format Cells dialog box to finally get the desired results. It seems to be too much work.
So I created a macro to resolve this pesky problem. I used the macro recorder to format cells to comma style and stored it in my PERSONAL.XLSB folder which simply means I can use it on any open workbook. Here’s how it works.
First, click on the lower part of the Macros button in the View tab. Then click on Record Macro.
Name the macro and store it in your Personal Macro Workbook. Click OK and the dialog boxes disappear, but now every keystroke and/or mouse movement will be automatically recorded.
Simply right click on the current cell the cursor is in and format cells to comma separated, two decimal places, parenthesis for negative numbers. Like this:
Next, click on the lower part of the Macros button in the View tab. Then click on Stop Recording.
Now we have the comma style macro ready to be applied to any values in any open workbook by clicking on the top part of the Macros button, but let’s go one step further and make it easier to use.
Place this macro in the Quick Access Toolbar for one click formatting by clicking on the bar over inverted pyramid symbol to the far right of the Quick Access Toolbar and select More Commands. Select Macros in the Choose commands from drop down menu on the left side of the Excel Options dialog box. Select Macros, then highlight the desired macro and click Add.
Finally, click on the Modify button at the bottom of the Quick Access Toolbar window on the right side of the dialog box, and a series of symbols appears. Select the desired icon to change the view of the macro in the Quick Access Toolbar. Now when I create a pivot table off of data from my general ledger software package, I simply drop the amount in the data field and click on the 8 ball icon for comma style, and away I go.