Troubleshooting

Also see the Functions > Troubleshooting page.


One or More Invalid References

Every time I save my workbook, the following warning message keeps appearing
A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell reference

The most common cause is an invalid reference inside a chart.


To recreate:
Create a new blank workbook
Select cells "A1:B6" on worksheet "Sheet1"
Enter the formula "=rand()" and press (Ctr + Enter)
Display the Insert Tab and select Column, Clustered Column to add a chart to Sheet1
Copy the chart and paste it onto "Sheet2"
Delete Sheet1
Right click on the chart on Sheet2 and choose "Select Data"
Press Edit under Legend Entries
You will see a #REF in the series values reference


######

This is displayed when a column is not wide enough to display the result. This is not technically an error.
Using a negative date or time.
A ##### error value occurs when the cell contains a number, date, or time that is wider than the cell or when the cell contains a date or time formula that produces a negative result. Try increasing the width of the column.
A number which is not a valid date serial number but has a date number format.


Generating Errors

It can sometimes be useful to generate different types of errors when testing your workbooks and formulas.
The following table shows you examples of formulas that will generate the necessary error values.


Finding Errors

Sometimes when you enter a formula an error will occur. This is to indicate that the formula syntax is incorrect.
If this error occurs press OK to be taken back to the formula bar. You can either correct the formula or press ESC to remove the formula completely.
This error may be caused by missing parentheses or incorrect arguments being passed to functions (e.g. passing a string when it is expecting a number).
To quickly locate any cells that contain errors, select (Home tab, Find & Select > GoTo Special) and tick the Formulas, Errors checkbox.


Preventing Errors

A common method used to try and eliminate errors from appears on your worksheet is to use the ISERROR worksheet function as a wrapper.

The formula in cell B2 tries to divide D2 by D3, which generates an error as division by zero is not possible.
The formula in cell B3 includes the ISERROR function as a wrapper around the formula.



© 2025 Better Solutions Limited. All Rights Reserved. © 2025 Better Solutions Limited TopPrevNext