Why Formatting Matters
Good formatting makes data readable, trustworthy, and professional. An unformatted spreadsheet with raw numbers is hard to interpret — the same data properly formatted communicates instantly.
Bad: 75000 vs Good: ₹75,000.00
Bad: 44927 vs Good: 15-Mar-2026
Bad: 0.0856 vs Good: 8.6%
Formatting never changes the underlying value — only how it's displayed.
Number Formatting
Opening the Format Cells Dialog
Press Ctrl + 1 or right-click → Format Cells. The Number tab is your control centre.
Built-in Number Formats
| Category | Display | Format Code |
|---|---|---|
| General | 75000 | General |
| Number | 75,000.00 | #,##0.00 |
| Currency | ₹75,000.00 | ₹#,##0.00 |
| Accounting | ₹ 75,000.00 | _₹* #,##0.00_ |
| Percentage | 8.56% | 0.00% |
| Scientific | 7.50E+04 | 0.00E+00 |
| Fraction | 3/4 | # ?/? |
Custom Number Formats
Press Ctrl + 1 → Number → Custom → type your format code.
Format code structure: positive;negative;zero;text
#,##0.00 → 75,000.00 (positives)
#,##0.00;[Red](#,##0.00) → positive normal, negative in red brackets
#,##0.00;-#,##0.00;"Zero" → custom zero display
₹ #,##0 → ₹ 75,000
0.0"x" → 3.5x
+0;-0;0 → +75 or -30 or 0
Wildcards:
0— digit, shows leading/trailing zeros#— digit, suppresses unnecessary zeros,— thousands separator (or scale by 1000 if at end:0,= thousands).— decimal point%— multiplies by 100 and adds %
Quick Format Shortcuts
| Format | Shortcut |
|---|---|
| General | Ctrl + Shift + ~ |
| Number (2 decimals) | Ctrl + Shift + 1 |
| Time | Ctrl + Shift + 2 |
| Date | Ctrl + Shift + 3 |
| Currency | Ctrl + Shift + 4 |
| Percentage | Ctrl + Shift + 5 |
| Scientific | Ctrl + Shift + 6 |
Date and Time Formatting
Excel stores dates as integers (number of days since 1 Jan 1900) and times as decimal fractions. This means you can do arithmetic on them:
=TODAY() → 45834 (the underlying number)
Formatted as Date → 27-Jun-2026
Date Format Codes
| Code | Displays |
|---|---|
dd/mm/yyyy | 27/06/2026 |
d-mmm-yy | 27-Jun-26 |
dddd, d mmmm yyyy | Friday, 27 June 2026 |
mmm-yy | Jun-26 |
hh:mm AM/PM | 02:30 PM |
dd/mm/yyyy hh:mm | 27/06/2026 14:30 |
Enter a date: type 27/6/2026 or 27-Jun-2026 → Excel auto-recognises it.
Watch out: Excel's date recognition depends on your regional settings. In regions using MM/DD/YYYY,
1/6/2026means January 6 not June 1. Always verify after entry.
Text Formatting
Font Formatting (Home Tab → Font Group)
| Setting | Description |
|---|---|
| Font | Typeface (Calibri, Arial, etc.) |
| Size | Point size (10, 11, 12...) |
Bold (Ctrl+B) | Emphasis for headers and key values |
Italic (Ctrl+I) | Secondary emphasis |
Underline (Ctrl+U) | Under text; double underline for totals |
| Font Color | Text color |
| Fill Color | Cell background (highlight) |
Alignment (Home Tab → Alignment Group)
| Setting | Options |
|---|---|
| Horizontal | Left, Center, Right, Justify, Fill |
| Vertical | Top, Middle, Bottom |
| Wrap Text | Text wraps within the cell (row height increases) |
| Merge & Center | Combines cells and centers content |
| Indent | Increases/decreases text indentation |
| Text Angle | Rotates text (useful for narrow column headers) |
Wrap Text Example
Long text like "Total Annual Compensation" can be wrapped in a narrow column:
- Select the cell
- Home → Alignment → Wrap Text (or
Alt + H + W)
The row height adjusts automatically to show all the text.
Borders and Cell Styles
Borders
Add borders to separate sections and improve readability:
- Select range
- Home → Font → Borders dropdown
- Choose: All Borders, Outside Borders, Thick Box Border, etc.
Format Cells dialog (Ctrl+1 → Border tab): full control over border style, colour, and which edges.
Cell Styles (Home → Styles)
Pre-built combinations of formatting:
- Header styles — for column/row headers
- Number styles — Good, Bad, Neutral
- Title, Heading 1-4 — for sheet titles and section headings
- Total — typically bold with top/bottom borders
Format Painter
Copy formatting from one range to another:
- Select the formatted cell
- Home → Clipboard → Format Painter (paint bucket icon) or
Ctrl + Shift + CthenCtrl + Shift + V(paste formatting only) - Click/drag to apply to target cells
Double-click Format Painter to apply to multiple areas; press Escape when done.
Column Width and Row Height
Adjust Manually
- Drag the column letter divider (between A and B headers) to resize
- Drag the row number divider to resize rows
AutoFit
- Double-click the column divider → AutoFit to content width
- Select all columns (Ctrl+A) → right-click column header → Column Width → type a value
| Action | Shortcut |
|---|---|
| AutoFit column width | Double-click column divider |
| AutoFit row height | Double-click row divider |
| Set exact column width | Right-click header → Column Width |
Conditional Formatting (Overview)
Conditional formatting changes cell appearance based on values — covered in detail in Chapter 14. Quick preview:
Home → Conditional Formatting → Highlight Cell Rules → Greater Than...
→ Value: 80000, Format: Green Fill
All cells with salary > 80000 turn green automatically.
Themes and Colours
Page Layout → Themes applies a coordinated set of fonts, colours, and effects across the whole workbook. Pick a professional theme (Office, Slate, Gallery) for consistent styling.
Colour Palettes:
- Theme colours change if you switch themes
- Standard colours are fixed
Use theme colours for professional documents — if the client needs to recolour, they just change the theme.
Paste Special — The Power Paste
Ctrl + V pastes everything (values, formulas, formatting). Ctrl + Alt + V (Paste Special) gives options:
| Paste Option | What It Pastes |
|---|---|
| Values | Just the values (no formulas, no format) — most useful! |
| Formats | Just the formatting |
| Formulas | Just the formulas |
| Values & Number Formatting | Values + number format only |
| Column Widths | Copy column width to destination |
| Transpose | Rotate data — rows become columns |
Common workflow: Calculate values with formulas, then copy → Paste Special → Values only → original formulas replaced with static values.
Operations in Paste Special
You can paste with arithmetic:
1. Type 1.10 in an empty cell (10% raise multiplier)
2. Copy it (Ctrl+C)
3. Select the salary column
4. Paste Special → Values → Multiply
→ All salaries increase by 10% in place
Data Entry Best Practices
Use Tables for Structured Data
Always convert data ranges to Excel Tables (Ctrl + T):
- Auto-expanding — new rows inherit formatting and formulas
- Structured references (
Table1[Salary]) instead ofA:A - Built-in filtering on every column
- Easy to reference in formulas and pivot tables
Consistent Data in Each Column
Good: one type of data per column
A B C
Name Dept Salary
Priya Finance 75000
Raj Tech 92000
Bad: mixed data in one column
A
Name: Priya Sharma
Dept=Finance
75,000/-
Don't Merge Cells in Data Ranges
Merged cells break sorting, filtering, and formulas. Use them only for headings/titles — never inside data tables.
Avoid Blank Rows and Columns Inside Data
A blank row or column splits Excel's automatic range detection. Keep data contiguous.
Practical Examples
Example 1: Employee Payroll Sheet with Formatting
Row 1 (header): Bold, blue fill (#4472C4), white text, centered
Row 2 onwards: Alternating white/light blue rows
Column C (Salary): Currency format ₹#,##0
Column D (Tax): Percentage format 0.00%
Column E (Net Pay): Currency format ₹#,##0
Row last (Total): Bold, thick top border
Example 2: Custom Date Format for Reports
Cell A1 = TODAY()
Format as: "Report Date: dddd, d mmmm yyyy"
Displays: "Report Date: Friday, 27 June 2026"
Example 3: Quick Colour-Coding
Highlight values:
- Select salary column
Ctrl + 1→ Fill → Light green- Or: Conditional Formatting → Color Scales → Green-Yellow-Red (green = high, red = low — instant heat map)
Common Mistakes
1. Entering numbers as text
Type '75000 (with an apostrophe) → stored as text
The cell shows 75000 but won't sum correctly
Fix: remove apostrophe or use Data → Text to Columns
2. Using merge cells in data tables
Merged cells prevent sorting, filtering, and pivot tables from working correctly. Use "Center Across Selection" instead (Format Cells → Alignment → Horizontal: Center Across Selection).
3. Hardcoding formatted text instead of using number format
Bad: Type "₹75,000" as text → you can't calculate with it
Good: Enter 75000, format as ₹#,##0 → still a number, looks formatted
4. Using different date formats in the same column
27/6/2026
Jun-26
2026-06-27 ← Excel may treat these inconsistently
Pick one format and apply it to the entire column.
Practice Exercises
- Enter a salary table (name, department, salary, tax rate) for 5 employees. Format: salary as ₹#,##0.00, tax rate as percentage.
- Apply conditional formatting to highlight salaries above ₹80,000 in green and below ₹65,000 in red.
- Use Paste Special → Transpose to rotate a horizontal list of department names into a vertical list.
- Create a custom number format that shows numbers in thousands (e.g., 75000 shows as "75K").
- Format a date column to display in the format: "Friday, 27 June 2026".
Summary
In this chapter you learned:
- Formatting only changes how data looks — the underlying value stays the same
Ctrl + 1opens Format Cells for full formatting control- Number format codes:
0,#,,,.,%— combine to create any format - Dates are stored as numbers; format codes like
dd/mm/yyyycontrol display - Key formatting: font, fill, borders, alignment, wrap text
- Format Painter copies formatting from one cell to another
- Paste Special (
Ctrl + Alt + V) — values, formats, transpose, or arithmetic - Don't merge cells in data ranges — use "Center Across Selection" instead
- Convert data to Excel Tables (
Ctrl + T) for automatic formatting and filtering - Keep one data type per column; avoid blank rows/columns in data ranges
Next up: Formulas & Functions Basics — write formulas to calculate, summarise, and analyse your data.