Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 at 13:29

2 Answers 2

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.

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
1  
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 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.