How to identify and correct #REF!

[Kat B.]

I have a column of formulas that are copies all the way down, most have a
zero in the cell, but a few have #REF and I don't know where the error is.
How can I identify the bad cell?

 

[ker_01]

It depends somewhat on the formula. In Excel 2003 and 2007, you should be
able to click on the cell and see a little 'warning' icon hovering to the
side of the cell. Mouse over that and click the down arrow next to it, and a
menu appears. Select "show calculation steps" and you can calculate each step
of your formula and see when the error appears. This works particularly well
for complex formulas with many "layers" where it can calculate a section of
the formula at a time, and also array formulas like sumproduct, where it can
show you each calculation's output and you can kind of figure out which array
element is causing the problem.

If that doesn't get you what you need, consider posting the formula that is
giving you problems, and maybe a community member will be able to give you
some more specific suggestions based on your formula.

Best,
Keith

 

[Roger Nye]

For complex formulae you may be able work through the terms or sub-components
systematically, to identify which part of the formula has the problem and so
narrow your search down.

My usual approach is:
Edit the formula with F2, select a sub-component in the formula and hit F9
to see if that component is #REF.
If not and it gives a valid number, press Esc and try the next sub-component.

Once you have found the culprit it is easier to trace the cause from a
simple sub-component.

It won't work with all formulae (for example some range-arrays) but it has
helped me a lot of times.
Good luck