Automatic Default Number Formatting in Excel Pivot Tables

Bottom line: Save tons of time by automatically formatting the numbers in the Values area of your pivot tables with a VBA macro.  Number formatting is automatically applied when you drag a field into the Values area in one single step.

Skill level: Intermediate

Video

Watch the video at the top of the page to see the automatic number formatting in action.

Download the File

Download the file that contains the macros.

The 2nd video that explains the macro is available below.

The Problem with Pivot Table Number Formats

Typically when we add a field to the values area of a pivot table, the cells are formatted with the General number format.  This is the default format for all cells on the worksheet, and the pivot table does NOT change it.

Unfortunately, the General number format makes numbers look UGLY.  As you can see in the screenshot below, the General format does not add commas/periods for thousands separators the number of decimal places is not fixed.

After adding a field to the values area, we then have to take extra steps to format the numbers.  This is a multi-step process that requires several clicks.  There are a few different ways to apply number formatting to the values area, and it can be time consuming process.

So, how can we change this default number formatting to something besides General?

I've created a macro that automatically applies number formatting when you add a field to the values area.  I have also added this feature to The PivotPal Add-in.  If you don't want to mess with macros then you can download and install the add-in to get this feature.

The Automatic Number Formatting Macro

The macro automatically formats the numbers in the values area of the pivot table when we add a field to the values area in the Pivot Table Fields List.  It's a one-step process.  You do NOT have to take any extra steps to format the numbers.

Which number format does it use?

The macro detects the number format that is used in the source data range of the field.  In the example below, the Revenue column in the source data range is formatted with the Accounting number format.  So, the macro detects this and automatically applies that number format to the Revenue field in the values area.

If the column in the source data range is not formatted, then a default number format is applied.  The default number format is currently set to the Comma[0] format (#,000), but you can change this in the macro code or PivotPal Add-in.

The default number format is also used if the source data range does not exist in the workbook or is in the Data Model.

This macro should save you a ton of time with formatting numbers in your pivot tables.  You can apply formatting to the source data range once, and automatically apply it to all pivot tables you create/modify in the future.

The macro will work on any existing pivot table or new pivot table.

The Automatic Number Formatting can be turned on/off by clicking a button on the right-click menu of the pivot table.  This button is added with a macro that is included in the download file.

How Can I Use This Feature?

There are two ways to implement this feature.

Option 1 – Add the Macros to your Personal Macro Workbook

You can download the file that contains the macros, and add the code modules to your Personal Macro Workbook (PMW).

The file is free to download and you can also modify the code.  In the video below I explain how to install the macros in your PMW, and also walk through how it all works.

Checkout my article and video series on the Personal Macro Workbook to learn all about this tool belt for Excel.

Option 2 – Get the PivotPal Add-in

This feature has also been added to The PivotPal Add-in.  This is a paid add-in.

You can download and install PivotPal to get this feature.  You don't have to know VBA or mess with any code for this option.  Just download and install the add-in, and the Auto Number Formatting button will be added to your right-click menu.

Click here to learn more about PivotPal

How Does The Macro Work?

The video below explains how the macro works in detail.

This solution works by using the PivotTableUpdate event to automatically run a macro when any changes are made to a pivot table.

The macro uses a For Next Loop to loop through all fields in the values area (data fields), and checks the NumberFormat property to see if they are formatted with the General format.  If a field contains General formatting, then the macro attempts to get the formatting for the field in the source data range.  If that is not found or is General, then the macro applies the default number format that you set.  So either way, the cells in the values area will NEVER be formatted with the General format.

You can turn the auto number formatting off at any time through the button in the right-click menu if you do want to keep the numbers formatted as General.

The solution uses Application Events to monitor changes to any pivot table in any open workbook.  This means you can add the code modules to your Personal Macro Workbook, or an add-in file, the code will run on any open workbook.  You do NOT need to add the macros to every workbook that contains pivot tables.

Here are two good articles on Application Events by Chip Pearson and Microsoft.

The code modules also contain macros to add the Automatic Number Formatting button to the right-click menu (context menu).  This works as a toggle button that allows you to turn the feature on/off.  Here is a great article by Ron de Bruin on programming context menus.

*Important Note: This solution is a complete workaround to a very common and time consuming problem in Excel.  Currently, the PivotTableUpdate event will fire when any change is made to the pivot table, or when the pivot table is refreshed.

So, if you have an existing pivot table that has General formatting, and you refresh the pivot table, the macro will run to change the formatting to the source/default number format.  If you want to keep the number format as General in the pivot table, then turn Auto Number Formatting off before refreshing/modifying the pivot table.

If the field in the values area has already been formatted with something other than General, then the macro will NOT change the number format when a refresh/modification is made.  The macro ONLY changes the number formatting from General to the source/default format.

Future Features

I have been using this feature for a few weeks and already have some ideas to improve it.

#1 – Only run the macro when a new field is added to the values area.

As I mentioned above, the macro runs any time the pivot table is changed or refreshed.  This hasn't caused any issues for me so far because I never want to leave the values area formatted as General.  However, we might be able to improve this so the macro only runs when a new field is added to the values area.  This would likely require us to store how many datafields are currently in the active pivot table with additional monitoring through the Worksheet_Change and Worksheet_Activate events.

#2 – Intelligent Number Formatting

Bringing in the formatting from the source data range is great, but in some cases the aggregation causes a need for a different format.  For example, we might use a standard Accounting number format in the source data column for Revenue.  When we add that field to the values area it might get summed to much larger numbers.  So, we might want to use a thousands ($500K) or millions ($5.5M) number format instead.

It's possible for the macro to detect this as well, and even set thresholds or rules for what number formatting should be applied based on the max, min, or average of the values in the values area.

Another option is to put your favorite number formats in the right-click menu for easy access.  There's a sneak peek at a future feature… 😉

Again, if macros aren't your thing, the Automatic Number Formatting feature is included with my PivotPal Add-in.

What suggestions do you have for features?  Please leave a comment below with suggestions or questions.  Thanks!