1.1Data types & cell formatting
The essentials in 30 seconds
- Three types, three behaviors: a number calculates (right-aligned), text does not calculate (left-aligned), a date/time is a disguised number (a day number) — the type decides the possible calculations, sorting and formats.
- The format dresses, it changes nothing: 0.25 displayed as «25%» is still 0.25 in calculations; rounding the display to 2 decimals does not change the real value — hence totals that «seem» wrong to the eye.
- Formatting without distorting: headers in bold + fill, zero merged cells in data (sorting and formulas break otherwise), color reserved for useful information.
Running mini-case — Wassim, an accountant in Algiers
Wassim, an accountant in Algiers, takes over a client's cash tracking: amounts were entered with units («12,500 DA») and totals refuse to calculate — everything is text. He cleans the column (plain numbers, DA Currency format for display), checks the right alignment, then applies Percentage format to the margin column. The same file, with no retyping: the SUMs start answering, and the client finally sees Dinars where needed.
This same case returns in the course, the diagram, the script and the 48h challenge — and continues in 1.2, 1.3, 1.4 and 1.5.
1. Three fundamental types
Excel handles three main types: number (calculable, right-aligned), text (left-aligned, not calculable) and date/time (a disguised number: internally, a date is a day number). The type drives the possible calculations, sorting and formatting.
Wassim's first reflex when facing a column that «won't calculate»: look at the alignment. Left everywhere? It's text — it must be cleaned before summing, not forced with a formula.
2. The value ≠ its display
A format (Number, Currency, Percentage, Date…) changes the display, not the real value. 0.25 formatted as percentage displays «25%» but is still worth 0.25 in calculations. Likewise, rounding the display to 2 decimals does not modify the underlying value — hence totals that «seem» wrong to the eye.
The rule fits in one sentence: you format to read, never to change the value. To change the value you need a function (ROUND, seen later), not a format.
3. Formatting without distorting
Formatting (bold, borders, colors, merging) serves readability. Best practice: format the headers, avoid merged cells in data areas (they hinder sorting and formulas), and reserve color for useful information, not decoration.
Wassim applies the grid to every client file: visible headers, never-merged data, readable currency — the rest never leaves for the client.
🔎 Concrete example — 🔎 Concrete example — The percentage that «lies»
Wassim's margin displays «25%»; what is it really worth?
Real value : 0.25
Format : Percentage → displays «25%»
=A1*200 → 50 (because 0.25 × 200) ✅ not 25×200Reading: the format dresses the value; the calculation uses the real value (0.25).
Lesson: to understand a result, look at the real value, not just the display.
Section vocabulary
- Data type
- Number, text or date/time; determines what can be calculated, sorted and formatted.
Simple example: Wassim spots text where he expected amounts.
What it is not: a mere choice of color or font.
Reflex: left alignment = suspect text before calculating.
- Cell format
- Dressing of the display (Currency, %, Date…) without changing the real value.
Simple example: 0.25 displayed «25%» but calculated as 0.25.
What it is not: a conversion of the value (like ROUND).
Reflex: format to read, never to fix a calculation.
- Serial date
- Internally, a date is a day number (1 = 01/01/1900) on which you can calculate.
Simple example: The 05/01 invoice displayed «45678» in Number format.
What it is not: a text that looks like a date.
Reflex: weird number instead of a date = apply a Date format.
- Merged cell
- Several cells joined into one; blocks sorting and formulas in data.
Simple example: Wassim's table sorted wrong because of a merged area.
What it is not: a simple thick border around a title.
Reflex: merging forbidden in data, tolerable on an isolated title.
In practice — In practice — Distinguish value and format
- Enter 0.25 in A1, then apply the Percentage format.
- In B1, compute =A1*200 and check the result (50, not 5000).
- Enter a date and change its format (short, long) without changing the value.
- Format a header row (bold + fill) without merging any data cells.
Ready-to-say script — explaining a «wrong» total (30 s)
« Your amounts were entered with DA, so Excel saw them as text: nothing could calculate. I put plain numbers, dressed as Currency for the display. And this 25% is really worth 0.25: the format dresses, the calculation uses the real value. »
Say it while showing the alignment before/after and the formula bar.
Copy-paste template — checking a column's types
1. Select the amount column
2. Look at the alignment: left = TEXT to clean
3. Remove the units (DA, kg) from the cells
4. Format: Number or Currency (DA) for display
5. Test: =SUM(B2:B13) must answer, not 0Wassim's cash in Algiers
As above: «12,500 DA» cleaned to 12500 + DA Currency format — the SUMs answer and the client reads Dinars.
Margins in percentage
0.25 entered then % format: the display shows «25%», =A1*200 gives 50 — the proof that the format does not change the value.
Invoice dates
A date displayed «45678»: apply a short Date format — the serial value does not change, only the display becomes readable again.
Readable headers
Bold + fill on row 1, no merging in the data: the level 1.5 sorting will run without breaking rows.
✓ Best practices
- Check the type before calculating (alignment).
- Format to read, not to change the value.
- Avoid merged cells in data.
✗ Common pitfalls
- Believing that rounding the display changes the value.
- Merging cells then being surprised that sorting breaks.
- Entering numbers with units («12,500 DA») → non-calculable text.
Key takeaways
- Three types: number, text, date/time.
- The format changes the display, not the real value.
- A date is internally a day number.
- Avoid merged cells in data areas.
48h challenge — apply tomorrow
Open a real file, hunt down numbers aligned left (disguised text), clean the entered units, dress them as Currency and check with =SUM(B2:B13).
- I distinguish number, text and date by alignment.
- I format the display without believing I change the value.
- I format without merging the data.
Frequently asked questions
Why does my date display as a number (e.g. 45678)?
Because the cell has a Number format while the value is a serial date. Apply a Date format: the value does not change, only the display becomes readable again.
Is rounding the display enough to round a calculation?
No: the format only rounds what we see; the real value keeps all its decimals. To round the value, you need the ROUND() function.
A cell displays «25%». What is it worth in calculations?