Microsoft Office Tutorials and References

In Depth Information

**Understanding How Excel Handles Formula Errors**

Circular Reference

A circular reference does not result in an error

message that appears in the result cell. But Excel

does send up a few flags when it occurs.

Avoiding Common

Formula Errors

There are other errors you might make when

entering a formula, and those errors may not

result in an error message such as DIV/0! being

displayed in a cell. Here is a list of things you

should be careful of when typing a formula:

First off, a
circular reference
is caused when a

formula references itself. For example, suppose

you type =SUM(B2:B10) in cell B10. You are

telling Excel to add all the values in the range

B2:B10, including the total you are creating in

cell B10, which of course changes that total,

which changes the total again, and so on, in an

endless circular loop.

Make sure you type equals sign (=) at

the start of every formula.
If you don’t,

Excel typically thinks that what you’ve

typed is text, and displays it in the cell

without calculating anything. For

example, if you type AVERAGE(B4:B7), you’ll

see that text, and not a result. Some

formulas, when you forget the equals

sign (=), are interpreted as a date: if you

type 3/8 instead of =3/8, you get March

8 instead of 0.375.

When you accidentally create a circular

reference, an error message appears on the status bar

similar to: “Circular Reference:F12”. If you open

a workbook that contains a circular reference,

you will see a warning telling you that the

workbook contains one or more circular references.

Don’t forget to match up all your

parentheses.
If you forget a closing parenthesis,

Excel will warn you of the problem and

may attempt to “fix” your formula by

adding a closing parenthesis. This guess

may not be what you wanted. The only

way to know for sure is to carefully check

the formula and add the parenthesis

where it belongs.

If you need to enter a date in a formula,

make sure you use a four-digit year
.

If you don’t, Excel may assume you mean

a year from the 20th century instead of

the 21st. For example, if you enter

=YEAR(“02/20/25”), Excel assumes

you mean 02/20/1925, rather than

02/20/2025.