Excel – How to keep Conditional Formatting formulas and ranges from automatically changing

conditional formattingmicrosoft excelmicrosoft-excel-2010

I've found that Conditional Formatting formulas and ranges will automatically adjust when you copy, delete, or move data around in a spreadsheet. While this is a nice idea, it tends to break things for me in some rather weird ways.

To avoid this, I tried writing rules that applied to the entire spreadsheet and keyed off of column headers to highlight the data I wanted to check.

Example: =AND(A$1="Check This Column For Blanks),ISBLANK(A1)) applied to =$1:$1048576

However, even with the rule explicitly applied to the entire sheet, it was still automatically adjusting (and breaking in weird ways by doing so) as I worked in the sheet.

How can I avoid this?

Best Answer

When I need a range that shouldn't change under any circumstances, including moving, inserting, and deleting cells, I used a named range and the INDIRECT function.

For example, if I want a range to always apply to cells A1:A50, I defined a named range through the Name Manager:

add named range

In the Name Manager, add a new range (click New), and in the Refers To: field, use the INDIRECT function to specify the range of cells you want, e.g. =INDIRECT("A1:A50") or =INDIRECT("Sheet!A1:A50"). Because the range is technically just a textual argument, no amount of rearranging cells will cause Excel to update it.

Also, this works in at least Excel 2010 and Excel 2013. Although my screenshot is from 2013, I have used this exact technique in 2010 in the past.

Caveats

  1. Keep in mind that this invariance can also trip you up. For example, if you change the sheet's name, the named range will break.

  2. I have noticed a minor performance hit when using this strategy on significant number of cells. A model I use at work uses this technique with named ranges that span several thousand disparate cell ranges, and Excel feels a tad sluggish when I update cells in those ranges. This may be my imagination, or it may be the fact that Excel is making additional function call(s) to INDIRECT.

Related Question