How do I keep Conditional Formatting formulas and ranges from automatically changing?

Question

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?

Answer 1

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:

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.

Answer 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.

Answer 3

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())

Answer 4

The problem usually happens when copy/pasting, if you set this macro:

Sub SetPasteDefaultasValues()
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

as the default Ctrl+V shortcut, the fields shouldn't change when editing a table. Code from https://www.youtube.com/watch?v=TLY19zTIxPc&t=9s

Answer 5

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.

Answer 6

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

Answer 7

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.

Answer 8

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)

Answer 9

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?

Answer 10

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).

Answer 11

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

Answer 12

copy the data using Paste Special, and covert the data to 'values'.
And for the conditionally formatted template/ file (whatever) save it as an xltx file - excel template. This was save you a mountain of headaches