Conditional Formatting not holding in pivot table querying Access


Chuck W

I have created a pivot table in Excel 2007 based on an Access query. I then
created some conditional formatting. My values in the pivot table are
percentages. I have red, green and yellow based on what the percentage is.
Everything is fine until I refresh the pivot table. The pivot table size
does not change (No new rows added). It wipes out all of the color coding
though. When I select a cell in my pivot table and select Conditional
Formatting, the rules are still there but there is not color. I originally
did this file in Excel 2003 and did not have this problem. Can someone help?

Roger Govier

Hi Chuck

Right click on any cell within your PT>Table Options>Layout tab>ensure that
Preserve cell formatting on update is checked

Chuck W

The preserve cell formatting is already checked. I googled the issue and
several others have the same problem. Basically, they had to write a bunch
of VBA code to preserve the formatting. Apparently, when the pivot table is
created in a previous version and then upgraded to 2007, the 2007 does not
hold the formatting.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question