How do I stop Excel from resetting my custom number format when I update my Pivot Table?

[Image] How to lock down your Pivot Table number formatExcel Pivot Tables are probably the single most powerful built-in feature of Excel. If you analyse lots of data, my guess is that Pivot Tables are one of your favorite tools.

They are fast and flexible – they save you lots of time by allowing you to explore data. I often find that checking out data with Pivot Tables gives me faster understanding of the data than using Excel formulas alone.

But Pivot Tables have some drawbacks.

One of those drawbacks is that your carefully chosen formatting is often lost when you change or refresh a Pivot Table. In this article we’ll have a look at how to stop Excel from resetting a custom number format in the Pivot Tables value area.

Reader Question: How do I stop Excel from resetting my custom number format when I update my Pivot Table?

This week we take a look at a question asked by one of our readers – Joanne.

Joanne uses pivot tables and wants to choose a custom number format for her value field. But the problem is that every time she adds a different value field, she loses her custom number format because Excel resets the number format to General.

Let’s take a look at some screenshots. I put together some fictional Fruit Sales data and created a simple Pivot Table of the Order value in $USD for each salesperson by year.

You can download a sample spreadsheet by clicking here

Custom Number Formatting

This is an image showing the custom number format I used in the spreadsheet.

[Image] Custom Number Format in Excel used in my Pivot Table

Here is the Custom Number Format that I used in my Pivot Table

The top row in blue shows what you type into Excel to get the custom number format, and the bottom row in pink shows examples of positive values, negative values and zero values.

There are lots of rules for number formats and if you’re interested in finding out more, feel free to ask me in the comments section below.

Before: Pivot Table with Custom Number Format

When we apply the custom number format to the Pivot Table we get this result:

[Image] Pivot Table value area with custom number format

Here is a screenshot of the Pivot Table showing “Sum of Order value $” in our custom number formatting

After: Pivot Table has lost Custom Number Format

When we change the Sum of Order value $ to Sum of Commission $, we see that the custom number format has been replaced by the General number format:

[Image] Pivot Table value area reverts back to General number format

When the Pivot Table is changed to show “Sum of commission $” we lose our custom formatting

Let’s see how we can solve this problem

In Part 1 we look at how most people change the Pivot Table number format.
In Part 2 we look at how to format the Pivot Table in a more permanent way.
In Part 3 I present a quick VBA/macro solution to automatically update the Pivot Table format.

Part 1 – How to Format the Pivot Table values area to a Custom Number Format (the temporary way)

[Image] Pivot Table values area

The selected range shows the Pivot Table values area

Here are the steps that most people use when they want to change the number format for the Pivot Table values area.

  1. Right click on a number in the values area
  2. Select Value Field Settings from the pop-up menu
  3. Click on the Number Format button
  4. Select the desired Number Format (e.g. number, currency, accounting, custom)

Part 2 – How to Format the Pivot Table values area to a Custom Number Format (a more permanent way)

I put together a video to show how we can format the values area so that our custom number format does not get reset every time we add or remove fields.

Watch it here:


Video Highlights

0:38 – It’s quite hard to read the $USD figures when they are in General number format
0:50 – Let’s format the values into Currency format
1:10 – That’s fantastic we can read the $USD figures much easier now
1:17 – Now let’s see how much Sales Tax we paid
1:23 – Oh no we lost the number format
1:58 – So the question is “How do we keep the number format the same even when we change the value field?”
2:12 – Here’s my answer (see below for step-by-step instructions)
3:20 – One little note: You have got to make sure that your Pivot Table options are set to Preserve Cell Formatting on Update
3:32 – Check out the next great tip! What happens when you want to select the values area when you have lots and lots of values?
4:36 – Step-by-step instructions for selecting the values area

How to format the values area so we can keep our Number Formatting

Video: 2 min 14 sec

[Image] Preserve Cell Formatting

Make sure you check the “Preserve cell formatting on update” option in PivotTable Options

  1. Select the whole Pivot Table values area first (not just one cell – for a good way to do this, see below “How to select the Values Area when you have lots and lots of values”)
  2. Bring up the Format Cells Dialog Box with Ctrl + 1
  3. Choose the desired Number Format (e.g. currency)
  4. You may need to resize the columns to fit cell contents with ALT + H + O + I
  5. And remember to check that you have your Pivot Table options set to Preserve Cell Formatting on Update (see video at 3 min 22 sec)

How to select the values area when you have lots and lots of values

Video: 4 min 38 sec

[Image] How to use the PivotTable Selection Tool

Use the Pivot Table Selection Tool to select (1) Entire Pivot Table, (2) Values Area

  1. Select the Pivot Table by clicking on it
  2. In the Excel Ribbon go to PivotTable Tools > Options > Actions > Select > Entire PivotTable
  3. Then click on Actions > Select > Values
  4. This will select the values area immediately

After selecting the values area you can proceed to apply the desired Custom Number format.


Download a copy of the Fruit Sales 2010 – 2012 spreadsheet

All the data in the video and example screenshots above are taken from this spreadsheet. You can download a copy by clicking here

[Image] Click to download Pivot Table file

Click the Fruits to download the sample Pivot Table file!

The data is completely fictional and you get a free digital helping of Summer Fruits (Image courtesy of somadjinn)

And make sure you enter our survey for a chance to win $50 of Amazon gift vouchers. Find out more on our Pivot Table survey page


Part 3 – How to Format the Pivot Table values area to a Custom Number Format (the VBA way)

Finally let’s have a look at how we can program Excel to instantly change the number format in our Pivot Table Value Area.

I’m going to cover this last because VBA and macros are a topic that some people find too difficult. If that’s you don’t worry you can skip this bit.

Don’t forget to check out the rest of my site – in particular you should take a look at our Excel video page and our Excel resource page

OK – let’s have a look at some VBA code.

Code Snippet 1: Setting default format for all the pivot table fields

Here’s some code I found on the Microsoft Developer Network (MSDN) Forum.

It sets all fields in the Pivot Table to have a default custom number format.

Sub Update_PT_Format_0()
    
    For Each pt In ActiveSheet.PivotTables
        For Each pField In pt.DataFields
            pField.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* -_)"
            Debug.Print pField
        Next pField
    Next pt

End Sub

But the problem is that when you add or remove a field from the values area the formatting is reset to the General format.

So you need to run the code every time you change fields, which sure is better than doing it manually. But can we do even better?

Code Snippet 2: Use the PivotSelect Method to set Number Format in Pivot Table

I played around with recording a macro to follow my steps in the video (see Part 2 above) and discovered the PivotSelect Method.

This functionality was added in Excel 2007 and allows you to select parts of the Pivot Table by name (e.g. xlBlanks, xlDataOnly, xlDataAndLabel).

You can look up the Microsoft documentation for more detail on using the method.

When you use the PivotSelect method in Excel 2010 in the way below, Excel remembers the format even when you change the field(s) in the values area.

Sub Update_PT_Format()
    
    For Each pt In ActiveSheet.PivotTables
        pt.PivotSelect "", xlDataOnly, True
        Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* -_)"
    Next pt

End Sub

If you are familiar with VBA I suggest you try out my code. It should work in both Excel 2007 and 2010. You may want to assign this macro to a keyboard shortcut (such as Ctrl + Shift + Q).


Subscribe to our email newsletter

If you like Excel tips & tricks why not subscribe to our free weekly newsletter, which you can do by filling out the form below the sharing buttons.

And remember to share this article with your friends and colleagues using the sharing buttons.

Cheers – Victor

Victor Chan
Hi I'm Victor. I started Launch Excel to help you use Excel and VBA faster. Thousands of people have learned new skills and increased their effectiveness at work with our help. You can do it too! Also I've personally used Excel lots since 2002. I use it mainly in accounting and finance but I also have a Masters degree in Engineering from Cambridge University. I hope you enjoy the site and tutorials!
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recommended Posts
Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.