Chapter 3: Formatting Worksheets

Formatting Worksheets.

This chapter is all about making your data look professional, beautiful, and easy to read. Good formatting helps people understand your numbers quickly — whether it’s a sales report, budget, or school mark sheet.

I’ll explain everything very slowly and in detail, like a patient teacher sitting next to you at your computer. We’ll use lots of real-life examples, step-by-step instructions, and clear tips. Let’s make your worksheets shine!

1. Cell Formatting (Font, Alignment, Borders, Colors)

All basic formatting tools are in the Home tab of the Ribbon.

A. Font Formatting (change typeface, size, style)

• Select cells → go to Home tab → Font group
• Change Font (e.g., Calibri → Arial → Times New Roman)
• Font Size (10 → 11 → 12 → 14 → 16…)
• Bold (B), Italic (I), Underline (U)
• Font Color (A icon with color bar)

Example: Make headers stand out

1. Select cells A1:F1 (your table headers)
2. Font → Arial Black, Size 14, Bold, Font Color Dark Blue

B. Alignment (how text/numbers sit inside cells)

• Home tab → Alignment group
• Horizontal: Left, Center, Right, Justify, Fill
• Vertical: Top, Middle, Bottom
• Wrap Text (text goes to next line inside cell)
• Merge & Center (combine cells into one big title cell)

Example:

• Select A1:F1 → Click Merge & Center → type “Monthly Sales Report 2026”
• Select column B (prices) → Right Align
• Select column A (item names) → Left Align, Middle Vertical

C. Borders (lines around cells)

• Home tab → Borders dropdown (square icon)
• Choose: All Borders, Outside Borders, Thick Box Border, Bottom Border…

Example:

• Select your entire data range (A1:F10)
• Borders → All Borders (thin black lines)
• Then select header row A1:F1 → Borders → Thick Bottom Border (makes header stand out)

D. Fill Colors (background color of cells)

• Home tab → Fill Color bucket icon
• Choose any color (light gray for headers, green for profit cells, red for loss…)

Example:

• Header row → Light Blue fill
• Alternate rows → Light Gray fill (makes table easier to read)

Here’s what the Home tab formatting groups look like on your screen:

[Imagine or look at your Excel: Home tab has Font | Alignment | Number | Styles | Cells groups with all these icons]

2. Number Formatting (Currency, Percentage, Date)

Numbers look plain by default. Number formatting makes them meaningful.

How to apply:

• Select cells → Home tab → Number group
• Or right-click → Format Cells (Ctrl + 1) → Number tab

Common formats:

Steps for Indian Rupee (₹):

1. Select price column (e.g., B2:B10)
2. Home → Number → Accounting number format (or Currency)
3. Click the ₹ symbol dropdown → choose INR (₹)

Percentage example:

• Cell E2 → 0.18
• Select → Home → Percentage button → becomes 18%

Custom date:

• Right-click → Format Cells → Date → choose 14-Mar-12 or dd-mmm-yy

3. Conditional Formatting Basics

This is magic! Excel automatically colors cells based on rules (e.g., highlight high sales in green, low in red).

Where: Home tab → Styles group → Conditional Formatting

Popular options:

• Highlight Cells Rules → Greater Than, Less Than, Between, Equal To, Text that Contains…
• Top/Bottom Rules → Top 10 Items, Bottom 10%, Above Average…
• Color Scales → Green-Yellow-Red gradient
• Data Bars → bars inside cells showing value size
• Icon Sets → arrows, flags, stars

Real example – Sales performance

1. Select your sales column (e.g., D2:D10 – Total column)
2. Conditional Formatting → Color Scales → choose Green – Yellow – Red Color Scale → Highest sales = dark green, lowest = dark red
3. Another rule: Conditional Formatting → Highlight Cells Rules → Greater Than → type 10000 → Format → Light Green Fill
4. Add rule: Less Than → 2000 → Light Red Fill

Another example – Due Dates

• Select date column → Conditional Formatting → Highlight Cells Rules → A Date Occurring → This Week → Yellow fill

4. Row/Column Height & Width, Hiding/Unhiding

Adjust width/height:

• Hover between column letters (A | B) → double-click → auto-fit to content
• Or drag the line to resize manually
• Select rows/columns → right-click → Column Width or Row Height

AutoFit:

• Select entire sheet (Ctrl + A) → double-click any column border → all columns auto-fit!

Hide/Unhide:

• Right-click column letter → Hide
• To unhide: Select columns on both sides → right-click → Unhide
• Same for rows

Example: Hide sensitive salary column → right-click column E → Hide → (invisible to others)

5. Themes and Cell Styles

Themes (change colors/fonts for whole workbook)

• Page Layout tab → Themes group → Themes dropdown
• Choose a theme like Office, Facet, Ion — changes all colors, fonts, effects at once

Cell Styles (pre-made professional looks)

• Home tab → Styles group → Cell Styles
• Categories:
  • Good, Bad, Neutral (green, red, yellow)
  • Titles and Headings
  • Themed Cell Styles (match your theme)
  • Number Format styles

Example:

1. Select header row → Cell Styles → Heading 1 (big, bold, colored)
2. Select total row → Cell Styles → Total (bold underline)
3. Select profit cells → Cell Styles → Accent1 (nice color)

Here’s how the Cell Styles gallery looks:

(You’ll see a big dropdown with colorful boxes: Heading 1, Heading 2, Good, Bad, Neutral, etc.)

Quick Summary Table – Chapter 3

Homework / Practice (Do This Today!)

Use your Practice_Chapter1.xlsx file (or create a new one with the sales table from Chapter 2):

1. Make A1:F1 headers bold, size 14, dark blue font, light blue fill, merge A1:F1 for title “Monthly Sales Report – Jan 2026”
2. Format price column (B) as Accounting with ₹ symbol
3. Quantity column (C) → Number with 0 decimal places
4. Total column (D) → Comma Style
5. GST % column (E) → Percentage format
6. Apply Green-Yellow-Red Color Scale to Total column (D)
7. Highlight totals > ₹10,000 in green fill
8. Auto-fit all columns
9. Apply a nice Theme from Page Layout (try “Facet” or “Ion”)
10. Use Cell Styles → Heading 1 for title, Total for bottom row

Your sheet should now look clean, colorful, and professional!