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

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.

Custom Number Formatting

This is an image showing the custom number format I used in the 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

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

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.

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.

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

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.

Cheers – Victor

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!

GET BETTER AT EXCEL

Recommended Posts
• Raseena

Improved PivotTables: PivotTables are now easier to use!

Key improvements include:
1. Performance Enhancements. In Excel 2010, multi-threading helps speed up data retrieval, sorting, and filtering in PivotTables.
2. PivotTable Labels. It is now possible to fill down labels in a PivotTable.
3. You can also repeat labels in PivotTables to display item captions of nested fields in all rows and columns.

• Victor

Hi Raseena,

Thanks for the comment about Excel 2010 improvements. It’s certainly true that there are a lot of improvements to PivotTables (just check out this Microsoft help page)

• Ann

One other number formatting question.

In my PivotTable, I am using both the Sum and Count functions in the value settings. Since the values are currency, Excel is formatting the count as a currency. Is there a way to tell Excel that count should always be a number and not currency without changing the formatting for the rest of the values?

• Victor

Hi Ann. Thanks for your comment and question. I don’t think what you are trying to do is simple (though I may be wrong).

You can try applying a custom number format, disabling the auto-format on refresh from pivot table options. Though this might not work if the pivot table expands with new values for count & sum.

Another way to get around this problem might be to build a reporting table formatted the way you want, and to use the GETPIVOTDATA function to extract data from the pivot table. This approach gives you more control over format of the reporting table, as it is separated from the format of the pivot table.

• Isla

Hi, Is there a way to keep cell colour after filtering. When I sort my pivot table from 20 rows to 5 rows the cells change to white. I’ve got the whole worksheet coloured light blue and would like the empty cells when filtering down to remain blue and not white. I’ve found a solution to alter the “normal” cell style and this works great BUT it changes the bg color of my slicer to black, and I’d like to keep that white if possible (it looks nicer)

cheers
Isla

• Victor

Hi Isla. Sounds like you have a nicely formatted worksheet. My advice is to look into using the GETPIVOTDATA function, because you can use this to read pivot data into a separate worksheet that retains its formatting. Here is a link to Microsoft’s help on that function:

http://office.microsoft.com/en-001/excel-help/getpivotdata-HP005209107.aspx

Pivot tables are great for quickly playing around with data, but because they usually lose formatting on refresh it’s a good idea to learn about GETPIVOTDATA and have a worksheet separated from the pivot table, that is used exclusively for presenting your data.

Cheers
Victor

• Vintila Gabriel

Hello,

I create a pivot table and i have to use two currencies. The problem seems to be that I am not allow to use two currencies in the same time.

Is there a way trough which the pivot table to display two currencies (EURO and \$)?

Thank you!

• David

Hi …. have struggled with this for a long time and cracked it today. Slightly different way of approaching:

1) Right click on the column of data you wish to format
2) Select “Value Field Settings”
3) Select “Number Format” in the dialogue box
4) Set the number format here

This will maintain the formatting for that data irrespective of how many columns you use to slice the data !

This site uses Akismet to reduce spam. Learn how your comment data is processed.