Theory
The total that says zero
Aryan imports last month's sales from the shop's billing system into Excel. He selects the amount column, types =SUM(...), and gets 0. The numbers are right there on screen, but Excel refuses to add them.
Nothing is broken. Excel simply does not think those 'numbers' are numbers, it thinks they are text.
Every cell you fill is silently sorted into a data type, and Excel even shows you which, if you know where to look. Missing this is the number-one cause of 'my formula is wrong' in real offices.
Theory
The postman reads the alignment
Excel is like a postman who automatically sorts every letter you drop in: bills and forms (numbers, dates) into the right tray, personal notes (text) into the left tray. And it shows its decision by which side of the cell the content sits on. A number sitting on the left means the postman filed it as a note, not a bill, which is exactly why it will not add up. Alignment is Excel telling you what it thinks you typed.
At a glance
The three data types
| Type | Default alignment | Usable in math? |
|---|---|---|
| Text (labels) | Left | No |
| Number | Right | Yes |
| Date / Time | Right (serial number) | Yes (date arithmetic) |
Theory
Alignment is the tell
By default, numbers and dates align right; text aligns left. This is not decoration, it is a diagnostic:
- A price showing on the right = a real number, SUM will work.
- A price showing on the left = secretly text (maybe imported that way, or with a leading apostrophe
'450, or a stray space), and SUM will ignore it.
So before trusting any imported column, glance at the alignment. Left-leaning numbers are the saboteurs. (Dates being serial numbers is the same fact you learned in BCA105, which is why date maths works.)
Quiz
Aryan's imported amounts all sit on the LEFT of their cells, and SUM returns 0. What is wrong?
- They are stored as text, not numbers, so SUM ignores them
- The SUM function is broken in his Excel
- He selected the wrong range
- The numbers are too large to add
Show the answer
They are stored as text, not numbers, so SUM ignores them
Left alignment on numeric-looking cells means Excel stored them as text, and SUM only adds real numbers, so it returns 0. The fix: convert them (multiply by 1, use VALUE(), or Text-to-Columns). This 'imported numbers are secretly text' problem is the single most common real-world Excel bug, and alignment is the free clue that spots it.
Think first
The disappearing zero
Aryan types a customer's phone number 09876543210 into a cell, and Excel shows 9876543210, the leading 0 vanished. Why, and how should he store phone numbers and PIN codes?
Show the answer
Excel treated it as a number, and numbers have no meaningful leading zeros (007 is just 7), so it dropped the 0. But a phone number is not a quantity you do maths on, it is a code, i.e. text. Store phone numbers, PIN codes and account numbers as text (format the cells as Text first, or prefix with an apostrophe), so every digit is preserved. The rule: if you never add or multiply it, it is text, not a number.
Watch out
Where marks leak
Not knowing that alignment reveals the data type (right = number/date, left = text). Assuming imported numbers are usable, text-numbers silently break SUM and every calculation. Storing phone numbers or PINs as numbers and losing leading zeros, they are codes (text). And forgetting dates are serial numbers (BCA105), which is why sorting or subtracting dates works. These type gotchas are exactly what a practical Excel exam probes.
Theory
A five-second habit
Before building any report on data you did not type yourself, glance at the columns: are the numbers hugging the right edge? If a numeric column leans left, fix the type before you trust a single formula. This one habit prevents hours of 'why is my total wrong' debugging. Next lesson: making the sheet readable with fonts, alignment and borders, the basic formatting a power user applies fast.
Summary
Key takeaways
- Excel sorts every cell into one of three data types: Text, Number, or Date/Time.
- Alignment reveals the type: numbers and dates align RIGHT, text aligns LEFT (by default).
- A numeric value showing on the LEFT is secretly text and will break SUM and other math.
- Fix text-numbers with VALUE(), multiply by 1, or Text-to-Columns.
- Store codes (phone, PIN, account numbers) as text so leading zeros survive.
- Memory hook: the postman files right (numbers/dates) or left (text), and shows you which.