Maintaining Formatting when Refreshing PivotTables

Jill - Thanks for your thanks to Allen. I start every workday reading his tips. He's a rock star! I've taught Excel 1-3 at the community college and business/industry levels, and always recommended Allen's tips. I've told many of my participants, if you want a Master's Degree in Excel, here's one tool to get there. He deserves much thanks. Thanks, Allen!

I would also recommend 'unchecking' the 'autofit column widths on update' as that can cause layouts to change significantly too! Thank you for all your tips and tricks! Excel is such a powerhouse and you help us tap into it bit by bit!

I found a consistently robust solution: Apply conditional formatting based on the names of the Fields used in your PivotTable. More specifically, apply Conditional Formatting for all numeric Values (columns) used in the PivotTable. I'm using PowerPivot in what I describe below.

Step 1. Click on a column with a numeric field, and apply conditional formatting based on the field, as shown in the figure. Check "All cells showing ..." The ... should show the name of the numeric Value you want to display (second button).

Step 2. Select the Rule Type "Format only cells that contain". Edit the Ruel Description to Format only cells with cell values between X and Y. Choose values for X and Y that ensure your Value field is always in that range.

Step 3. Click Format... and set up the desired numeric format as usual.

Step 4. Apply Conditional Formatting for each Value to the entire PivotTable. That way, when Value columns are moved their formatting moves with them.

(see Figure 1 below)

One more thing. I typically define "Measures" in Power Pivot for all the quantities that might appear in the PivotTable. Yes, even the "Sum of", "Count of", etc simple types of measures that don't require a formula to define. I find that helps simplify managing the tables, and drives use of consistent reporting measures across multiple pivot tables sharing the same source data.

This doesn't work if it's a calculated field.

Nope doesn't work - every time I refresh the conditional formats still come off.

This was driving me absolutely insane! I have a sheet with multiple tabs, multiple pivot tables. They all kept the borders when data was refreshed or when a slicer was used. I added 2 new pivot tables, and the borders kept disappearing. I tried everything I read online about how to fix it but every time I would refresh or select from a slicer, the borders would disappear.

THE FIX FOR ME:

I saved the file, closed excel, and reopened it. When I did that, the borders stayed on the new pivot tables with no issue. Not sure why that happened but it caused me about a half hour of yelling at my screen before I tried it!

Problem seems to occur when using the Format Painter to copy formats from one pivot table to another. I had a worksheet in a file with several pivot tables, and after using the format painter to help format fonts and fills, I found the same problem when refreshing the file: formats would be lost, even though the preserve formatting box was checked in the Pivot Table Options.

I then deleted all the formatting from all the tables, and removed all filters from the tables, and started formatting each table individually.

When finished, the formatting stayed in place after a Refresh All. I then reapplied the filters I wanted, refreshed again, and the formatting still remained.

You will need to use set the number format in Value Field Settings first, then on "Refresh" the number format will always revert to the defined format

(see Figure 1 below)

And how do you troubleshoot when that box IS checked but the formatting is NOT being preserved!??

Does not work on Excel 2016 pivot table with conditional formatting.

The thing that worked for me was removing my label filter removing the (blank) label and instead apply a value filter to remove the row with a 0 value--which thankfully was my (blank) row. For whatever reason switching from one type of filter to the other worked for retaining font size. And the result is the same whether I filter for (blank) or 0. Thank God for a solution. This was painful.

Where it comes to column header format refreshing despite the option to no reformat being selected (!), I found that if you first apply a format pre-set within excel e.g. "Normal 3" and then reapply your formatting; it will successfully allow the formatting to stick.

This does not work for 2010

Allen, I have O365. Do you have or could you research any updates on this issue? I religiously do the tasks in your tip, but Excel still randomly and selectively wipes out my pivot table formating.

Does not work when using "Conditional Formatting".

Good job at creating a guide that doesn't actually apply to anything. These options are on by default and they don't change anything. Refreshing the table still removes all conditional formatting.

I have performed this task as described to retain the formatting on a pivot table but when i refresh some of the formatting reverts back to the original formatting. any ideas I am using Excel 2013.

Any help would be appreciated as I update the main table and have created a summary table via a Pivot table which I present to the client on a weekly bases

Maintaining Formatting when Refreshing PivotTables

by Allen Wyatt
(last updated August 25, 2017)

This does not work for Excel 2010. Wish I could find something that does!

Particularly cell border formatting but other formatting also.

When i did this, step by step, everything in my pivot table went o centered alignment.

Reviewed and tried all of these comments - didn't work for me.

My pivot table has a slicer and every time I selected a filter or refreshed the pivot table, all formatting was lost. I my case a specific column was formatted as number with no decimals, when refreshed certain cells changed to general format.

Then playing around I found if I selected the entire sheet, (triangle top left hand side between Col A and Row 1) and then formatted all data right using the alignment icon at the top of the page all worked ok for me.

Hope this helps

Right click on Pivot table->Pivot table Options ->Preserve cell formatting on update doesn't seem to effect much when used slicers.


Try this method:


First Select the values you want to format or click anything on the slicer->Now Go to Conditional Formatting->New Rule or Manage Rules->It opens up the window as

New/Edit Formatting Rule dialog window:

Apply rule to :

1)Selected cells

2)All cells showing “Field” (Use this)

3)All cells showing “Field values” with ……


Now select your condition

For ex: Format only cells that contain less than or equal to “2” Format to Yellow.

Format only cells that contain less than or equal to "3" Format to Orange.


The slicer format will still remain because you have selected Field instead of selected values.

*If you want to add more conditions simply follow the above and create new rule.

(see Figure 1 below)

(see Figure 2 below)

For conditional formatting issues this worked for me in Excel 2010: Select a cell in the column you want to show conditional formatting and select "New Rule" then you should get several buttons. Select the one that says "All Cells Showing Values". Unfortunately you have to do if for each column. When I refresh or use filters, my conditional formatting stays intact.

Hope this helps!

That check box doesn't work at all.

Anne's suggestion also worked for me. I was selecting multiple tabs, CTRL-A to select everything on those tabs, and then changing fonts. The proper checkboxes (preserve cell formatting) were checked, yet that formatting would get lost every time the data was updated. Clicking the black down arrow so that specifically the pivot table was selected and THEN following those steps caused the formatting to stick as intended. Not great to do it on lots of different tabs in the same spreadsheet, but at least next time I'll know to follow those steps before I start copying and editing full sheets.

I have a similar question to Danielle (8/24). I have pivot tables that when the data is refreshed loses the conditional formatting in the cells. I have 5 columns of data - Customer Name (L), YTD 2017 (M), YTD 2016 (N), Annual 2016 (O), Annual 2015 (P), % 17 v 16 (Q). Columns L thru P are updating each month, Column Q is a calculated field of Col M/Col N. The conditional formatting is set to change the entire line a certain color based on the % in Col Q. The conditional formatting works until the data is updated for new month. When I look at the info in the conditional formatting rule for that table (or any of them that I have) the original data range of $L$6:$Q$97 then changes to $L$6:$L$97;$Q$6:$Q$97. I have "Preserve cell formatting on update" checked in PivotTable Options. I cannot figure out how to keep the original conditional formatting rule from changing upon refresh. Any solutions?? Again - this is conditional formatting, not font, alignment, number, column width etc.

I have a conditional format in my pivot table for columns A through C. Columns A and B have data and Column C is the total column for the data. Although I have the conditional formula created for the range of =$A$46:$C$62, when I refresh it it changes to =$A$46:$B$62 and the total column is not formatted per the conditional formula. I have used the Options for maintain the formatting in the pivot table but nothing has worked. If anyone can provide guidance I would appreciate it. (see Figure 1 below)

This advice doesn't disclose that only SOME formatting is preserved. For instance, if you change the borders, then refresh, MS Excel still formats borders and even worse, formats them in an inconsistent manner...

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

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.

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??

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

Still lose formatting when pivot is refreshed.

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

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.

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.

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.

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

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.

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.

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?

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.

THANK YOU DAN! (Feb 28 commenter)

Well, that was easy! Thanks!

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.

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.

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.

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.

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)

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!!

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

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.

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.

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.

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?

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

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?

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.

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!

Good job Anne.... Exactly my problem!

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!

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

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 ?

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

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.

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

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.

This does NOT reliably work.

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

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

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.

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

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.

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

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

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

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.