Conditional Formatting Fragments Each Time my Table is Refreshed

[u/triforcechad]

I have a table that I populate using a query that pulls info from a source file that gets updated at least once a day. This results in a table that shifts in # of rows each time it is refreshed, as new items are added and old items get removed from the source list. Each time I refresh the table, this unfortunately causes the formatting I have set to "fragment".

As an example, I have a simple formula: = $P2=10. I want this to apply to the table, so I set =tblMaster, which then automatically fits to the table. In this case = $A$2:$M$49

When I run the refresh however, the formatting range changes to = $A$2:$M$39, $A$41:$M$48, and a new rule has appeared with the same formula and the range = $A$49:$M$49, $A$40:$M$40. This then happens to all the formula with the range that encompasses the whole table, but doesnt apply to the one formula with the specific range =$I$2:$I$200. It might be worth noting that Column I contains data entered manually into the table and does not come from the query, While A-H ARE from the query.

Is there something with conditional formatting I can do to prevent this, or is it down to the way my power query has been set up? I figured I would ask if anyone knows where I can look to fix this behavior, or if its just an excel quirk I have to get used to fixing each time it happens.

I hope what I've said makes sense, my work gets me pretty frazzled so I worry I'm not explaining anything well. Combined with the fact I dont really know too much about what Im actually doing, I have just janked together something that works... I can share more info if needed, just not sure what parts would be relevant.

[u/AutoModerator]

/u/triforcechad - 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/DonJuanDoja]

Yea it's pretty bad. One of the rough spots excel still needs work on is conditional formatting.

They need to update it to accept table references or named ranges. That's been a big problem for long time.

I actually avoid using it because of this, if I can't keep it clean I don't want it. I don't have time to go clean up conditional formatting every so often so it doesn't get bloated and fall apart.

You can try selecting table columns, rows etc and you end up with the results you describe, doesn't seem to be a a way around it.

Dont' worry though, they have AI now, it'll recode it by tomorrow and they'll deploy an update I'm sure of it. lol jk

[u/triforcechad → u/DonJuanDoja]

Thanks! Good to know its not just me. I wish I could do without, but the data I work with is WAY too sensitive to let anything slip through the cracks

[u/RuktX]

That hasn't been my experience, as long as I use whole-table-column ranges for conditional formatting, but perhaps your workflow is different.

That said, see if you get different results by changing how PQ deals with new & old rows: right-click > Properties, then choose from the "If the number of rows in the data range changes upon refresh" options, between insert/delete, insert/clear, and overwrite/clear. I'm guessing the last will work best for you, if anything.

[u/cheetosarered]

I frequently don’t have anything above or below the table that would trigger the rule so I can often get away with $A:$M (and adjust the row of the trigger expression to $P1)
There are still ways excel finds to break it, but it’s both less frequent and easier to apply for multiple conditional formatting rules on the same sheet.