If you receive the message “Excel found a problem with one or more formula references in the worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are correct” (as per below) you may struggle to find the problem.
First step- #REF errors in the cells
The first and easiest step is finding any #REF errors in the cells. Normally you can just see them, but sometimes it is not easy. A quick way is to use the FIND tool and search through the WHOLE workbook for REF within any formula. Keep in mind the problem could be (and often is) in hidden sheets that you think are not being used.
No REF errors- where to look next
If you perform the above and you find nothing, we find the one quick place to look is in the one place that the warning message does not mention. Look in any charts you have.
Right click on the chart and click ‘Select Data’ as shown below.
Choose one of the series and click Edit.
The series values may contain the error.
Unfortunately you might have many charts so you will need to work through this manually unless you use a tool.
Another common one is any Data Validations you have set up with a link to cells (that may have been deleted).
Where else to look for REF errors that are not in the cells
As the error message says, you need to look in any area that uses cell references. Similar to charts, there are many tools that do not appear directly in cells but which get affected if you delete a row or column. Again, unless you use a tool, you may need to look through these manually. So have a look in your Named Ranges, defined names, links to other workbooks.
Where else to look for REF errors
Don’t forget other ‘non cell’ references e.g. Pivot Tables, VBA, Sparklines, Data Validation.