786 votes213 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
Thanks to Graham for starting this conversation. If you would also like Excel to maintain named range references and structured table references in the “applies to” field for Conditional Formatting rules, please add your comments and vote this one up. We will prioritize accordingly.
Steve (MS Excel)Christopher Tindall commented
The only approach I've found to control Conditional Formatting is to prevent user ability to copy and paste rows or cells. This requires VBA buttons to add rows or columns (by inserting a row, copying the info from the row below, then clearing all of the cells for that row). I've used this approach on a 1000+ row spreadsheet with 12+ people simultaneously inserting and deleting rows... not one change to the 10+ conditional formatting formulas.
MS understands this issue is important to the users. This suggestion is ranked #11 in number of votes (an a few higher on the list appear to be solved by the latest update). This board is too valuable for direct user engagement for MS to ignore.
That said, Conditional Formatting is a volatile function without a decent workaround, which makes this difficult to correct without a big update to existing Excel coding. These kind of updates bring risk. Since this is essentially a visual issue, it has less priority than improvements which will increase user efficiency. This is evident in the latest updates which bring very powerful tools (AI, Ideas, New Data Types, Data from Pictures, and Dynamic Arrays). These are added features, not corrections to existing features, so there is less risk.Christopher Tindall commented
Futski... I fix proposed by Graham in the initial solution is clear. We need to be able to link conditional formatting ranges to previously defined 'named ranges'. These conditional formatting ranges would then be unaffected by modifications to cells, rows, and columns.
Implementing this functionality is not very difficult from a programming standpoint. This is why we are all stupefied that this has not yet been implemented.Christopher Tindall supported this idea ·