Formatting Data & Conditional Formatting in Excel
Complete Guide for Delhi Police Computer Section
1. What is Formatting in Excel?
In Excel, data formatting means making your sheet look clean, readable, and professional — just like how a soldier's uniform represents discipline 💪
In exams and real-world work, good formatting means clarity + confidence.
Formatting means changing how data looks — not what it contains:
Examples:
- Changing font style, size, or color
- Adjusting cell borders or shading
- Aligning text (left, right, center)
- Setting number formats (₹, %, dates, decimals)
Shortcut Tip:
Press Ctrl + 1 to open the Format Cells dialog box instantly!
2. Basic Data Formatting Tools
| Formatting Option | Shortcut | Description |
|---|---|---|
| Bold Text | Ctrl + B |
Highlight headings |
| Italic Text | Ctrl + I |
Add emphasis |
| Underline | Ctrl + U |
Underline key data |
| Cell Borders | Alt + H + B |
Add gridlines |
| Font Color | Alt + H + F + C |
Change text color |
| Fill Color | Alt + H + H |
Color the background |
| Merge Cells | Alt + H + M + M |
Combine cells for a title |
| Wrap Text | Alt + H + W |
Show long text in a single cell |
Pro Tip: Use formatting to highlight patterns, not to make your sheet look like a rainbow 🌈
3. Conditional Formatting
Conditional Formatting automatically changes the appearance of cells based on conditions (rules).
Example:
If a student's marks < 33 → Cell turns red.
If marks ≥ 80 → Cell turns green.
Steps to Apply Conditional Formatting:
Select range of cells
Go to Home → Conditional Formatting
Choose rule type (Highlight, Data Bars, Color Scales, Icon Sets)
Set your condition and style
Click OK and Excel does the rest!
4. Previous Year Delhi Police Questions
Which shortcut opens the Format Cells dialog box in Excel?
Year: 2020 | Shift: 1
View Answer
Ctrl + 1
Conditional formatting is available under which tab?
Year: 2021 | Shift: 2
View Answer
Home Tab
What is the purpose of conditional formatting?
Year: 2020 | Shift: 2
View Answer
To automatically format cells based on certain conditions
What does the "Wrap Text" feature do?
Year: 2022 | Shift: 1
View Answer
Displays long text within one cell on multiple lines
Which conditional formatting feature shows bars inside cells to represent values?
Year: 2021 | Shift: 1
View Answer
Data Bars
Which of the following is NOT a conditional formatting option?
Options: (a) Icon Sets (b) Data Bars (c) Border Style (d) Color Scales
Year: 2022 | Shift: 2
View Answer
Border Style
To highlight duplicate entries, which option is used?
Year: 2021 | Shift: 2
View Answer
Conditional Formatting → Highlight Cells Rules → Duplicate Values
5. Smart Excel Tricks
Trick 1: Combine Alt + H + L to open Conditional Formatting instantly
Trick 2: Use Color Scales to compare marks, sales, or ranks visually
Trick 3: To copy only formatting (not values), use the Format Painter (shortcut: Alt + H + F + P)
Trick 4: To clear formatting, select cells → Alt + H + E + F
Design Tip: Mix text color and cell fill smartly — it makes data readable, not flashy
6. Practice Assignment
Task: Format this dataset
| Student | Marks |
|---|---|
| A | 90 |
| B | 30 |
| C | 75 |
| D | 50 |
Make column headings bold & center aligned
Apply conditional formatting:
- Marks < 33 → Red fill
- Marks ≥ 80 → Green fill
Add border and wrap text
Save your file as Formatting_Practice.xlsx
7. Quick Recap
Formatting: Improves look & readability without changing data
Conditional Formatting: Applies rules automatically based on cell values
Key Shortcuts: Ctrl+1 (Format Cells), Alt+H+L (Conditional Formatting)
Important: Formatting doesn't change data, it enhances presentation
Exam Focus: Formatting shortcuts and conditional formatting rules are frequently asked in Delhi Police exams
8. Practice Questions
Q1. What is the difference between regular formatting and conditional formatting?
View Answer
Regular formatting is applied manually and stays the same, while conditional formatting changes automatically based on cell values or conditions
Q2. How would you highlight all cells with values greater than 100 in a range?
View Answer
Select range → Home → Conditional Formatting → Highlight Cells Rules → Greater Than → Enter 100 → Choose formatting style
Q3. What is the purpose of the Format Painter tool?
View Answer
Format Painter copies formatting from one cell/range and applies it to another, saving time when you want consistent formatting
Q4. When would you use "Merge and Center" versus "Center Across Selection"?
View Answer
Merge and Center combines cells into one, while Center Across Selection centers text across multiple cells without merging them (better for data integrity)
Q5. How can you quickly remove all formatting from a selected range?
View Answer
Select the range → Home tab → Editing group → Clear → Clear Formats, or use shortcut Alt + H + E + F
You've completed Excel Formatting & Conditional Formatting!
Courage Tip: Remember that good formatting makes your data speak clearly. Practice the shortcuts like Ctrl+1 for quick formatting access and use conditional formatting to automatically highlight important patterns in your data. These skills are essential for both exams and professional Excel use.
Master Excel Formatting for Delhi Police Exam!
Join Courage Library for comprehensive study materials and expert guidance.
Be a Couragian!