Try the example, then check the result.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
The fictional workbook looks as though it contains four amounts, yet its raw SUM shows $55. The first three amounts are text values. Excel's SUM ignores text values in referenced cells, so the correct total of $300 is missing from the raw result. Download the practice workbook and answer check. Work in a copy.
What is in the workbook
| Order ID | Raw amount | Stored type | Converted amount |
|---|---|---|---|
0071 | 125 | Text | $125 |
0072 | 80 | Text | $80 |
0073 | 40 | Text | $40 |
0074 | 55 | Number | $55 |
Swipe or scroll sideways to see every table column →
The four IDs are identifiers. Their leading zeros matter. Do not convert column A into numbers while fixing the amounts.
1. Verify the symptom
Open the Orders sheet. Inspect B2:B5 and the raw total at B7, which contains =SUM(B2:B5). Its saved result is 55. You may see a green error triangle next to the first three values; Excel's error-checking settings affect that indicator, so also inspect the actual formula and stored types. A visible digit string alone does not prove that a cell is numeric.
2. Create a numeric helper column
In Excel for the web, enter =VALUE(B2) in C2, then fill the formula through C4. Enter =B5 in C5 because B5 is already a number. Microsoft's conversion instructions document VALUE for Excel for the web. On desktop Excel, the error menu may also offer Convert to Number for selected text amounts. Confirm the selection contains amounts, not order IDs, before choosing it.
The expected helper results are 125, 80, 40, 55. If VALUE returns an error, inspect that source cell for currency symbols, stray characters, or a locale-specific separator before converting other rows.
3. Reconcile the total
Enter =SUM(C2:C5) in C7. The answer is $300: 125 + 80 + 40 + 55. Compare B7 ($55) and C7 ($300). Check A2:A5 still display 0071 through 0074.
If you need to replace the original amount strings in another workbook, copy the converted results and paste Values into a copy of the amount column. Keep the original export until the total is reconciled. Do not paste into the ID column.
Common mistakes
- Converting all digit strings:
0071is an order code. As a numeric value it becomes71unless specially formatted, and its underlying value changes. - Formatting text as Currency without converting it: a number format changes appearance, not the stored type. Check the helper values and total.
- Assuming every wrong SUM has this cause: missing range rows, hidden errors, and manual calculation are different diagnoses. The downloadable file demonstrates numeric text specifically.
Sources and visual evidence
Microsoft documents text-to-number conversion, SUM's handling of text, and why IDs may need text formatting. Checked 13 October 2026. Companion video panels are illustrative diagrams made from this fictional workbook. The source file and independent arithmetic were checked locally; the conversion workflow has not yet been reproduced in a fresh signed-in Excel session for this episode.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.