4

Upon saving my Excel sheet, I receive the following error message:

error message

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.

“Error Checking” (under “Formulas” → “Formula editing”) returns

The error check is completed for the entire sheet.

What is the fastest way to find what is causing this problem?

Excel in Office 365 ProPlus, Windows 7.

  • In this specific case, the problem seemed to be caused by a chart with empty data ranges. – Karlo Nov 20 '15 at 11:04
  • OK. I hadn't answered it yet because it is not a general solution to this problem. And I have to wait for 2 days before I can accept my own answer. – Karlo Nov 20 '15 at 12:21
  • 1
    In my copy of Excel 2013, “Error Checking” is under “Formulas” → “Formula Auditing”. – Scott Nov 11 '16 at 19:18
7

The problem was caused by a chart with empty data ranges. This potential cause was mentioned in this forum.

  • 5
    Please add more detail on how you determined this, otherwise it's not a very useful answer – Raystafarian Nov 20 '15 at 12:23
  • 2
    (+1) Detail or not, after an hour debugging my formulas and searching Google this is the first answer I've found to this problem which actually solved my problem! I have a very fancy spreadsheet which builds my dynamic chart for me, but I'd never considered what would happen if a whole column of data was left as zero or empty. – Steve Taylor Nov 11 '16 at 15:03
3

This is caused by empty values in named ranges/cells.

To debug, go to Formulas > Name Manager. Look for Named Ranges with an empty value. Either change the where the reference points (the "Refers to:" column) such that it pulls in a value, or delete the reference if it is not needed.

Be careful deleting named ranges, as you can cause some downstream #REF errors.

1

This error occur from name manager (Dynamic range) mostly. if you have define any range and there is no data then it will come. please check you dynamic ranges and fill some data for test then it will resolve.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.