Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Maybe you just can't use the entire spreadsheet... Can you give a more concrete example of what are you doing? – Doktoro Reichard Sep 20 '13 at 18:49
Could you perhaps have a screenshot and indicate which areas get broken? – Jerry Sep 20 '13 at 18:55
@JohnBensin That sounds like it's exactly what I was looking for. I'll try to remember that one next time. – Iszi Sep 20 '13 at 19:33
@Iszi I added it as an answer. I'm sure you know how to add named ranges, but I include details in the answer for future visitors (and because when I first learned to use Excel, I could never remember). – John Bensin Sep 20 '13 at 19:59
An article about exactly what goes wrong: Excel 2010 conditional formatting nightmare. They sure made it a lot worse while trying to make it a bit better... – romkyns Jul 14 '14 at 13:29

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.


  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.

share|improve this answer
Though the named range provides a convenient short-hand method of referring to the target cells, couldn't you just use the INDIRECT function on its own? – Iszi Sep 22 '13 at 18:00
@Iszi Yes, I believe so. However, as you say, named ranges are convenient, and I often find myself referring to the same range of cells in multiple places throughout a workbook. I try to keep the ranges as small as possible, for performance reasons, and only change them when necessary, so using a named range means I only need to change it in one place. – John Bensin Sep 22 '13 at 20:14
@Iszi Did you end up using this method at all? If you run into any pitfalls or drawbacks that I didn't mention, let me know or edit them into the answer. I use this strategy in enough places that I don't want something to come back to haunt you or I down the road. – John Bensin Sep 27 '13 at 13:43
I haven't gotten around to implementing it yet, but will probably do so the next time I run across this problem. I'll let you know if there are any issues. – Iszi Sep 27 '13 at 17:28
I tried using the named range in the "Applies to" column of the Conditional Formatting Rules Manager, but the named range gets expanded when I hit "OK" or "Apply". Am I doing it wrong? – Mihai Alexandru Bîrsan Apr 29 '14 at 17:06

I'm not SO sure and I face the same problem frequently.

I'd say that the 'Apply to' field in the Conditional Formatting (CF) panel will ALWAYS work dynamically. So, it will ALWAYS convert any references to the format =$A$1:$A$50.

It's a pain.

share|improve this answer
It doesn't, even when I use either explicit or variable declarations ($ or no $). – Paul Jun 5 '15 at 16:19

I've found that rules are very easy to break, but here's something you can try that don't seem to break any rules.

You can change text inside cells. If you need to add a row, add your data at the end of your table and re-sort it. If you need to delete a row, only remove the text/numbers, then re-sort the table.

This works for me when I have conditional formatting that's applied to columns, and I usually set the formatting for the whole column, eg. $F:$F. It should still work if you're formatting for a partial range, just make sure that when you're done adding/removing and resorting that all the data you want formatted is still within your original range parameters.

It's a huge frustration for me as well.

I hope this helps.

share|improve this answer

I've experienced a very similar issue. I've made a few macros to add rows, and copy down formulas, and then adjust columns and row sizes to format how the sheet looks. I've found this issue occurs in one of two occasions.

1) When something from INSIDE the "applies to" is cut/pasted outside of this range.

2) When there are merged cells inside the "applies to", and any of the rows or columns get adjusted.

It appears during the merged cell issue, that excel has to unmerge everything, recalculate its conditional application, adjust all the cells (add or delete rows or what not) and then remerge them back. Its invisible to us but that seems to be how it is applied.

Thought that might help us reach the solution on this.


share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .