Number formatting
Formatting changes how a value looks, never the value itself. A cell showing 18% contains 0.18; a cell showing ₹1,235 might contain 1234.567. Formulas always use the real value.
The built-in formats
Select cells, then choose a format on the Home tab, or press Ctrl+1 for the full Format Cells dialog.
| Format | The value 1234.567 shows as | Notes |
|---|---|---|
| General | 1234.567 | the default: shows what you typed |
| Number (2 decimals, with separator) | 1,234.57 | rounded on screen only |
| Currency | ₹1,234.57 | the symbol sits next to the number |
| Accounting | ₹ 1,234.57 | symbols line up at the cell's left edge |
| Percentage (2 decimals) | 123456.70% | multiplies by 100 for display; the % button uses no decimals |
| Scientific | 1.23E+03 | for very large or small numbers |
| Text | 1234.567 | treated as text: formulas stop calculating with it |
Percentages
Type 0.18 and apply Percentage to see 18%. Or type 18% directly, and Excel stores 0.18 and formats it for you. Either way, a formula like =B2*(1+C2) uses 0.18.
A common trap: applying Percentage to a cell that already contains 18 shows 1800%. The format multiplies by 100; it doesn't add a percent sign to the number you typed.
Dates are numbers
Excel stores a date as the number of days since 1 January 1900. Format 45474 as a date, and it shows 01-07-2024; format a date as General, and you see its number again.
=DATE(2024, 7, 1) → 45474, shown as a date
=DATE(2024, 7, 1) + 30 → 45504, which is 31 July 2024Because dates are numbers, you can subtract them to count days, and add to them to move forward. The dates lesson covers this in detail.
| Date format code | Shows 45474 as |
|---|---|
dd-mm-yyyy | 01-07-2024 |
d mmm yyyy | 1 Jul 2024 |
dddd, d mmmm | Monday, 1 July |
mmm-yy | Jul-24 |
Custom formats
In Format Cells → Custom, you can write your own format codes:
| Code | 1234.5 shows as | Meaning |
|---|---|---|
0 | 1235 | a digit, always shown |
0.00 | 1234.50 | two decimal places |
#,##0 | 1,235 | thousands separators, no decimals |
"₹"#,##0.00 | ₹1,234.50 | text in quotes is shown as is |
0.0,"K" | 1.2K | each comma at the end divides by 1,000 |
00000 | 01235 | padded to five digits with zeros |
A custom format can have up to four sections separated by semicolons: positive; negative; zero; text.
#,##0;[Red]-#,##0;"–"This shows positive numbers normally, negatives in red, and zero as a dash.
Leading zeros: text or format?
PIN codes, phone numbers and product codes like 00731 lose their zeros when Excel reads them as numbers. Two fixes:
- format the cells as Text before typing (or type an apostrophe first:
'00731), which keeps exactly what you typed; - or keep numbers and use a custom format like
00000, which only pads them on screen.
Use Text for codes you'll never do maths with.
Conditional formatting
Home → Conditional Formatting changes a cell's look based on its value:
- Highlight Cells Rules: greater than, less than, duplicates, text that contains...
- Data Bars: a bar inside each cell, proportional to its value.
- Colour Scales: a gradient from low to high.
- Icon Sets: arrows or traffic lights.
For example, select a column of scores and choose Highlight Cells Rules → Less Than → 50 to colour every failing score red. The rule updates automatically when the numbers change.