Searching for error values

  • Feb. 02, 2014
Example files with this article:
  • Error values

    • Introduction

    Any Excel file - at least potentially - shows errors in worksheet formulas. Either due to inconsistencies or errors made by the author(s), either by bad input by the user, either by a flaw in the logic, and so on. Typically, cells show up as #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A. For this article, it is irrelevant whether the error is the result of a formula, or if the error value is a fixed constant. Also, the specific type of error is not of importance. The errors that Excel displays are localized to the language settings: a Spanish Excel installation will show other errors than the English counterpart. Therefore, pay attention when for instance searching for literal names.

    Now, where are those errors on the sheet… (if any) ? What follows below are some lesser-known tips and tricks / formula / macro solutions to help and guide you when looking for errors in cells. We are not discussing logical errors but rather formula errors.

    Formula solutions

    The following functions can be used to spot errors: ISERR, ISERROR, IFERROR, ERR.TYPE and TYPE. A formula-based solution will not always suffice. Hence, the need to go broader and looking for alternatives. Excel provides (at least) 4 built-in methods to spot error values. I will now discuss them from least useful to most useful.

    Excel Options

    It is possible to indicate errors in cells with certain Error checking rules: the above screenshot shows green triangles in cells B3 and D2. This functionality is not useless, in my opinion, but generally I tend to turn them off. The green triangles are the result of checking "Enable background checking" and "Cells containing formulas that result in an error" in the Excel Options:

    In the same dialog screen, you can see that green can sometimes be a different colour. It is your call utltimately.

    Autofilter

    An Autofilter can be used to filter on error values in a table. One of the drawbacks is that it needs to be done on a column-by-column basis. As Autofilters are well understood in general, I will not zoom in on this type of solution. Please read this article discussing an Advanced Filter trick, highly recommended!

    Special cells

    More interesting to me is the Special cells dialog screen where you can make Excel search for error values. While the previous solution with background error checking only allows for formulas resulting in errors, the current method can be applied in more situations. For example, out a large selection of cells, you want to highlight cells containing a formula that results in an error:

      Proceed as follows:
    1. Select all cells (entire columns and rows are fine too, that speeds up selecting cells)
    2. Press F5 or Ctrl-G
    3. Choose Special…
    4. Choose "Formulas"
    5. Check "Errors" and uncheck the other 3 possibilities
    6. Hit OK

    The result will be a selection of cells. If Excel cannot find such cells, you will be shown a descriptive message.

    Usually, I color the cells immediately so that I can through them one by one. Also, I define a named range for the returned cells so that I track the cells and navigate back and forth:

    Conditional formatting

    The best option (to me anyway) is Conditional formatting. Yes, the good old conditional formatting, though it has undergone substantial improvements in the latest versions of Excel.

      Proceed as follows (at least in Excel 2013):
    1. Select all cells (preferably, no entire columns and rows !)
    2. Go to Conditional formatting > Highlight Cell Rules > More Rules…
    3. Choose "Format only cells that contain"
    4. "Format only cells with" > "Errors"
    5. Pick a formatting of your liking
    6. Hit OK

            FOLLOWED BY:        

    For a finer level of information, you can use Conditional formatting with a formula like ISNA() and test for NA() type of errors only.

    VBA solutions

    This is a broad area, of course, since programming gives us a much broader toolbox to pick from. Our own phantasy and experience in coding can help us write custom solutions and routines. For example, loop over cells to find formulas that lead to #REF! error values: 

    Sub SearchREFerrors()
    

    '''''
' VBA-code to locate #REF! error values
' 01/02/14
'''''
    
    Dim rng As Range

    For Each rng In Cells(1).CurrentRegion.SpecialCells(-4123, 16)

        If rng.Value = CVErr(xlErrRef) Then rng.Value = "'" & Mid(rng.Formula, 2)

    Next


    End Sub

    You can see that I use the SpecialCells method (see above), searching for formulas (argument -4123) and error values (argument 16). CVErr(xlErrRef) is the VBA translation of a #REF! error. Rather than highlighting the cells in a certain way, the code above removes the formula from the cell. Please modify this procedure in any way to suit your needs. For instance, looking for other error types or making the cells with errors red for background. VBA-code gives a static result, unlike Conditional formatting, which is much more dynamic.

    That is it for today. As mentioned above this article shows a fenomenal Advanced Filter trick on how to filter out records with errors - rather than only highlighting them as discussed in the current article. Stay tuned for more Excel blog articles and suggestions.




    Homepage

    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links