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.
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:
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:
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)
Here are the steps that most people use when they want to change the number format for the Pivot Table values area.
- Right click on a number in the values area
- Select Value Field Settings from the pop-up menu
- Click on the Number Format button
- 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:
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
- 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”)
- Bring up the Format Cells Dialog Box with Ctrl + 1
- Choose the desired Number Format (e.g. currency)
- You may need to resize the columns to fit cell contents with ALT + H + O + I
- 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
- Select the Pivot Table by clicking on it
- In the Excel Ribbon go to PivotTable Tools > Options > Actions > Select > Entire PivotTable
- Then click on Actions > Select > Values
- 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
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.
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