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 at the top of the page to see the automatic number formatting in action.

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!

24 comments

Your email address will not be published. Required fields are marked *

  • Hi Jon,
    Great tutorial. At first I got it working, but now I have created a personal macro workbook and I can’t seem to get it working again.
    If I open one of my reports, the macro won’t install automatically and the number formatting stays on General, although the data in the source is formatted as accounting.
    Can you help?
    Thanks in advance.
    Chantall

  • 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?

  • 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?

  • 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

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

  • 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!!!

  • Hi Jon,

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

    Thanks again,

    Edil

  • 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

  • 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

    • 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!

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

    • 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!

  • 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

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

    • 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!

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly