Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Maintaining Formatting when Refreshing PivotTables.

Maintaining Formatting when Refreshing PivotTables

by Allen Wyatt
(last updated September 28, 2013)

49

PivotTables provide a great way to analyze large amounts of data and pull out the summarizations that you need. Once you have the PivotTable displaying the values you need, you can then format the table to make the data presentable—for a while. You see, when you update the data on which the PivotTable is based, and then refresh the PivotTable, all your formatting work may go away.

The way around this is to follow these steps:

  1. Make sure that your PivotTable displays the values you want.
  2. Format the PivotTable in whatever way desired.
  3. Select a cell in the PivotTable.
  4. Display the Options tab of the ribbon.
  5. Click the Options tool in the PivotTable group. Excel displays the PivotTable Options dialog box.
  6. Make sure the Layout & Format tab is displayed. (See Figure 1.)
  7. Figure 1. The Layout & Format tab of the PivotTable Options dialog box.

  8. Make sure the Preserve Cell Formatting On Update check box is selected.
  9. Click OK.

Now, when you refresh the PivotTable, your previously applied formatting should remain on rows and columns previously in the PivotTable. If the refresh results in new rows being added to the PivotTable, then you will still need to format those, unless you are using an AutoFormat.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8731) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Maintaining Formatting when Refreshing PivotTables.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Returning the Minimum of Integers of a Range

If you have a range of numbers that contain both integers and decimal numbers, you may have a need to determine the minimum ...

Discover More

Indenting a Paragraph

Normally your text extends from the left margin all the way to the right. If you need to indent a paragraph of your text, ...

Discover More

Selecting Multiple Cells by Mistake

Click on a cell and you expect the single cell to be selected. If you instead get a group of cells, it can be frustrating to ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Refreshing PivotTable Data

If you modify the data on which a PivotTable is based, you'll need to refresh the table so it reflects the modified data. ...

Discover More

Setting Stable Column Widths in a PivotTable

When you update a PivotTable, Excel can take liberties with any formatting you previously applied to the PivotTable. Here's ...

Discover More

Bogging Down with Calculated Items

Create a complex PivotTable and you may find that your system slows to a crawl. The reason for this may be due to the way in ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine minus 5?

2017-05-23 05:05:24

Timothy Joy

That solution didn't work for me.

The tips that did work are:
1. take off any filters you have in the pivot table. (<<< THIS WAS KEY)
2. hover the point over the header until it forms a black down arrow to select the entire column (or over the row header left edge, to select a row)
3. tick "preserve formatting on update" in the p-t option (although this is seems to be not meaningful)

Tim

PS: Credit where credit is due, my solution comes from http://datapigtechnologies.com/blog/...omment-1492670


2017-05-04 13:56:28

Marc Hawk

ANSWER!!!!

When using a slicer, select all options. Format properly, then when you select a subset of the larger table using the slicer. It will be formatted properly. If you only format on the subsection of your pivot table. It will look odd when you choose another subset of the data.


preserve formatting should be checked.


2017-01-31 09:46:45

Alan Jones

Didn't work completely. Some remained...some didn't! On refresh, it seems that cells (randomly it seems) 'forgot' that they were centred in the cell...and borders (randomly it seems) disappeared.

Any ideas??


2017-01-24 09:29:21

Arnold

Try to click on each cell in a column randomly...you will come to a point where all cells will suddenly be highlighted...that will be the time you can right click and format cells...after you do this...try to refresh..you will see that all cell in that column has retained its formatted values...Enjoy Everyone...


2016-11-09 17:50:48

Lauren

Still lose formatting when pivot is refreshed.


2016-08-26 14:10:18

Chris M

I'm with Trisha.
Pivots simply cannot retain the formatting. Examples: Fonts and Alignments.


2016-08-18 19:17:42

JA

Hi. A few weeks ago, the company, upgraded Windows (not exactly sure how) and it affected my ability to retain pivot formatting in Excel 2013. I have one file that will retain the format when new data has been added to the range and two other files were I continually have to reformat (e.g. Add borders, shading, bold/underline, etc). I've read through the comments starting with this string and still don't seem to have a proper solution. I made sure the "Preserve cell formatting on update" has been checked. The file that retains was saved with an Excel Worksheet extension vs Excel 97-2003 Worksheet. Tried to change the extension and no luck. Last resort is to include IT, but this is a weird fluke after some kind of add-ins were done.


2016-08-13 07:05:09

jd murphy

Thanks Jim Swindle. My query isn't related to Pivot Tables so maybe I'm intruding into the wrong forum. Just in a general Excel sheet I find it a great inconvenience when I 'Replace All' I have to go back and re-italicize where necessary. There are 154,000 rows x 10 cells in the file.
I'll try to keep out of jail.


2016-08-12 07:00:12

Jim Swindle

jd murphy, if you're using Find and Replace on a PivotTable, there's something odd about your process. A PivotTable is linked to source data. If you change the result data manually, that probably means either that there is something wrong with the source data (which should be fixed) or that you're adjusting the results in ways that they should not be adjusted. Of course, maybe you're just adjusting the spelling of a label that some bureaucracy won't let you fix in the source, but again, you or your organization may have a bigger problem than Excel. I'm cautious, because years ago I innocently helped a likable guy modify data; it turned out that he was hiding risk. When it was discovered, he went to prison.


2016-08-12 05:56:53

jd murphy

Find and Replace loses all type formatting. Specifically italics disappear. Help!


2016-08-11 09:03:08

Trisha Santos

I have worked with Pivot Tables for years. I always check the two boxes needed to keep the formatting and the column widths. I also know to format the columns, you do it from the table itself with the little black arrow instead of the Excel column letters on top... it still doesnt work. I was told this is an Excel glitch that has happened for years. I thought that maybe every time they come up with a new version, they would fix these issues but I guess they didnt. UGH... How frustrating.


2016-07-06 05:44:37

Donat Protzen

Hello,

The layout of the columns is not yet fixed unless the box "Autofit column widths on update" is unchecked.

This helped me get the format to stick.


2016-05-16 12:47:59

Amy Laughlin

Hello,

I am drawing numbers from another excel sheet and they appear in the pivot but not in order. I have checked formatting for both and ensured they are the same but there are five numbers that will not sort in order?

Can you assist please?


2016-03-01 05:49:00

Ben

Thanks for this, Dan. However, this doesn't appear to work on Data Labels in a Pivot Chart.

I'm using Excel 2010 and to suppress zero values on the Pivot Chart data labels, I've formatted them to #,###;;;

When I check the preserve formatting box in the source pivot table and then refresh the table, the formatting disappears. Any ideas on how to prevent this? The only solution I've found involves a macro.

Ben.


2016-02-24 16:53:00

AC2

THANK YOU DAN! (Feb 28 commenter)


2016-01-26 13:57:51

Daniel

Well, that was easy! Thanks!


2016-01-25 16:49:08

Mike

Things were dire. We needed a hero. And so Anne appeared, dropped a knowledge bomb, and walked away from the explosion without even flinching. Her contribution has transcended space, time, newer additions of Excel, and the uselessness of this article. We salute you, Anne. We will go forth and spread tales of your bravery throughout the land.


2016-01-07 12:59:45

Michael Kelley

This did not work for conditional formatting for me. I wanted every other row to be shaded gray, and when I would click on the little + or – to expand or collapse the information the shading would go away. One thing I noticed is the shading will go away on the values part but not on the rows part. To get it to stay you must conditional format each part of the pivot table separately, one conditional rule for the rows section (the labels on the left) and one for the values section (on the right). When I did that the shading stayed.


2015-12-14 15:17:18

Patty

I am confused as I do not have an option tab in Excel 2013, so I can go no further than this step.
4.Display the Options tab of the ribbon.
My actual problem is, when I refresh the data, my "Row Labels" disappear.


2015-12-03 04:13:06

Abhijit Ghosh

For those who dont find that black pointer while moving over pivot. You can go to Analyze on the Pivot tables tool and under actions you will find the magic wand. There are options to select enable selection. Explore that more. you will find the solution as i did.


2015-11-12 08:53:01

Greg van Staden

I find none of these work effectively. What does work and thank you to my brother, change all the formats you want including column widths.
Highlight all the columns and Group together. (I normally leave first column visible)
Before refreshing the Pivot, collapse the columns, after pivot refresh expand thecolumns and you will find all column widths and formats have remained the same and the data has refreshed (UIDUIW)


2015-11-07 04:02:49

James Knight

Thanks for the tip
simple fix to an annoying problem.

All I need now is a fix to getting the data and filling the damned thing!!


2015-10-07 11:01:54

Richard

I'm sorry, but saying to individually select cells, or clicking on preserving cell formats, is redundant, and incredibly unhelpful since IT CLEARLY DOES NOT WORK


2015-08-27 11:18:30

AC

On Excel 2007 I have much better luck when I set the formatting before adding any pivot table filters. If you are still having issues after these tips try removing all filters - set the formatting and check the box to preserve - then add the filters back at the end.


2015-06-19 16:14:45

Jim Swindle

CORRECTION to what I'd just posted...
In summary, it looks like there are two things to do to get Excel to keep number formats in a PivotTable:

First, right-click in the PivotTable and in PivotTable Options select "Preserve cell formatting on update."

Second, click on the field name cell in the top row of the table (NOT on the Excel column header). Hover over the top edge of the cell until you can click the black arrow, then right-click and choose "Number format." Format as needed.


2015-06-19 16:08:29

Jim Swindle

Thanks! In summary, it looks like there are two things to do to get Excel to keep number formats in a PivotTable:

First, right-click in the PivotTable and in PivotTable Options select "Preserve cell formatting on update."

Second, hover over the top of the field name cell in the first row of the table (NOT on the Excel column header) until you can click the black arrow, then right-click and choose "Number format." Format as needed.


2015-06-04 09:48:09

Diane

Regarding Anne's solution: I do not get a tiny black arrow when I hover on the column title. I only get a down-pointing black arrow when I hover on the column header (the letter of the column). I would like one column in my pivot table to always be centered text. When I close and reopen, some of the rows are centered and some are not. Is there a way, once and for all, to to have formatting permanently maintained?


2015-05-28 02:50:24

maija

for some reason when i do this my pivot does exactly opposite, it loses my formating for a part of pivot table which is very strange, cannot understand why. at the same time another pivot table at the same sheet works fine


2015-04-29 04:55:21

tristan

Good Day,

Guys need your help regarding my pivot conditional formatting. For example I have a source data with list of accounts that have status for example of, penetrated, no potential, new account etc. How can i color code my pivot cell per account based on their account status on source data?


2015-04-23 11:41:01

Dale

Dan's comment on Feb 28 was very helpful for me. Each header cell needs to be separately formatted in order for it to stick when expanding or collapsing rows.


2015-03-31 15:36:25

Gary

Dan: Thanks for the post 28 Feb 2015, 14:56! I've been trying to figure this out for a long time.

Allen:
Thanks for your Excel and Word tips. Great resource!


2015-03-16 09:49:15

Dave

Good job Anne.... Exactly my problem!


2015-02-28 15:01:22

Dan

In addition to my previous post... I should clarify that you'll still need to have the "Preserve cell formatting on update" option turned on, I also have the "Autofit column widths on update" turned OFF. Also; using Office 2013.

Happy Excelling!


2015-02-28 14:56:04

Dan

Sorry if this is redundant (if I missed it in another comment).

After trying many of the suggested approaches I was still unable to keep the formatting in my Header Row (as using slicers/filtering. The fix that worked for me: Individually select each header in the pivot table, Right-Click "Format Cells...", then specify my format options. I tried it a couple ways (both worked): Simply select the cell, right-click... Or, wait for the black column select arrow to appear, then right-click cell (again both worked).

I posted this in case someone else was about to loose their mind (like I almost did - kidding of course).


2015-02-20 15:14:44

MGG

The tip with the black arrow does allow formating of the whole column or row, but what if I want o have the formating done only to the column headers ?


2015-01-29 23:33:37

Adrian Joseph

I would like to seek help on how to maintain value field settings in MS Excel 2010 when pivot table are being refreshed.

I have a pivot table where one column has a value field setting of (% Running Total). Every time I open the said saved excel sheet and refresh it, the value field setting on the said column is changing to "No Calculation."


2015-01-22 07:45:35

brian Murray

Anne 07 Oct 2013, 10:57 solution is the only one that worked for me.. thank you anne
where ever you may be :)

The correct procedure is to hover on the column title within the pivot table until the tiny black arrow appears, then click in the column. This will only allow the selection of data within the table itself, not above or below it.


2015-01-07 11:31:01

Martin

Thank you to Anne for solving a problem that has bugged me for ages.


2014-12-31 11:29:39

Oscar

SOLUTION - I would format Column F by selecting the column header, and change the number format to currency. It worked but only for an instance. It kept defaulting to General every time I would refresh. I read Anne's posting and it only made sense so I decide to try. I simply selected a cell within the pivot table of the column I wanted to format, changed it from general to currency, and it worked like a charm. Doesn't change when I refresh or when I open the file. Thank you, Anne. I think you solved the mystery for many of us.


2014-12-16 13:34:46

Hal

This does NOT reliably work.


2014-11-01 08:25:27

Narendra chouhan

a lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lot lots of thanks for this tips... thank you so much....


2014-10-15 16:39:35

Francis Moore

I followed Anne's suggestion and now have a happy pivot table which does not change when I refresh the data - thank you!


2014-10-06 15:11:47

Rich

I have the exact same problem as 'Laurel', a problem with the cells going black. I have seen that it is the fill color changes. Laurel's happens in 2013 version, mine happens in 2010 version. When I select a report filter, the result is presented in 'black fill', which I can change, but who wants to do that everytime? Plus I send this report out to several others, since it is rather comprehensive w/o using the filter, no one had advised me until recently of the problem, as no one had used a filter. I inherited this particular file, and had not previously 'tested it' myself, as no one had mentioned until now. I do not know how they survived for a year without anyone utilizing in that way. I see it as a blatant (blatant) Excel glitch, but appreciate anyone's advice.


2014-08-07 15:52:01

Laural

I'm using Excel 2013, and my pivot table options are defaulted to the setting above, however I still get formatting problems when I refresh - but only on some pivot tables. Any changes cause some of the cells in the pivot table to be blacked out completely (fill color black).

I have a lot of manual color assignments to different cells to help me tie out values to another report (if that makes a difference).


2013-10-07 10:57:32

Anne

I've had the same problem as Robert. After some tinkering, I've figured out the solution.

The problem occurs when you don't select the area or areas to format correctly. For instance, when I wanted to format a column of data in a pivot table, I selected the full column as I would normally do in a regular spreadsheet. That is, I clicked on the column heading, which would be the letter name on the spreadsheet grid, so that I am including data above and below the table.

The correct procedure is to hover on the column title within the pivot table until the tiny black arrow appears, then click in the column. This will only allow the selection of data within the table itself, not above or below it.

I think doing it the other way must indicate to excel that you are formatting the area surrounding the pivot table, not the table itself, and in actuality, you are only temporarily formatting the table.

The same goes for a row.


2013-09-30 09:17:20

KD

Jan, simply deselect the autofit column widths on update' box found directly above the 'preserve cell formatting' box.


2013-09-30 08:42:13

Jan

I use Excel 2007. While the table does retain colors and fonts, it will not keep wrapped headers or column widths. Extremely frustrating.


2013-09-30 05:27:47

Duncan

I have to say that losing formatting is one of the chief reasons I dislike PivotTables.


2013-09-28 07:06:24

Robert

Excellent Excel Tip.

The problem is that this approach does not always ensure that data remains formatted within a pivot table. Furthermore, data that changes or is modified at the source can create additional issues. I had to create a Macro to delete line items that are no longer present or have been modified in the source data. I would very much appreciate if Microsoft addressed these issues once and for all.

P.S. Currently using Excel 2010.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.