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.
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.
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:
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
Worksheets(i).Delete
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
Sheets("Summary").Select
Columns("A:D").EntireColumn.AutoFit
Range("A1:D1").Font.Bold = True
Range("A2").Select
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:
http://www.manville.org.uk/software/findlink.htm
Note:
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.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
Want to be able to take information that is in one cell and match it to data that is contained in a table within a ...
Discover MoreWhen you enter a formula from the keyboard, Excel only knows it is a formula if you start it with an equal sign. You can ...
Discover MoreThe primary way you signify that you are entering a formula is to start a cell entry with an equal sign. The equal sign ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-11-11 00:45:26
Steve Wells
For casual use, copies and copies-of-copies may be fine. Eventually, multi-generation copies degrade. I adjust for the target audience.
I used to ship comb-bound documentation for multi $100,000 systems. So, only first generation printouts on 28 lb high-brightness paper. End users were usually in a subfloor wiring equipment racks together. They loved comb bindings that enabled them to lay manuals flat open. A-size pages beat laptops for viewing details and the BIG picture.
2025-11-09 15:58:38
Tomek
@Steve Wells
I agree that for one-time printing of a few copies of the assembled document, my approach may be an overkill and error-prone. The easiest approach can be to print one copy in two passes for single- and double- sided, re-assemble it in the order required, than use a double-sided copier to get additional copies. You can quickly get 2, then 4, then 8, and so on... copies.
2025-11-09 05:41:08
Steve Wells
I acknowledge Tomek's ingenuity and work for very complicated page printing setups and repeats over time.
However, suppose the issue is simply, maybe a dozen sets for a proposal in a small business meeting where the content is likely to receive major edits by the next decision point. All the page break points would be different.
Somewhat like the originally posed problem, consider a few copies at most of a 50-page job. Say, 18 pages of single-sided, 20 pages double-sided, and 12 more single-sided. As a practical matter for its simplicity, I'd do the printing as a manual collation process. For all but high-volume printing, it's more time and effort (and maybe more error-prone) to overthink it. In nominal quantities on my own duplex printer, I'd set my printout to single sided, print pages 1-18,39-50. Set to double-sided and print 19-38. Stack the sets manually—good enough. As an analogy, just because I can develop a complex find/replace regular expression, I don't do it to edit a street address in 2 places in a document.
At a job years ago, I had to print lots of user manuals where a few pages had to be in color using a special color printer (to which I had limited access) and all the rest in B&W/grayscale on a high-volume printer. Manual collation was the easy solution, even with a dozen or so copies.
High or repeat volume? Allen's PDF solution above looks pretty good. Unchanging and huge volume? Review Tomek's approach.
2025-11-09 00:13:46
Tomek
Allen stated that "The short answer is that this cannot be done in Word." That may not be completely true - I found the way to set up the document to print it the way Jim wanted, completely within Word with some creativity. Although it is a bit convoluted, the logic of this is simple.
The solution depends on the fact that you can tell Word which pages to print and in which order. For example, you can specify to print pages 1,5-9,20-25,3,3,3,3 and it will print the selected pages in that order including four copies of page 3 at the end.
I suggest inserting a blank page at the front of the document. The page should be completely blank and have neither header nor footer (use different first page header/footer If needed. To keep the page numbers of the rest of the document starting from 1,
I suggest setting this first page number to 0.
Once this is set up, in the print dialog under Settings select “Custom Print” and specify pages to print. For example, if you want to print pages 1-5 single-sided, pages 6-24 double sided, pages 25-30 single sided you would enter:
1,0,2,0,3,0,4,0,5,0,6-24,0,25,0,26,0,27,0,28,0,29,0,30
Please note a 0 after the range 6-24 – this is to prevent page 25 printed on the back of page 24, as the range 6-24 contains odd number of pages.
Unluckily, page selection for printing is not saved with the document and remembered session to session, so it would be tedious to re-enter this information every time the document needs to be printed, but you can copy this selection and paste it into one of the document properties like comments and easily retrieve it when needed.
Below are the steps to achieve the above:
1. Once the document is finished move the cursor to the very beginning of the document and press Ctrl+<ENTER>.
2. Make sure that the document is set to have different first page header/footer. On the empty first page, create a header containing an automatic page number. Right click on it and select “Format Page Numbers”. Select start at 0 and click OK. To keep the page completely blank delete that header; the page number will stay set at 0.
3. Press Ctrl+P to go to print dialog. Select “Custom Print” and specify pages to print as specified above.
4. For safekeeping, copy the page selection and switch to the info tab. Paste the clipboard content into comments field, and save the file.
5. Go back to print dialog. Make sure that double-sided printing is selected.
6. Print the document.
NOTE that the page numbers to be printed are the ones assigned by page numbering, not always matching the numbers indicated in the left part of the status bar.
The blank page could be any page of the document including the last one, as long as you know its number, but having it at the beginning makes it unaffected by any additions or changes of the main document. And assigning it number 0 makes the logic more clear.
This approach has the following advantages over the workarounds suggested in the tip and by the readers’ comments (including earlier one of mine):
• The printout comes assembled in the order you specify, no need to rearrange single- and double-sided pages.
• No need to manually insert blank pages and deal with disrupted page numbering.
• No need to use an external program like PDF reader to insert blank pages, which has the benefit of not disrupting page numbering. Having said that, you can use my approach to print-to-PDF file and have the same result as the second workaround from the tip.
• Changes to the original document are minimal: it only has one extra blank page at the beginning.
2025-11-08 19:44:04
Tomek
I have a potential improvement on @Frank's suggestion: Instead of inserting blank pages, that would somewhat clutter the document. at the end of each page that needs to be printed single sided, insert a section break selecting odd or even page option to skip the next page. In other words you need to choose odd or even page the same as the current page. You need to do it sequentially from the start of the document, because this will affect the numbering of the following pages.
Nice thing about this approach is that any footers or headers will not be printed on the back side of the single sided page similarly to creating a PDF with added empty pages. However, the page numbering in the headers or footers will count the skipped pages, as it would with Frank's approach. I thought you could use your own page counter, but contrary to Microsoft documentation, the SEQ field creates an "Error! Main Document Only" if placed in a header or footer.
2025-11-08 18:27:32
Steve Wells
The 'blank pages in the Word document' approach sounds good, except if your document includes headers and/or footers / page numbers.
Be careful about what the "blank" pages look like. You might want to break the document into individual sections and eliminate headers/footers on even pages in the single-sided sections. That may be more work than the PDF approach, or not.
2025-11-08 15:08:33
Frank
I don't need to take the detour with PDF, but can insert extra page breaks in the word document and print directly. If I don't want to keep the blank pages in my document, I save it before I insert the blank pages, but not thereafter.
2025-11-08 12:40:38
Craig Buback
Re: printing duplex for a partial document
Why convert to PDF
Will it not work to simply add page breaks in appropriate places to create the “blank” second side? I am away from my printer for a couple of weeks, so I cannot verify
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments