Reference errors and troubleshooting in Excel - Cell-by-Cell Formula Trace

Sample Data

Sample data with numbers in columns A and B, formulas in columns C and D demonstrating reference errors and troubleshooting.

Cell	Value
A1	10
A2	20
A3	30
B1	5
B2	0
B3	15
C1	=A1/B1
C2	=A2/B2
C3	=A3/B4
D1	=SUM(A1:A3)
D2	=VLOOKUP(25, A1:B3, 3, FALSE)

Formula Trace

=A3/B4

Step 1: B4

Step 2: A3 / #REF!

Cell Reference Map

    A     B     C     D
1 | 10 |  5  | =A1/B1 | =SUM(A1:A3)
2 | 20 |  0  | =A2/B2 | =VLOOKUP(25, A1:B3, 3, FALSE)
3 | 30 | 15  | =A3/B4 |       
4 |    |     |        |       

References in formulas: C1 uses A1 and B1; C2 uses A2 and B2; C3 uses A3 and B4 (which is missing); D1 sums A1 to A3; D2 uses VLOOKUP with range A1:B3 and column 3 (which is invalid).

Result

    A     B     C       D
1 | 10 |  5  | 2       | 60
2 | 20 |  0  | #DIV/0! | #REF!
3 | 30 | 15  | #REF!   |    

Final results: C1 calculates 10/5 = 2; C2 divides by zero causing #DIV/0! error; C3 references missing B4 causing #REF! error; D1 sums 10+20+30=60; D2 returns #REF! because VLOOKUP requests column 3 which does not exist in A1:B3.

Sheet Trace Quiz - 3 Questions

What error appears in cell C2 and why?

A #DIV/0! because dividing by zero is not allowed

B #REF! because B2 is missing

C #NAME? because formula is incorrect

D No error, result is 0

Key Result

Reference errors occur when formulas refer to cells that do not exist or invalid ranges.