"A problem with one or more formula references"
The message appears when saving, without saying where. The broken reference is often not in a cell: it is in a defined name, a chart, a drop-down list or a conditional format.
What the message says
Somewhere in the workbook, a reference points to a cell, a sheet or a workbook that no longer exists, or is written in a way Excel can no longer read.
Why it appears
- A defined name equal to #REF!, often hidden, arrived with a copied sheet.
- A chart whose series points to a deleted sheet.
- A drop-down list or conditional format pointing to another workbook.
- A link to an external workbook that was moved or renamed.
Making it go away
- Formulas, Name Manager: sort by "Value", and delete the names equal to #REF!.
- Check the source of each chart's series.
- Data, Edit Links: list the workbooks targeted.
The tool that handles it
The health check lists broken names and links, and removes them in one click; Remove external links says where a link hides in a list or a chart; the formula audit finds the #REF! in cells.
Frequently asked questions
- The message comes back at every save: is it serious?
- The file saves, but the broken reference stays: a calculation or a chart may depend on it and show a wrong or empty value.