28

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?

5
  • Maybe you just can't use the entire spreadsheet... Can you give a more concrete example of what are you doing? 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). 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...
    – RomanSt
    Jul 14 '14 at 13:29

10 Answers 10

9

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.

7
  • 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. 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. 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
  • 10
    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? Apr 29 '14 at 17:06
2

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.

1

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.

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

I found that using the INDIRECT function and the ROW function in the Conditional Formatting rule eliminates the problem of Excel creating new rules and changing the range.

For example, I wanted to add a line between rows in my checkbook register spreadsheet when the month changed from one row to the next. So, my formula in the CF rule is:

=MONTH(INDIRECT("C"&ROW()))<>MONTH(INDIRECT("C"&ROW()-1))

where Column C in my spreadsheet contains the date. I didn't have to do anything special to the range (didn't have to define a Range Name, etc.).

So in the original poster's example, instead of "A1" or "A$1" in the CF rule, use:

INDIRECT("A"&ROW())
0

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.

-T

0

I have a simple solution for this.

Just Move to a cell which is in range (the one not broken of the Range), click on Format Painter and then paste on whole Column. Again it will show Where it has broken, you just need to do a Format Painter to the Cell which has broken the range. Now, this too may seem a bit lengthy, you can just build a simple macro for this.

0

In Conditional Formatting formula, use R1C1 notation with INDIRECT function:
Example 1:

If same row column A = 1 ...

=IF($A1=1,1,0) becomes 
=IF(INDIRECT("RC1",FALSE)=1,1,0)

Example 2:
If same row column A = 2 AND next row column A = 3 ...

=IF(AND(($A1=2),($A2=3)),1,0) becomes
=IF(AND((INDIRECT("RC1",FALSE)=2),(INDIRECT("R[1]C1",FALSE)=3)),1,0)
0
0

If the use case is to generate a report spreadsheet, then this should work well:


You can bypass the excessive helpfulness of Excel with a couple of one-time setup steps, if you are also willing to manually perform a simple refresh of the final data after doing all the data entry.

Assuming your data are arranged normally (in rows):

  1. Input all your data into one sheet. For this tutorial I'll name the sheet Input. Don't apply any conditional formatting to it. Feel free to shift values around to your heart's content (e.g. delete/insert/copy/paste)
  2. Create another empty sheet and name it as you like (e.g. Output). Manually copy over your global formatting such as column widths and header formatting (not the contents of the headers, just the formatting).
  3. Replace $A$1 in the following formula with the location of the start of your header row, then copy across all columns and rows you want to output: =IF(LEN(OFFSET(Input!$A$1,ROW()-1,COLUMN()-1))>0,OFFSET(Input!$A$1,ROW()-1,COLUMN()-1),"") (the IF(LEN(...)>0,...,"") statement is necessary because Excel also helpfully determines data types for you and uses 0 for empty cells otherwise, and the -1 terms come from offset versus ordinal semantics)
  4. Apply conditional formatting to the Output sheet.

This copies your Input data sheet to Output sheet without any cell references that Excel would auto-butcher so you can confidently define your conditional formatting on the Output sheet.

You just have to manually extend the range the formula is applied to in the Output sheet as the size of your Input data changes.

I suggest you avoid merging any cells.


P.S. This question has been asked numerous times (it also applies in Google Sheets, which is a clone of Excel) so you might find a better solution for your use case looking through them:

  1. Excel conditional formatting fragmentation
  2. (this one) How do I keep Conditional Formatting formulas and ranges from automatically changing?
  3. Preserve conditional formatting on cut-paste
  4. Excel: how to permanently apply conditional formatting to the whole spreadsheet?
  5. Why is Excel butchering my Conditional Formatting?
0

If you paste without format (Paste > Paste Special > Unicode Text or similar) the special formatting will not be copied, and as such the "Applies to"-range should not change.

You can delete rows/columns, but if you insert them the "Applies To"-range will be split up.

You extend the area by selecting the last row/column and "copy-drag" on the small square at the bottom left of the "cursor". This way the "Apply to"-range should remain intact. (Note that this will not extend any formulas on rows or columns like insert does.)

It is, however, a total pain to remember to paste without format, etc. I usually don't.

I sometimes also define a name for the area I want to format. It won't be used in the rule, but you can clean the rule up by removing all rules but one and use the area name in the "Applies to" section.

I've tested on a Mac using Excel 16.3 (I think Office 2016... hmm).

0

If all rules of a worksheet have a similar Applies To range, you can use below script in current sheet's code. It locks conditional formattings' ranges and also prevents creating similar rules during pasting.

You can change applyToRange if you want.

(Complete example with descriptions and saving lock state can be found here: https://github.com/reza55n/ExcelLockConditionalRange)

Const applyToRange = "$1:$1048576"
  'Must be entered absolute, similar to the formula entered in Rules Manager window.
  'Examples:
  '  $D:$D (Single column)
  '  $B:$E (Multiple columns)
  '  $C$2:$E$12 (Range)
  '  $3:$6 (Multiple rows)
  '  $B$9 (Single cell)

Dim prevConditionalsCount As Integer


Private Sub Worksheet_Change(ByVal Target As Range)
    With Me.Cells
        If .FormatConditions.Count > 0 Then
            Dim i As Integer, j As Integer
            
            For i = 1 To .FormatConditions.Count
                If .FormatConditions(i).AppliesTo.Address <> applyToRange Then
                    .FormatConditions(i).ModifyAppliesToRange (ActiveSheet.Range(applyToRange))
                End If
            Next

            If prevConditionalsCount = 0 Or .FormatConditions.Count <> prevConditionalsCount Then
                ' "For" loop doesn't refresh its condition.
                i = 1
                Do While i < .FormatConditions.Count
                    j = i + 1
                    Do While j <= .FormatConditions.Count
                        If cmpConditions(.FormatConditions(i), .FormatConditions(j)) Then
                            .FormatConditions(j).Delete
                            'Now j will target to new item and shouldn't be increased
                        Else
                            j = j + 1
                        End If
                    Loop
                    
                    i = i + 1
                Loop
                prevConditionalsCount = .FormatConditions.Count
            End If
        End If
    End With
End Sub


Private Function cmpConditions(ByRef c1 As Object, ByRef c2 As Object)
    Dim areSame As Boolean
    areSame = False
    
    'It's enough to check formulas here:
    If _
           (c1.Type = xlTextString Or c1.Type = xlExpression Or c1.Type = xlTimePeriod Or _
            c1.Type = xlErrorsCondition Or c1.Type = xlNoErrorsCondition Or _
            c1.Type = xlBlanksCondition Or c1.Type = xlNoBlanksCondition) _
            And _
           (c2.Type = xlTextString Or c2.Type = xlExpression Or c2.Type = xlTimePeriod Or _
            c2.Type = xlErrorsCondition Or c2.Type = xlNoErrorsCondition Or _
            c2.Type = xlBlanksCondition Or c2.Type = xlNoBlanksCondition) Then
        If c1.Formula1 = c2.Formula1 And c1.StopIfTrue = c2.StopIfTrue And c1.PTCondition = c2.PTCondition Then
            areSame = True
        End If


    'Otherwise, types must be identical:
    ElseIf c1.Type = c2.Type Then
        If c1.StopIfTrue = c2.StopIfTrue Then
            If c1.PTCondition = c2.PTCondition Then 'On PivotTable
                Select Case c2.Type
                
                Case xlCellValue
                    If c1.Operator = c2.Operator And c1.Formula1 = c2.Formula1 Then
                        If c1.Operator = xlBetween Or c1.Operator = xlNotBetween Then
                            If c1.Formula2 = c2.Formula2 Then
                                areSame = True
                            End If
                        Else
                            areSame = True
                        End If
                    End If
                
                Case xlUniqueValues
                    If c1.DupeUnique = c2.DupeUnique Then
                        areSame = True
                    End If
                    
                Case xlAboveAverageCondition
                    If c1.AboveBelow = c2.AboveBelow And c1.CalcFor = c2.CalcFor And _
                            c1.NumStdDev = c2.NumStdDev Then
                        areSame = True
                    End If
                
                Case xlTop10
                    If c1.TopBottom = c2.TopBottom And c1.CalcFor = c2.CalcFor And _
                            c1.Rank = c2.Rank And c1.Percent = c2.Percent Then
                        areSame = True
                    End If
                
                Case xlColorScale
                    If c1.ColorScaleCriteria.Count = c2.ColorScaleCriteria.Count Then
                        Dim hasInequality As Boolean, i As Integer
                        hasInequality = False
                        For i = 1 To c1.ColorScaleCriteria.Count
                            If c1.ColorScaleCriteria(i).Type <> c2.ColorScaleCriteria(i).Type Or _
                                    c1.ColorScaleCriteria(i).Value <> c2.ColorScaleCriteria(i).Value Then
                                hasInequality = True
                                Exit For
                            End If
                        Next
                        
                        If Not hasInequality Then
                            areSame = True
                        End If
                    End If
                    
                Case xlIconSets
                    If c1.ReverseOrder = c2.ReverseOrder Then
                        'Item 1 is same all the time, except its icon
                        If c1.IconCriteria(2).Operator & c1.IconCriteria(2).Type & c1.IconCriteria(2).Value = _
                                c2.IconCriteria(2).Operator & c2.IconCriteria(2).Type & c2.IconCriteria(2).Value And _
                                c1.IconCriteria(3).Operator & c1.IconCriteria(3).Type & c1.IconCriteria(3).Value = _
                                c2.IconCriteria(3).Operator & c2.IconCriteria(3).Type & c2.IconCriteria(3).Value Then
                            areSame = True
                        End If
                    End If
                    
                Case xlDatabar
                    If c1.MaxPoint.Type = c2.MaxPoint.Type And c1.MinPoint.Type = c2.MinPoint.Type Then
                        If c1.MaxPoint.Value = c2.MaxPoint.Value And c1.MinPoint.Value = c2.MinPoint.Value Then
                            areSame = True
                        End If
                    End If
                
                Case Else
                    'Error unknown type!
        
                End Select
                
            End If
        End If
    End If

    cmpConditions = areSame
End Function

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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