How do I stop Excel from resetting my custom number format when I update my Pivot Table?
Last updated on February 23, 2012 By Victor Chan
Excel 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
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:
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:
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)
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
Make sure you check the “Preserve cell formatting on
update” option in PivotTable Options
5. 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
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
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
Related Posts:
Author: Victor Chan
Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.
Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.