How do you find an Excel problem reference

Question

Need to locate the formula in Excel that has a problem refernce

Answer 1

To locate formulas with broken references in Excel, press Ctrl + F, type #REF!

Change “Look in” to “Formulas” and “Within” to “Workbook”, then select “Find All”. Excel will display every formula containing a broken reference, and selecting an entry will take you directly to the affected cell.

You can also press F5 or Ctrl + G, choose “Special”, select “Formulas”, then check only “Errors”. This highlights all cells where formulas are returning errors, including broken references.

If you want to identify the exact missing range or deleted cell, select the affected formula and use Formulas > Trace Precedents

Broken references commonly appear after rows, columns, sheets, or linked workbooks were deleted or renamed.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

Hi @Blake Hodges,

For your concern, there are ways to locate an Excel formula with a bad reference. You can use either of the following options:

1/ Use Excel Go to Special

• Press Ctrl + A to select all of your used range and then press Ctrl + G to open Go To dialog
• Select Special... >choose Formulas > tick only Errors > OK

2/ Use Error checking

• In the Formulas tab, you can use Error Checking and Trace Error to quickly identify and navigate to the cell that contains error in formula

3/ Manually checking by showing all formulas

• You can press Ctrl + ` to make Excel display all the formulas instead of results which making it easier to visually spot broken/bad references formula, e.g =SUM(!REF#:C10)

Hope my sharing will be helpful for your concern. If you have any questions or need any further clarification, please leave a comment here and I will be happy to continue to help you.  

Thank you for your patience and understanding. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread