📎 This article includes 1 downloadable practice file ↓
In this article
The same numbers can look like a mess or like a report your manager trusts. Formatting is how. The most important thing to understand first: formatting changes how a value looks, never the value itself. 0.075 formatted as 7.5% is still 0.075 underneath, and that’s what formulas use.
Format Cells: Ctrl + 1
Select cells and press Ctrl + 1. The Number tab is where most of the work happens:
| Category | 1234.5 shows as | Use for |
|---|---|---|
| Number, 2 decimals, 1000 separator | 1,234.50 | Amounts |
| Currency (₹) | ₹1,234.50 | Invoices, price lists |
| Accounting | ₹ 1,234.50 (symbol lined up at the left) | Financial statements |
| Percentage | 0.075 → 7.5% | Rates, growth |
| Date | 04-10-2026, 4-Oct-26… | Any date |
| Text | Stored exactly as typed | Codes with leading zeros, typed before entering them |
Ribbon shortcuts on Home › Number: ₹ (Accounting), % (Percent), comma style, and the two “.0” buttons to add or remove decimals.
Custom formats
Ctrl+1 › Custom lets you write your own:
| Format code | Value | Shows |
|---|---|---|
000 |
7 | 007 |
0.0,,"M" |
2500000 | 2.5M |
0.0,," L" won’t work for lakhs |
2500000 | Excel’s comma trick divides by thousands only; for lakhs use =A2/100000 or TEXT |
#,##0;[Red]-#,##0 |
-4520 | -4,520 in red |
dd-mmm-yyyy |
a date | 04-Oct-2026 |
dddd |
a date | Sunday |
"Qty: "0 |
12 | Qty: 12 (still the number 12) |
Alignment, wrap and borders
- Wrap Text (Home › Alignment) keeps long text inside the cell on several lines.
- Center Across Selection (Ctrl+1 › Alignment › Horizontal) centres a title over several columns without merging cells. Merged cells break sorting, filtering and copying; avoid them in data.
- Borders: Home › Borders › All Borders for a table, Thick Bottom Border under headers. Less is more.
- Freeze Panes (View › Freeze Panes › Freeze Top Row) keeps headers visible while scrolling.
Copy formatting: Format Painter
Click a nicely formatted cell, click the paintbrush (Home › Format Painter), then click or drag over the target. Double-click the paintbrush to paint many places; press Esc when done.
Formatting vs the TEXT function
You can also format with a formula: =TEXT(A2,"0.00"). The difference matters:
| Ctrl+1 formatting | TEXT function | |
|---|---|---|
| Result | Still a number | Text |
| Can you SUM it? | Yes | No |
| Use it for | Tables and reports | Joining numbers into sentences and labels |
More codes in the TEXT function guide.
A simple house style
- Header row: bold, light fill, bottom border, frozen.
- Amounts: comma style, no decimals unless paise matter.
- Percentages: 1 decimal.
- Dates: one format across the whole file (dd-mmm-yyyy avoids day/month confusion).
- Inputs in a light yellow fill, formulas left white. Readers instantly see what they may change.
Where beginners go wrong
| Mistake | Effect |
|---|---|
| Rounding by removing decimals | The value isn’t rounded; totals can look off by 1. Use ROUND for real rounding |
| Formatting a column as Text after typing numbers | Existing numbers stay numbers; new ones become text. Format first |
| Merging cells in tables | Can’t sort, filter or select columns cleanly |
| Too many colours | Nobody knows what matters; keep colour for meaning |
Practice
Download this lesson’s workbook below. It has an amount, a rate, a date and a code to display in different formats, plus two checks on formatting vs TEXT. Type your answers in the yellow column; the Check column turns green when you’re right, and the Answers sheet shows a working formula for every task.
📎 Practice files for this article
- 📗Lesson 3 practice workbookAmounts, rates, dates and codes to show in different formats, plus the difference between formatting and TEXT.⬇ XLSX · 13 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.