Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Automate Finding Cell Errors with the 'Find Cell Errors' Utility

Jerry Paladino
By Jerry Paladino


In Excel there is no built-in method to generate a report of all error codes in a worksheet let alone an entire workbook. It is also difficult to check all formulas, especially when rows, columns and worksheets can be hidden. The 'Find Cell Errors' routine presented in this article creates an index of all errors found in a workbook and presents them on a new sheet similar to this Error Report:
Cell Error Report

The Find Cell Errors Report

The Summary section of the report includes:

The number of worksheets scanned
The number of cells scanned
The number of errors found
The elapsed time the scan took
An 'Error Index' with a brief description of the seven cell based error condition and their likely causes
The Error section of the report indicates:

The worksheet where the error occurs
The cell reference to the worksheet error with a hyperlink to the error cell
A 'Hidden' indicator is provided if the cell is located in a hidden row (R), hidden column (C), hidden row/column (R/C), or if the cell is located on a hidden worksheet. Since a hyperlink will not transfer to a hidden worksheet, a comment is embedded in the hyperlink cell indicating the worksheet must be unhidden for the link to function properly.
The specific error found
The formula causing the error.
The error report will be located on a new worksheet named ' Error Cells+' which is added as the last sheet in the workbook being checked. In an Excel 2007 system the error section of the report will be formatted as an Excel Table with filters enabled to isolate specific sheets or errors if the numbers of line items is excessive. In an Excel 2003 or earlier system the report will be a static list of rows with AutoFilter enabled.

When all errors in a workbook have been resolved, a final run of FindCellErrors will eliminate the 'Error Cells+' worksheet and display a message indicating no errors were found in the workbook.

No Errors Found


Using The FindCellErrors Code

A sample file is attached with examples of all seven cell error conditions including errors located in hidden rows, hidden columns and on hidden worksheets. Please download the Sample File and give it a try.

The attached sample file has a copy of the FindCellErrors module and a friendly button on the 'Start Here' worksheet to run it. If you think the Find Cell Errors Utility could be useful please read on, there are instructions in the article to make it available for all your workbooks.

1)   Download the example file if you simply want to test the FindCellErrors utility. When you open the file you will need to select the enable macros button or place the file in a Trusted Location if you are using xl2007/2010. The file will open to the 'Start Here' sheet. Press the large blue button on the screen and the routine will check all the worksheets in the workbook for cell based errors. Once it has completed its scan of the workbook it will create the 'Cell Errors+' worksheet with the Error report. The hyperlinks will take you to each error in the workbook even if they are located in hidden row or columns. The worksheet 'Sheet 3' is a hidden worksheet and the cell comment will ask you to unhide the sheet so the hyperlink will function properly.

2)   If it looks interesting, let's try it on one of your workbooks. You can run it against another workbook by leaving the Sample file open and also opening your workbook. From your open workbook press Alt-F8 to display the Macro dialog. Select the FindCellError macro and press the Run button. It will scan all worksheets in your workbook and if it finds errors it will create the Error Report. If not, a message will be displayed indicating no errors were found and the number of worksheets and cells that were scanned.

3)   If you think you might use FindCellErrors on a regular basis you can add it to your Personal.xls file and it will be available every time you open Excel.

From any workbook, press Alt + F11 to open the Visual Basic Editor (VBE)
Find and select the VBA Project - PERSONAL.XLS(B) in the left pane (Project Explorer). If you do not see the Project Explorer press Ctrl-R to display it. If you do not have a Personal.xls listed please follow the link below on "How do I create a Personal.xls(b) or Add-in"
From the menus, choose Insert-Module
Copy the code from the code box below and paste it into the right hand pane of the VBE
Save the Personal.xls file from the menu: File-Save or press the Save icon on the toolbar
Close the Visual Basic Editor and return to Excel
For more information on creating a Personal Macro Workbook, please see: How do I create a Personal.XLS(B) or Add-in

Now that the FindCellErrors utility is part of your Personal.xls file you can run it on any open workbook by pressing Alt-F8 and selecting the FindCellErrors macro in the Macro Dialog box. You can even set up a keyboard shortcut from the Macro Dialog. Just press the Options… button and enter a letter in the box. The macro will run when you press Ctrl+the letter you selected. Be careful not to select a letter for a keyboard shortcut that you use regularly such as Ctrl-C or Ctrl-V.

4)   An alternative to the keyboard shortcut is to assign the macro to a toolbar button in xl2003 or to an icon on the QuickAccess Toolbar in xl2007/2010.

In xl2007, right click on the QuickAccess Toolbar and choose 'Customize Quick Access Toolbar'. In the 'Choose commands from' dropdown choose 'Macros' and from the list select 'Personal.XLS!FindCellErro rs'. Press the ADD>> button in the middle of the two boxes. Press the 'Modify' button at the bottom of the right pane if you want to change the icon and the text displayed. Then press OK to accept the change and return to Excel.

In xl2003, to add a button to a toolbar and assign it to the FindCellErrors macro see: Add a button, menu, or command

You can now check any active workbook by pressing the button on the toolbar that is assigned to FindCellErrors. The source code is listed below.

Option Explicit
                      Sub FindCellErrors()
                      ' FindCellErrors
                      ' Written by Jerry Paladino (ProdOps)
                      ' 26-July-2010
                      ' Searches the used range of all worksheets in the active workbook
                      ' and creates a report worksheet named "Error Cells+" if it finds
                      ' any cells with an Excel error condition.  If no errors are found
                      ' a dialog box is displayed indicating no error conditions found.
                          Dim wb As Workbook
                          Dim ws As Worksheet
                          Dim rngTest As Range, rngErrors As Range, rngCell As Range
                          Dim Start As Long, Finish As Long, errCnt As Long, cntr As Long
                          Dim shtcells As Long, ArryUBound As Long, wsCnt As Long
                          Dim wbCellsTot As Long, HyperCounter As Long
                          Dim NumArry As Variant
                          Dim StrArry() As String
                          Dim PauseTime, PauseTimeStart, PauseTimeStop As String, hddn As String
                          Dim myMsg As String, strComment As String, ErrRptMsg As String
                          Dim Response As String, Title As String, myErrMsg As String
                          On Error GoTo Problem
                          Start = Timer
                          With Application
                              .DisplayAlerts = False
                              .ScreenUpdating = False
                              .EnableEvents = False
                              .StatusBar = False
                          End With
                          Set wb = ActiveWorkbook
                          Set rngTest = Nothing
                          Set rngErrors = Nothing
                          ' Delete the Error Report sheet if it exists in the workbook
                          If IsError(Evaluate("'Error Cells+'!A1")) = False Then
                              PauseTimeStart = Timer
                              ErrRptMsg = vbCrLf & "The Existing 'Error Cells+' Worksheet Will be Deleted" & vbCrLf & vbCrLf
                              ErrRptMsg = ErrRptMsg & "Press OK to Continue" & vbCrLf & vbCrLf
                              ErrRptMsg = ErrRptMsg & "To Save it, Press CANCEL and Rename the Worksheet   " & vbCrLf
                              ErrRptMsg = ErrRptMsg & "Before Running the 'Find Cell Errors' Utility Again" & vbCrLf & vbCrLf
                              Title = "Deleting Existing Error Report"
                              Response = MsgBox(ErrRptMsg, 49, Title)
                              If Response = 2 Then   ' User pressed CANCEL
                                  GoTo Normal_Exit
                                  PauseTimeStop = Timer
                                  PauseTime = PauseTimeStop - PauseTimeStart
                                  Sheets("Error Cells+").Delete
                              End If
                          End If
                          ' Determine the number of worksheets and cells in the workbook
                          Application.StatusBar = "Determining the number of cells used in this workbook..."
                          For Each ws In ActiveWorkbook.Worksheets
                              shtcells = 0
                              shtcells = ws.UsedRange.Cells.Count
                              wbCellsTot = wbCellsTot + shtcells
                              wsCnt = wsCnt + 1
                          ReDim StrArry(1 To wbCellsTot, 1 To 1)
                          ReDim NumArry(1 To wbCellsTot, 1 To 4)
                          ' Loop through the used range of all worksheets in the workbook and if a
                          ' cell error is found then load the Worksheet Name, Cell Address,
                          ' Cell Value and Cell Formula into the error array.
                          errCnt = 0
                          For Each ws In ActiveWorkbook.Worksheets
                              Application.StatusBar = "Checking Cells in Worksheet " & ws.Name & " for Cell Errors"
                              If ws.Visible <> -1 Then hddn = "Worksheet"
                              Set rngErrors = Nothing
                              Set rngTest = Nothing
                              Set rngErrors = ws.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
                              Set rngTest = ws.UsedRange.SpecialCells(xlCellTypeConstants, xlErrors)
                              If rngTest Is Nothing And rngErrors Is Nothing Then GoTo Next_WS
                              If Not rngTest Is Nothing Then
                                  If rngErrors Is Nothing Then
                                      Set rngErrors = rngTest
                                      Set rngErrors = Union(rngErrors, rngTest)
                                  End If
                              End If
                              For Each rngCell In rngErrors
                                  If IsError(rngCell.Value) Then  'Redundant but needed if SpecialCells is graeter than 8,000
                                      If hddn <> "Worksheet" Then
                                          If rngCell.EntireColumn.Hidden = True And rngCell.EntireRow.Hidden = True Then
                                              hddn = "R/C"
                                          ElseIf rngCell.EntireRow.Hidden = True Then
                                              hddn = "R"
                                          ElseIf rngCell.EntireColumn.Hidden = True Then
                                              hddn = "C"
                                          End If
                                      End If
                                      errCnt = errCnt + 1
                                      StrArry(errCnt, 1) = ws.Name
                                      NumArry(errCnt, 1) = rngCell.Address
                                      NumArry(errCnt, 2) = hddn
                                      NumArry(errCnt, 3) = rngCell.Value
                                      NumArry(errCnt, 4) = "'" & rngCell.Formula
                                      If hddn <> "Worksheet" Then hddn = ""
                                  End If
                              hddn = ""
                          ' If no errors found in the workbook. Display the "No Errors" dialog and exit sub
                          If errCnt = 0 Then
                              Finish = Timer - PauseTime
                              Application.StatusBar = "No Errors Found in Workbook " & ActiveWorkbook.Name
                              myMsg = vbCrLf & "No Cell Errors in Workbook:" & vbCrLf & vbCrLf
                              myMsg = myMsg & "   '" & ActiveWorkbook.Name & "'" & vbCrLf & vbCrLf
                              myMsg = myMsg & Format(wbCellsTot, "#,#") & " Cells Reviewed in " & Format(wsCnt, "#,#") & " Worksheets"
                              myMsg = myMsg & vbCrLf & vbCrLf & " Elapsed Time = " & Format(Finish - Start, "0.0000") & " Secs"
                              MsgBox myMsg, vbInformation, "Check Workbook For Cell Errors"
                              'Clean up the application settings & Exit
                              GoTo Normal_Exit
                          End If
                          ' If errors were found, add a new worksheet after the last worksheet
                          ' in the workbook and write the error array contents beginning in A2
                          Application.StatusBar = "Populating and Formatting the 'Error Cells+' Worksheet"
                          Set ws = Worksheets.Add(After:=Sheets(Sheets.Count))
                          ws.Name = "Error Cells+"
                          ActiveWindow.DisplayGridlines = False
                          Range("A1:E1").Value = Array("Worksheet", "Cell", "Hidden", "Error", "Formula")
                          [A2].Resize(errCnt, 1) = StrArry
                          [B2].Resize(errCnt, 4) = NumArry
                          If Val(Application.Version) >= 12 Then   'Excel version 2007 or greater
                              ActiveSheet.ListObjects.Add(xlSrcRange, , , xlYes).Name = "Tbl_CellErrors"
                              Columns("C:C").HorizontalAlignment = xlCenter
                              With Range("E:E")
                                  .ColumnWidth = 100
                                  .WrapText = True
                              End With
                          Else    '  Versions earlier that Excel 2007
                              Columns("C:C").HorizontalAlignment = xlCenter
                              With Range("A1:E1")
                                  .Font.Bold = True
                                  .Interior.Pattern = xlSolid
                                  .Interior.PatternColorIndex = xlAutomatic
                                  .Interior.ColorIndex = 37
                              End With
                              With Columns("E:E")
                                  .ColumnWidth = 100
                                  .WrapText = True
                              End With
                              With Range("A1").CurrentRegion
                                  .Borders(xlEdgeLeft).LineStyle = xlContinuous
                                  .Borders(xlEdgeTop).LineStyle = xlContinuous
                                  .Borders(xlEdgeBottom).LineStyle = xlContinuous
                                  .Borders(xlEdgeRight).LineStyle = xlContinuous
                                  .Borders(xlInsideVertical).LineStyle = xlContinuous
                                  .Borders(xlInsideHorizontal).LineStyle = xlContinuous
                              End With
                          End If
                          ' Convert the error cell addresses to Hyperlinks
                          Application.StatusBar = "Generating Hyperlinks to the Cell Errors Found in the Workbook"
                          If errCnt > 1000 Then
                              HyperCounter = 1000
                              HyperCounter = errCnt
                          End If
                          Set rngCell = [B2]
                          For cntr = 1 To HyperCounter
                              ActiveSheet.Hyperlinks.Add Anchor:=rngCell, Address:="", SubAddress:= _
                                                         "'" & rngCell.Offset(0, -1) & "'!" & rngCell.Value, TextToDisplay:=rngCell.Value
                              rngCell.Font.ColorIndex = 32
                              If rngCell.Offset(0, 1) = "Worksheet" Then
                                  strComment = "Unhide the '" & rngCell.Offset(0, -1) & "' worksheet for the hyperlink to function"
                                  rngCell.AddComment Chr(10) & strComment
                              End If
                              Set rngCell = rngCell.Offset(1, 0)   'Drop down one cell
                          Next cntr
                          'Add Heading and Date/Time
                          Application.StatusBar = "Formatting the Error Report Worksheet"
                          Rows("1:12").Insert Shift:=xlDown
                          [A1] = "Cell Errors in Workbook - '" & ActiveWorkbook.Name & "'"
                          [A2] = Format(Date, "short date") & " @ " & Format(Time, "short time")
                          With Range("A1:E2")
                              .HorizontalAlignment = xlCenter
                              .VerticalAlignment = xlCenter
                              .Font.Size = 14
                              .Merge True
                          End With
                          'Add Footer
                          With ActiveSheet.PageSetup
                              .LeftFooter = "&8&F-(&A)"
                              .CenterFooter = "&8Page &P of &N"
                              .RightFooter = "&8Printed on &D @ &T"
                              .LeftMargin = Application.InchesToPoints(0.25)
                              .RightMargin = Application.InchesToPoints(0.25)
                              .TopMargin = Application.InchesToPoints(0.75)
                              .BottomMargin = Application.InchesToPoints(0.75)
                              .CenterHorizontally = True
                              .Orientation = xlPortrait
                              .Zoom = False
                              .FitToPagesWide = 1
                              .FitToPagesTall = False
                          End With
                          'Add stats, error index values and generic causes
                          [A4] = "Summary:"
                          [A5] = "#Worksheets"
                          [B5] = Format(wsCnt, "#,#")
                          [A7] = "#Used Cells"
                          [B7] = Format(wbCellsTot, "#,#")
                          [A9] = "#Errors Found"
                          [B9] = Format(errCnt, "#,#")
                          [A11] = "Timer (Secs)"
                          [D4] = "Error Index:"
                          Rows("4:4").Font.Bold = True
                          Range("D5:E5").Value = Array("#DIV/0!", "occurs when a number is divided by zero (0)")
                          Range("D6:E6").Value = Array("#N/A", "occurs when a value is not available to a function or formula")
                          Range("D7:E8").Value = Array("#NAME?", "occurs when Microsoft Excel doesn't recognize text in a formula")
                          Range("D8:E8").Value = Array("#NULL!", "occurs when an intersection of two areas do not intersect")
                          Range("D9:E9").Value = Array("#NUM!", "occurs with invalid numeric values in a formula or function")
                          Range("D10:E10").Value = Array("#REF!", "occurs when a cell reference is not valid")
                          Range("D11:E11").Value = Array("#VALUE!", "occurs when the wrong type of argument or operand is used")
                          With Range("D5:E11, A5:B5, A7:B7, A9:B9,A11:B11")
                              .Borders(xlEdgeLeft).LineStyle = xlContinuous
                              .Borders(xlEdgeTop).LineStyle = xlContinuous
                              .Borders(xlEdgeBottom).LineStyle = xlContinuous
                              .Borders(xlEdgeRight).LineStyle = xlContinuous
                              .Borders(xlInsideVertical).LineStyle = xlContinuous
                              .Borders(xlInsideHorizontal).LineStyle = xlContinuous
                          End With
                          ' Write the Elapsed Time, fit columns
                          [B11] = Format(Timer - Start - PauseTime, "#.####")
                          If HyperCounter = 1000 Then MsgBox "The Hyperlink Process was Stopped after the First 1,000 Errors to Reduce User Wait Time"
                          'Clean up the application settings & Exit
                          With Application
                              .DisplayAlerts = True
                              .ScreenUpdating = True
                              .EnableEvents = True
                              .StatusBar = False
                          End With
                          On Error GoTo 0
                          Exit Sub
                          If Err.Number = 1004 Then Resume Next   ' No Error or Constant Special Cells were found
                          myErrMsg = vbCrLf & "An Unexpected Error has Occurred in Procedure 'FindCellErrors' " & vbCrLf & vbCrLf
                          myErrMsg = myErrMsg & "   Error #" & Err.Number & " (" & Err.Description & ")" & vbCrLf & vbCrLf
                          myErrMsg = myErrMsg & "The Program is Terminating Without Completing the Reporting Process     "
                          MsgBox myErrMsg, vbCritical, "'Find Cell Errors' Utility - System Error Occurred"
                          Resume Normal_Exit
                      End Sub

Open in new window

Spreadsheet Auditing Tools

FindCellErrors is a simple single use utility that was interesting to write and is very useful for finding cell based formula errors. However, there are a number of professionally built spreadsheet auditing suites on the market that perform a variety of complex functions for auditing Excel workbook models. An excellent example of these tools is the Mappit! Add-in written by Dave Brett which can be downloaded from the Experts Exchange Article located here. Within Dave's article are links to a number of other sites and auditing tools for your reference.

If you found the article helpful please click on the " YES" button after the question below.
This utility has been helpful to me and I hope you find an opportunity to use it as well.

If you didn't or otherwise want to vote No, please leave a comment and give me an
opportunity to respond. It will give me a chance to improve this article and others I
would like to write.

Thank You for Stopping by to Read the Article!
Jerry Paladino

Comments (6)



Thanks Dave!
Top Expert 2010


Very nice debut article!




Thank you.  Some credit has to go to Markus(harfang) who was the EE page editor that worked with me.  The first submission could have been published but Markus made several excellent suggestions for improvement and helped make it a much better article than the original.



Thanks for the acknowledgement, but your really did all the work yourself, including rewriting entire portions of the code to improve it. It was a pleasure working with you, for your communicative enthusiasm, your willingness to go through several editorial cycles, and your candour in light of constructive criticism.

Great job! I wish plenty of success to this article and to you as an author.

Markus — (°v°)


Wow!  Thank you Patrick.   I really appreciate being selected.  Writing this article was very educational for me personally and I hope others have found it useful as well.


View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.