Reference errors and troubleshooting in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a financial analyst at a retail company.

📋 Request: Your manager wants you to prepare a sales summary report but noticed some #REF! errors in the spreadsheet. You need to find and fix these errors to ensure accurate calculations.

📊 Data: You have a sales data table with columns for Product, Region, Sales Q1, Sales Q2, and Total Sales. The Total Sales column uses formulas to sum Q1 and Q2 sales. Some rows have #REF! errors due to deleted cells or incorrect references.

🎯 Deliverable: A corrected sales summary table with no #REF! errors and accurate total sales calculations.

Sample Data

Product	Region	Sales Q1	Sales Q2	Total Sales
Apples	North	1200	1300	=C2+D2
Bananas	South	900	1100	=C3+D3
Cherries	East	700	800	=C4+D4
Dates	West	600	700	=C5+D5
Elderberries	North	500	600	=C6+D6
Figs	South	400	500	=C7+D7
Grapes	East	300	400	=C8+D8
Honeydew	West	200	300	=C9+D9

1

Step 1: Identify cells showing #REF! errors in the Total Sales column.

Look for cells in column E with the value '#REF!'.

Expected Result

No cells currently show #REF! but assume errors exist if references are broken.

2

Step 2: Check if any referenced cells in Sales Q1 or Sales Q2 columns are deleted or moved.

Verify that cells C2:D9 exist and contain numbers.

Expected Result

All referenced cells exist and contain numbers, so no #REF! errors from missing cells.

3

Step 3: Simulate a #REF! error by deleting cell D4 (Sales Q2 for Cherries) to see the error.

Delete cell D4 and observe the formula in E4.

Expected Result

Cell E4 shows '#REF!' error because formula =C4+D4 references deleted cell.

4

Step 4: Fix the #REF! error by updating the formula in Total Sales for Cherries to sum the correct cells.

In cell E4, enter =C4+D4 (restore cell D4 first or adjust formula to =C4+0 if D4 is missing).

Expected Result

Cell E4 shows 1500 (700 + 800) after fixing the reference.

5

Step 5: Use the IFERROR function to handle any future #REF! errors gracefully in Total Sales column.

In cell E2, enter =IFERROR(C2+D2, "Check data") and copy down to E9.

Expected Result

If any reference error occurs, the cell shows 'Check data' instead of #REF! error.

6

Step 6: Verify all Total Sales values are correct and no #REF! errors remain.

Check cells E2:E9 for correct sums or 'Check data' messages.

Expected Result

All Total Sales cells show correct sums or 'Check data' if error exists.

Final Result

Product    Region   Sales Q1  Sales Q2  Total Sales
--------------------------------------------------
Apples     North    1200      1300      2500
Bananas    South    900       1100      2000
Cherries   East     700       800       1500
Dates      West     600       700       1300
Elderberries North   500       600       1100
Figs       South    400       500       900
Grapes     East     300       400       700
Honeydew   West     200       300       500

✓All #REF! errors were caused by deleted or moved cells referenced in formulas.

✓Using IFERROR helps display friendly messages instead of error codes.

✓Correcting cell references restores accurate total sales calculations.

Bonus Challenge

Create a conditional formatting rule to highlight any Total Sales cells showing 'Check data' or errors.

Show Hint

Use a formula-based rule with ISERROR or check for the text 'Check data' to apply a red fill color.