24 min read
Understanding Excel Data Types
Text vs numbers — why 0123 differs from 123. Leading zeros, alignment clues, and why SUM ignores text.
Why Data Types Matter for Budgets
Every cell holds a data type — the kind of value Excel stores, not just what it looks like on screen.
James Okonkwo at Northgate Academy imported a supplier code 0123 into the budget workbook. Excel left-aligned it like a label. SUM ignored it. The quarterly total was short by thousands.
Open NorthgateBudget.xlsx on the Budgets sheet. You will compare how Excel treats labels, amounts, and values that *look* numeric but are stored as text.
Three Common Data Types
Every cell on Budgets holds one of these types:
| Type | What you type | Used for |
|---|---|---|
| Text | Department, INV-0042, 0123 (when forced) | Labels, codes, notes |
| Number | 12500, 0.05, -300 | Budget amounts, rates, counts |
| Date/Time | 1 Sep 2025 | Deadlines, expense dates |
Excel decides the type from how you enter the value and from cell format — not from what it looks like on screen.
Text vs Number on the Budget Grid
English Q1 budget vs a supplier code
| Number (12500) | Text ("0123") |
|---|---|
| Right-aligned in the cell | Left-aligned (default for text) |
| =SUM(B2:E2) includes it | SUM treats it as zero |
| Sorts 100 before 2000 | Sorts "100" after "20" (alphabetically) |
| Currency format works normally | Currency format may not apply as expected |
Why 0123 Is Not the Same as 123
Type these side by side on a spare area of the Budgets sheet (for example columns M and N):
In M2, type 0123 and press Enter. In N2, type 123 and press Enter. Click M2. Look at the formula bar — Excel often shows 123 even though you typed a leading zero.
Format M2 as Text first (*Home → Number → Text*), then type 0123 again. The zero stays. The cell is left-aligned.
Expected result: Without Text format, Excel converts 0123 to the number 123 and drops the leading zero. With Text format, 0123 stays as text — useful for invoice codes, not for currency.
Cell B2 holds 12500 for English Q1. Excel stores it as a number. When you write =SUM(B2:E2), all four quarters add correctly.
Numbers Stored as Text
James Okonkwo pasted Q1 figures from an email. Some amounts show a green triangle and left alignment. =SUM(B2:E2) returns less than expected because one quarter is text.
Fix: select the column → Data → Text to Columns → Finish (or retype as numbers). Or click the warning → Convert to Number.
Quick check: click a budget cell. If the formula bar shows a number without quotes and the value is right-aligned, Excel treats it as numeric.
Force Text with a Leading Apostrophe
Type '0123 (apostrophe before the digits). Excel stores 0123 as text and hides the apostrophe in the cell.
Use this for purchase order codes on the Expenses sheet, room numbers like 04, and IDs that must keep leading zeros.
Do not use apostrophe tricks on budget amounts in columns B:E — those must stay numeric for =SUM(B2:E2) and similar formulas.
Excel also offers Linked data types (Stocks, Geography) in Microsoft 365. The Northgate budget course uses classic text / number / date types — the ones that affect SUM, AVERAGE, and sorting on NorthgateBudget.xlsx.
Practice
Swipe or use the arrows to move between questions.
1 / 2
More lessons in Microsoft Excel · Next: Dates, Times, and Custom Formats · Previous: Data Validation