Excel – Hide “inconsistent formula” error in a selection in Excel

microsoft excelworksheet-function

This may seem easy to more advanced users, but I can't seem to find this out there.

I have a complex Excel worksheet. It has various formulas/calculations throughout the sheet.

Unfortunately, these cells trigger the Inconsistent Error Warning (green triangle), and over the few thousand cells, this makes for quite an ugly sheet.

I am aware you can disable this globally, and it is possible to individually disable the checking… but is it possible to hide a range? EG: I4:JQ151.
As you can imagine; individually hiding every cell is too much, and global disabling is a minefield for other parts of the sheet that DO need validation.

Cheers in advance, and sorry if this is a nooby question.

Best Answer

You can do this for a range by:

  1. selecting the range.
  2. Click the arrow next to the button that appears.
  3. Select ignore error

Opton 2 via vba: select your cells and run the following macro

Sub Example() 

    Dim rngCell As Range, bError As Byte 
    For Each rngCell In Selection.Cells 

        For bError = 1 To 7 Step 1 

            With rngCell 
                If .Errors(bError).Value Then 
                    .Errors(bError).Ignore = True
                End If 
            End With         
        Next bError     
    Next rngCell 

End Sub
Related Question