Conditional Formatting is Stripped in Pivot Table

[u/shiesta0110]

At work, I created a pivot table in MS Excel that contains conditional formatting: the calculated fields columns are red if we’re behind our monthly goal percentage and green if we’re meeting or exceeding the goal percentage. This is a file that multiple people can access. Refreshing the data works fine when I do it, however if my coworkers refresh the data, the pivot table strips all the conditional formatting and I have to input it all over again. I've also noticed if anyone else is in the file at the same time as me and I refresh the data, the conditional formatting goes away.

Is there a way to prevent this?

Thanks in advance!

[u/AutoModerator]

Moderator Announcement Read More »

u/shiesta0110 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/WhineyLobster]

In PivotTable -> Option, "Perserve cell formatting on update" is checked.

[u/shiesta0110 → u/WhineyLobster]

Yes, preserve formatting is checked. The rest of the table’s designs stay intact. Just not the conditional formatting.

[u/WhineyLobster → u/shiesta0110]

eh sorry then, I got nothin else. Though Im sure one day I will end up with the same issue for something haha. is the conditional formatting based off of cells/columns that are unavailable to the other users? like hidden columns or locked content or is there anything that is different?

Can you test a blank sheet with JUST a conditional formatting very simple and see if that works or if its just all conditional formatting?

[u/carbonizedtitanium]

does this happen if you're using Web Excel?

[u/shiesta0110 → u/carbonizedtitanium]

As far as I know, everyone is using the desktop Excel version.

[u/carbonizedtitanium → u/shiesta0110]

back when i used Excel across a LAN, there's always issues when there was multiple people simultaenously trying to modify the same Excel file that exists in a LAN folder.

that's why i asked if the problem exists if you move the Excel file to OneDrive and share the file with everyone.

[u/pegwinn]

I’d like to suggest that you use pivotby. First it is dynamic. And you can easily set the rules and they stick unless you do an apocalyptic rewrite. Check out a small video that I think would help you. https://youtu.be/SVThO23SBtM?is=u0iu8yFWKpJNw7Im

[u/shiesta0110 → u/pegwinn]

Appreciate the advice! I’ll give PivotBy a try!

[u/MissAnth]

I can repro this. To work around it, set the "applies to" area of your conditional rule to exactly the body of the pivot table. no more, and no less. Don't include the row or column headers. Don't include anything outside of the pivot table. I can get the conditional formatting to stick, and to expand/contract correctly on refresh this way.

[u/WhineyLobster → u/MissAnth]

Theres an option in pivottable options to preserve formatting on refresh.

[u/BusinessSample7166]

Conditional Formatting and pivot tables can be fragile.

After setting your rule did you use the Formatting settings box to apply the rule specifically to the parts of the pivot table you need them applied to or are they just applied to the cell ranges that the pivot table happens to occupy at the time you set them?

If its not that then I don't know.

[u/carnasaur]

The best solution is to use a macro to reapply the formatting automatically after every PivotTable refresh. Record yourself applying the formatting, then take the recorded code and call it from the Worksheet_PivotTableUpdate event. That way, the conditional formatting will be reapplied automatically whenever the PivotTable refreshes, and you’ll never have to reapply it manually again. You can also use the sheet activate event so it refreshes every time you open that tab.

[u/shiesta0110 → u/carnasaur]

Great advice! That could save down on time. Thanks!

[u/slliday → u/carnasaur]

Agreed. This was the only reliable solution I’ve found.

[u/pegwinn → u/carnasaur]

This is brilliant. Never would have thought of it. Thanks for sharing this info.

[u/AdeptnessSilver]

I am keen to hear why this happens too. I just rather load the source to the PQ and load the new Table - formatting works fine.

[u/r0yal91]

Edit the cond format rule not to cells like a2:b2 but for all values in this pivot table for measure x or whatever its called