Ravindra BagaleCourses & study guides Track your progress
Easy challenge

Easy: Numbers stored as text

By · Free ·

Absolute refsNumbers as textLeading spacesFilter left onChart range% vs decimalDuplicate headerROUND vs displayFreeze panesPaste valuesVLOOKUP & textVLOOKUP col indexHLOOKUP TRUESUMIF spacesCOUNTIFS ORIF nest orderDate serialINDEX MATCHWildcard COUNTIFMixed currencyCircular & datesCircular interest3D sum renameNamed range staleCF vs IFPivot staleHard-coded NPVValidation movedPQ step orderTyped-over formulaSUM wrong anchorsFiscal year Apr–MarFind all bugsInterview oral

Amounts look like numbers but are stored as text. SUM(D5:D12) returns 0 in LibreOffice/Excel. Convert with VALUE, Paste Special → Add, or Text to Columns.

About this data: Made-up expenses for Meera Desai (Pune clinic) and James Okonkwo (Lagos clinic). INR ₹. Not real clinic spend.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: CSV imports and CRM exports often leave amounts as text. Totals silently fail.

What: Eight expense rows; Amount_INR is text in the broken file. SUM → 0. Fixed file has real numbers; SUM → 15950 (LibreOffice verified).

How:

  1. Download and open sheet Expenses.
  2. Notice left-aligned amounts and SUM = 0.
  3. Convert D5:D12 to numbers (VALUE, *1, or Paste Special Add).
  4. Confirm Total shows 15950.

Ravindra Bagale’s Tip

Green triangle / left align often means text. Do not retype every cell — convert the range once.

Bugs explained (with mini examples)

Bug 1 — Text amounts

Broken: cells hold "4500" as text → SUM skips them → 0. Fix: =VALUE(D5) fill down, or select → Data → Text to Columns → Finish, or Paste Special → Add 0.

Bug 2 — Blind trust in the total cell

A formula can be correct while the inputs are wrong. Always check data types first.

Practice tasks (12)

  1. Task 1. Select D5:D12 and say whether values are text or numbers.
    Hint: Look at alignment / green marks.
  2. Task 2. Why is Total 0 in the broken file?
    Hint: SUM ignores numeric text in many cases.
  3. Task 3. Convert one amount with =VALUE(D5).
    Hint: Should become 4500.
  4. Task 4. Convert the whole column without retyping.
    Hint: Paste Special Add or Text to Columns.
  5. Task 5. Re-check SUM — target 15950.
    Hint: LibreOffice verified.
  6. Task 6. Name two ways text numbers enter a sheet.
    Hint: CSV import; apostrophe prefix.
  7. Task 7. What does ISTEXT(D5) return on a broken amount?
    Hint: TRUE.
  8. Task 8. After fix, does currency format still work?
    Hint: Yes on real numbers.
  9. Task 9. Write one interview sentence about this bug.
    Hint: “Totals were zero because amounts were text.”
  10. Task 10. Add a ninth expense 500 as text — show SUM stays wrong until convert.
    Hint: Repeat the lesson.
  11. Task 11. Use =SUMPRODUCT(--D5:D12) as a quick coerce check.
    Hint: Double unary coerces text.
  12. Task 12. Bonus: format Amount as ₹.
    Hint: Home → Number → Currency.

In-browser formula check

Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Convert D5:D12 to numbers.
  2. Keep =SUM(D5:D12).
  3. Expected total: 15950. LibreOffice: broken 0, fixed 15950.

Quick quiz

Q1. SUM of a range of numeric text often returns:
Q2. VALUE("4500") returns:
Q3. Left-aligned “numbers” usually mean:
Q4. Paste Special → Add with 0 is used to:
Q5. Verified fixed total in this workbook: