Skip to content
Reading settings
ExcelIntermediate~30 min practiceLesson 29 of 34

Find why a total is wrong

Separate a text amount from a missing row and fix them one at a time.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Diagnose two causes of a wrong SUM, record intermediate results and test a changed input.

Example

One total, two separate faults

Enter Item and Amount in A1:B4: Travel 120, Supplies 45 stored as text and Training 80. Put =SUM(B2:B3) in B6. Supplies is not numeric and Training is outside the formula range; changing the display alone will not fix both faults.

How it works

Start with what you can see, then test each possible cause. Types and range boundaries can both affect a total; change one thing at a time to see which result it explains.

A useful diagnostic note records the cells, original formula, observed result and each correction. It lets someone else repeat your check instead of guessing what changed.

Steps

  1. Save a BeforeDiagnosis copy. Inspect B2:B4 in the formula bar and add the intended amounts yourself to get 245.

  2. Select B6 and read =SUM(B2:B3). Note that it excludes Training in B4, then check the included cells before changing the range.

  3. Enter =ISNUMBER(B3) in a free cell. It should show FALSE. On the working copy, convert Supplies to a number with VALUE or another suitable numeric conversion.

  4. Check B6 again: it should now show 165. The text problem is corrected, but the formula still omits Training.

  5. Change the SUM range to B2:B4 and check 245. On a copy, change Training to 90 and check 255 to see whether the formula responds to the input.

  6. If the result does not update, check the formula’s references and calculation mode. Recalculate and note the exact result before trying another fix.

  7. Write the two causes in DiagnosticLog with intermediate total 165 and final total 245. Restore the original amounts and save the corrected workbook under a new name.

Try it yourself

  1. Create a second flawed total that accidentally includes a summary row as input. Predict the double-counting effect and correct the range on a copy without deleting valid source records.
  2. Explain to a colleague why changing the number display alone was not sufficient, and include both identified causes and the input-change test in the handover note.
Show expected result
  • After converting Supplies but before correcting the range, the total is 165. After including Training, it is 245, and a changed Training value 90 produces 255.
  • The diagnostic record distinguishes a type problem from a source-boundary problem; both corrections are supported by observable input and output evidence.

If something goes wrong

Number formatting does not fix the text amount.
Read the stored entry and convert or re-enter it as a number. Applying Number format may leave the underlying text unchanged.
The final total matches, but you cannot explain the edits.
Return to BeforeDiagnosis and repeat one correction at a time. Log each result so you can distinguish the type fix from the range fix.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. What is the intermediate total after only the text conversion?
2. What additional change produces the intended 245?
3. What is the strongest diagnosis record?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Diagnose this SUM without editing it. Explain the text amount and missing row separately, and predict the intermediate and final totals.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…