Automatic Default Number Formatting in Excel Pivot Tables - Excel Campus
22

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

Number Formatting Automatically Applied GIF2

 

Video

Watch the video below to see the automatic number formatting in action.

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Download the File

Download the file that contains the macros.

Pivot Table Automatic Number Formatting Macro.xlsm (28.4 KB)

The PivotPal Add-in (paid) also has this feature and can be downloaded and installed.

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.

Pivot Table General Formatting Used by Default

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.

Number Formatting Automatically Applied Field Added to Values Area

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.

Number Formatting from Source Data Range is Detected and Applied

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.

Default Number Formatting Applied if Source Data Column is Not Formatted

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.

Automatic Number Formatting Pivot Table Right Click Menu Button

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).

Pivot Table Automatic Number Formatting Macro.xlsm (28.4 KB)

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.

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

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

Class Module for PivotTableUpdate Auto Number Formatting App Event

Click to Enlarge

 

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.

Intelligent Automatic Number Formatting for Pivot Tables

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… 😉

My Number Formats Pivot Table Right Click Menu

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!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 22 comments
ofir - August 12, 2017

Hi,Excellent tutorial.what is the field?
what you ment when you say field in pivot table?
i want to refer to each cell in the pivot table(value only):
For Each cell In __________
cell.NumberFormat = “#,##0” & “E-3”
Next cell
is it true?

Reply
Belita - August 11, 2017

Hi Jon,

Love the macro. What a time saver. Doesn’t work with fields created by formulas inserted into a pivot table. How can I make that work?

Reply
Joan - July 25, 2017

Excellent tutorial. Thanks for taking the time to explain how VBA works.

Reply
Marek - July 25, 2017

Hi, it is very useful. I try to get it running but I do not see “VBA Project (PERSONAL.xlsb)” to copy the modules in. What should I do?

Marek

Reply
Tim - July 24, 2017

This is awesome. This is something I do every day and if I don’t do it exactly right, it reverts back on refresh. This solves it. Thank you.

Reply
Jim - July 23, 2017

Jon,

Many thanks for the Automatic Number Formatting in PivotPal. I was very happy to see that this new addition also works on text when put into the values field. Great Job!!!

Reply
Edil Poulina - July 23, 2017

Hi Jon,

This is a very useful macro for me.
And it will certainly save me quite some time formatting.

Thanks again,

Edil

Reply
Rick - July 22, 2017

Hi Jon
Your Automatic Number Formatting for Pivot Tables macro does not work as it has an error in macro code yellow shaded:
‘——————— APP EVENTS ————————
‘Resources on App Events
‘http://www.cpearson.com/excel/AppEvent.aspx
‘https://support.microsoft.com/en-us/help/213566/how-to-create-application-level-event-handlers-in-excel
Sub ToggleAppEventObject()
With CommandBars(“PivotTable Context Menu”).Controls(“Automatic Number Formatting”)
If .State = msoButtonDown Then
.State = msoButtonUp
Call KillAppEventObject
Else
.State = msoButtonDown
SetAppEventObject
End If
End With

End Sub

Sub SetAppEventObject()
Set cObject.appEvent = Application
End Sub
Sub KillAppEventObject()
Set cObject.appEvent = Nothing

Reply
Peter - July 21, 2017

Hi Jon,

When I downloaded the file, the code in the actual file differs from the video. Would you be able to send a copy with the code from the video?

Thanks!
Peter

Reply
    Jon Acampora - July 21, 2017

    Hi Peter,
    Hmm… The code should be the same. I just double-checked and didn’t see any difference. I did add a few comments to the code to help describe some of code blocks. Those comment lines are in green and start with apostrophes. The comment lines do not run when the macros run, they are just developer notes.

    Otherwise, everything should be the same. Let me know if there is a specific macro or function where you find differences. Thanks!

    Reply
Chuck - July 21, 2017

Excellent! Thanks for the tips.

Reply
DILSHAD AHMAD - July 21, 2017

Hi, John

This very help full Post

WITH Love & respect

Dilshad

Reply
Anthony L Lockly - July 21, 2017

Jon, thanks again for providing another fantastic Excel tool!

Reply
Andrew Evans - July 21, 2017

A very useful utility. One thing that has always bugged me is the way Excel does not format fields as their source formatting. It not only applies to numbers but dates as well. The column width changing if you make a change to table after setting all the widths up is something else you sort with a macro.

Reply
    Jon Acampora - July 24, 2017

    Thanks Andrew. Yes, I agree with the date formatting.

    There is an option in the Pivot Table Options menu to turn off “Autofit column widths on update”.

    If you are referring to regular Excel tables, then yes this can still be an issue.

    Thanks again!

    Reply
Alexander Balleh - July 21, 2017

Is there a short course on how to find your personal macro workbook or find it if it has been removed? That was the immediate roadblock i ran into when trying to add this. I may have lost it when trouble shooting prior macro installs before

Reply
    Alan Mueller - July 21, 2017

    The Personal workbook is usually hidden. You can make it visible in the View tab, Windows section. Click Unhide and choose Personal.

    If you don’t have one, the quickest way to create one is to record a short macro. The record button is in the bottom left corner of the Excel window.

    Reply
    Jon Acampora - July 21, 2017

    Hi Alexander,
    Great question! Yes, I have a video series on the Personal Macro Workbook (PMW). Video #2 is on where the Personal Macro Workbook is Stored on your computer, and how to view it. Here is a direct link to video #2 on the page that contains all 4 videos.

    https://www.excelcampus.com/vba/personal-macro-workbook-video-series/#vid2

    The PMW is stored in the XLSTART folder on your computer. Another option is to put this file that contains the macro in the XLSTART folder. Any file in the XLSTART folder will automatically open when Excel opens. So, you could just put this file there instead of copying the code modules into your PMW. The drawback is that Excel will take longer to start up if you have a lot of files opening at startup.

    Video #1 on that page explains how to create a new PMW if you can’t find your old one.

    I hope that helps. Thanks!

    Reply

Leave a Reply: