Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Tracking Down Invalid References.

Tracking Down Invalid References

Written by Allen Wyatt (last updated December 3, 2021)
This tip applies to Excel 2007, 2010, and 2013


Joel noted that when he closes a workbook that has thousands of formulas in it he is getting this message lately: "A formula in this worksheet contains one or more invalid references." Joel wonders how he can know which of the seven worksheets in this workbook is being referred to. How can I find the errant formula? I do not observe any problems in the display of information on my reports.

Tracking down invalid references can be frustrating. There are several places you can start to look. The first is in the formulas that are on the worksheets. (Yes, you need to do these steps for each worksheet in the workbook.) Use the Go To Special dialog box (press F5 and choose Special) to choose to go to only the cells that contain errors. You can then use the Tab key to move amongst any cells that Excel selects.

You could also use the Find tool to look for possible errors. Just press Ctrl+F to display the Find tab of the Find and Replace dialog box, then search for the # character. Make sure you tell Excel to do its searching within Formulas. Inspect anything that is found to see if it is an error or not.

You should also take a look at any named ranges defined in your workbook. Look at each name in the Name Manager dialog box (Formulas tab, click the Name Manager tool), making sure that whatever is in the Refers To column doesn't include any error indications.

These aren't all the places that there could be errors; Excel is really good at letting errors exist in lots of places. If you need to search for errors often, you might try a macro that looks through your formulas for any potential errors.

Sub CheckReferences()
' Check for possible missing or erroneous links in
' formulas and list possible errors in a summary sheet

  Dim iSh As Integer
  Dim sShName As String
  Dim sht As Worksheet
  Dim c, sChar As String
  Dim rng As Range
  Dim i As Integer, j As Integer
  Dim wks As Worksheet
  Dim sChr As String, addr As String
  Dim sFormula As String, scVal As String
  Dim lNewRow As Long
  Dim vHeaders

  vHeaders = Array("Sheet Name", "Cell", "Cell Value", "Formula")
  'check if 'Summary' worksheet is in workbook
  'and if so, delete it
  With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
  End With

  For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "Summary" Then
    End If
  Next i

  iSh = Worksheets.Count

  'create a new summary sheet
    Sheets.Add After:=Sheets(iSh)
    Sheets(Sheets.Count).Name = "Summary"
  With Sheets("Summary")
    Range("A1:D1") = vHeaders
  End With
  lNewRow = 2

  ' this will not work if the sheet is protected,
  ' assume that sheet should not be changed; so ignore it
  On Error Resume Next

  For i = 1 To iSh
    sShName = Worksheets(i).Name
    Application.Goto Sheets(sShName).Cells(1, 1)
    Set rng = Cells.SpecialCells(xlCellTypeFormulas, 23)

    For Each c In rng
      addr = c.Address
      sFormula = c.Formula
      scVal = c.Text

      For j = 1 To Len(c.Formula)
        sChr = Mid(c.Formula, j, 1)

        If sChr = "[" Or sChr = "!" Or _
          IsError(c) Then
          'write values to summary sheet
          With Sheets("Summary")
            .Cells(lNewRow, 1) = sShName
            .Cells(lNewRow, 2) = addr
            .Cells(lNewRow, 3) = scVal
            .Cells(lNewRow, 4) = "'" & sFormula
          End With
          lNewRow = lNewRow + 1
          Exit For
        End If
      Next j
    Next c
  Next i

' housekeeping
  With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
  End With

' tidy up
  Range("A1:D1").Font.Bold = True
End Sub

This macro creates a worksheet called "Summary" that is used to list information about any errors detected in the worksheet links.

You can also use Excel MVP Bill Manville's FindLink program, which does an amazing job of locating information in links. You could use the add-in to search for the # character in all your links, which should help you locate the errors. It is unclear as to whether the add-in will work with Excel 2013, as the last update for the file (as of this writing) was in 2011. It is worth trying though; more information on FindLink can be found here:


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8664) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Tracking Down Invalid References.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Keeping a Picture Title with the Picture

Pictures and their titles go together like peanut butter and jelly. (Wow, did I just say that?) Seriously, pictures and ...

Discover More

Understanding the WinX Menu

Windows provides a special menu that allows you to easily access many common tools in the operating system. Here's the ...

Discover More

Leaving Minimized Documents Minimized

If you have open documents minimized on the Taskbar and then open another document, all of the previously minimized ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Deriving Monthly Median Values

When processing huge amounts of data, it can be a challenge to figure out how to derive the aggregate values you need. ...

Discover More

Returning the Rightmost Value in a Row

Do you need to figure out the rightmost value within a row in which not all cells may contain values? This tip provides a ...

Discover More

Outlining Cells Referenced in a Formula

When you are editing a formula, Excel helpfully outlines the cells referenced in the formula. If you want this capability ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 4?

2021-12-03 08:27:45


One place I find a lot of this kind of thing is in graph ranges.

2017-12-02 10:42:38

Sparrows D

Ha! Great! I entered #GivingTuesday in one of my cells (as a category of donations) and Excel saw this as an Error in my formula. I'm so dumb.

Microsoft, the internet, and hashtags, will become very popular sometime in the very distant future, learn to deal with it.

2017-10-30 03:40:21

Chris Sharpe

Alan, I can confirm that the FindLink Addin works fine in Excel 2016. A fantastically useful tool, part of my essential armoury. Many thanks to Bill for creating it.

2016-11-17 23:57:54


Can't use different forms of .Count in these two places as Worksheets.Count does not count hidden Sheets, whereas Sheets.Count does

iSh = Worksheets.Count
'create a new summary sheet
Sheets.Add After:=Sheets(iSh)
Sheets(Sheets.Count).Name = "Summary"

would be better as:
iSh = Sheets.Count
'create a new summary sheet
Sheets.Add After:=Sheets(iSh)
Sheets(Sheets.Count).Name = "Summary"

2016-05-13 09:24:06

Ben S.

I had to add "Set rng = Nothing" at the bottom of the loop and a check for it at the top because it was falsely reporting formulas on sheets that did no have any, when a previous sheet had some.

Allen, send me an email and I'll send you my update. I also added a status message, freeze panes, hourglass cursor, beep at end.

2016-04-20 15:47:09


Thank you for the very helpful tip!

2016-02-24 06:53:47

Willy Vanhaelen


Your are right but in this case you can better replace those 3 lines with:

Sheets("Summary").Range("A1:D1") = vHeaders

Since Sheets("Summary") is used only once it has no sence to use With ... End With

2016-02-23 07:19:58


Missing dot in front of word 'Range':
With Sheets("Summary")
Range("A1:D1") = vHeaders
End With

2016-02-02 13:44:30

Rick Sheeley

Excellent tip, found 90% of issues.

2016-02-02 10:40:17


Another place where this invalid references can occur is in charts.

2016-01-03 06:53:51


This did not find my problem. I am workign with excel files that save out on SharePoint 2007; several of them have the error:

"We found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names and links to other workbooks in your formulas are all correct."

2015-09-10 11:41:53


The Macro code solved the problem for me.

Thank you

2015-07-01 15:59:29


Thanks for useful code!
Just small improvment for easy to go to cell:-)
vHeaders = Array("Sheet Name", "Cell", "Link", "Cell Value", "Formula")
With Sheets("Summary")
.Cells(lNewRow, 1) = sShName
.Cells(lNewRow, 2) = addr
.Cells(lNewRow, 3).FormulaR1C1 = "=HYPERLINK(""#""&RC[-2]&""!""&RC[-1],""GO!"")"
.Cells(lNewRow, 4) = scVal
.Cells(lNewRow, 5) = "'" & sFormula
End With


2014-06-09 16:56:43


A couple of issues + fixes:

1. The line

Dim c, sChar As String

will give the compile error:

"For Each control variable must be Variant or Object"

This can be fixed by replacing the above with:

Dim c As Variant
Dim sChar As String

2. It is a good idea to include a DoEvents statement at the top of your loops. This basically tells the PC to see if Windows and any other applications have any processing to do, and allow them to do it, so that Excel doesn't hog 100% of your CPU, which can freeze up your machine until the loop in your macro is finished. So you would change above to look like:

' For i = 1 To iSh
' DoEvents
' ...
' For Each c In rng
' DoEvents
' ...
' For j = 1 To Len(c.Formula)
' DoEvents
' ...
' Next j
' Next c
' Next i

3. In VBA and VBScript, before exiting a given scope such as a sub or function, it is a best practice to explicitly release any objects (non-primitive types) from memory.

Adding the following lines to the bottom of the routine (right before End Sub) would protect against memory leaks (in any case they can't hurt):

' release objects from memory
Set wks = Nothing
Set sht = Nothing
Set rng = Nothing

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.