Excel Conditional Formatting
Learn how to highlight important values, identify patterns, compare performance, and make Excel reports easier to understand.
1. What Is Conditional Formatting?
Conditional Formatting automatically changes the appearance of cells based on specified conditions. It can change cell colors, font colors, data bars, color scales, and icons according to the values in a worksheet.
For example, a training institute can use Conditional Formatting to:
- Highlight fees greater than ₹50,000.
- Highlight marks below 40 in red.
- Identify duplicate student IDs.
- Compare student performance using color scales.
- Display arrows or traffic-light icons based on performance.
2. Where Is Conditional Formatting in Excel?
Follow these steps to find the Conditional Formatting command.
- Select the cells or range where formatting should be applied.
- Open the Home tab.
- Locate the Styles group.
- Click Conditional Formatting.
- Select a rule type and configure the condition.
Excel Ribbon — Illustrative Visual
Custom instructional illustration, not a literal screenshot of Excel.
3. Common Conditional Formatting Rules
| Rule Type | Purpose | Example |
|---|---|---|
| Highlight Cells Rules | Highlight cells based on values, text, or dates. | Fees greater than 50000. |
| Top/Bottom Rules | Highlight highest or lowest values. | Top 10 scores. |
| Data Bars | Display horizontal bars inside cells. | Compare fees paid. |
| Color Scales | Apply color gradients based on values. | Low marks red, high marks green. |
| Icon Sets | Display icons based on thresholds. | High, medium, and low performance. |
| Duplicate Values | Identify repeated entries. | Duplicate admission IDs. |
| New Rule / Formula | Create a custom condition using a formula. | Highlight pending fees. |
4. Understand Data Bars and Color Scales
Excel-Style Visual Comparison
Illustrative diagram. The exact colors depend on the selected Excel rule.
Data Bars
Data Bars display a horizontal bar inside each cell. Higher values normally produce longer bars, making comparisons easier.
Color Scales
Color Scales apply different colors according to the values in a range. A common three-color scale uses red for lower values, yellow for middle values, and green for higher values.
5. Prepare Sample Data
Create a worksheet named StudentPerformance and enter the following data.
| StudentID | StudentName | Course | Marks | FeesPaid | PendingFees |
|---|---|---|---|---|---|
| 101 | Samadhan | Java | 35 | 45000 | 5000 |
| 102 | Samiksha | SQL | 78 | 60000 | 0 |
| 103 | Pradnya | Python | 92 | 32000 | 8000 |
| 104 | Omkar | Java | 48 | 75000 | 10000 |
| 105 | Neha | SQL | 88 | 28000 | 2000 |
| 106 | Samadhan | Python | 65 | 55000 | 5000 |
| 107 | Omkar | C# | 35 | 22000 | 3000 |
| 108 | Neha | Java | 81 | 65000 | 0 |
6. Practical Example: Highlight Fees Above ₹50,000
Suppose we want to highlight FeesPaid values greater than 50000 using a red fill.
Expected Excel Worksheet Appearance
Illustrative result using the rule FeesPaid greater than 50000.
Step-by-step procedure
- Select the FeesPaid data range, for example E2:E9.
- Go to Home → Conditional Formatting.
- Select Highlight Cells Rules → Greater Than.
- Enter
50000. - Choose a red fill style.
- Click OK.
Only the selected range is formatted. If your actual FeesPaid column is D instead of E, select the correct range before creating the rule.
7. Highlight Cells Rules
Highlight Cells Rules provide ready-made conditions for common formatting tasks.
| Rule | Example Condition | Possible Use |
|---|---|---|
| Greater Than | FeesPaid > 50000 | Highlight high fee payments. |
| Less Than | Marks < 40 | Identify low marks. |
| Between | Marks between 40 and 60 | Highlight a specific score range. |
| Equal To | Status equals Pending | Identify pending records. |
| Text That Contains | Text contains "Java" | Highlight course names. |
| Duplicate Values | Repeated StudentID | Detect duplicate IDs. |
8. Top/Bottom Rules
Top/Bottom Rules identify the highest or lowest values in a range. They are useful when comparing scores, fees, sales, or performance.
Example: Highlight Top 3 Marks
- Select the Marks range, for example D2:D9.
- Open Home → Conditional Formatting.
- Select Top/Bottom Rules → Top 10 Items.
- Change 10 to 3.
- Choose a formatting style and click OK.
Excel highlights the top three items according to the rule. Ties and duplicate values may affect how many cells are highlighted.
Other available rules
- Top 10 Items
- Top 10%
- Bottom 10 Items
- Bottom 10%
- Above Average
- Below Average
9. Apply Color Scales
Color Scales compare values using a color gradient. A three-color scale often uses one color for low values, another for middle values, and a third for high values.
- Select the Marks range.
- Go to Home → Conditional Formatting → Color Scales.
- Choose a two-color or three-color scale.
- Observe how the cells receive different colors based on their values.
10. Apply Data Bars
- Select the numeric range.
- Go to Home → Conditional Formatting → Data Bars.
- Select a solid or gradient fill style.
- Compare the relative bar lengths.
Data Bars make it easy to compare many numbers without creating a separate chart. They are especially useful for sales, fees, marks, and monthly performance.
11. Apply Icon Sets
Icon Sets display symbols such as arrows, traffic lights, flags, or ratings according to the rule thresholds.
- Select the performance range.
- Go to Home → Conditional Formatting → Icon Sets.
- Choose an icon style, such as three traffic lights or three arrows.
- Review the icons displayed next to the values.
For more control, open Manage Rules → Edit Rule and adjust the thresholds, comparison types, and whether icons alone should be displayed.
12. Formula-Based Conditional Formatting
Use a formula-based rule when a built-in rule cannot express the condition you need. The formula should evaluate to TRUE for the cells that should be formatted.
Example A: Highlight Marks Below 40
Suppose Marks are in D2:D9. Select D2:D9, choose Conditional Formatting → New Rule → Use a formula, and enter:
=D2<40
Choose a red fill. Because D2 is the first cell in the selected range, Excel adjusts the relative reference for each row.
Example B: Highlight Pending Fees Greater Than Zero
Suppose PendingFees are in F2:F9. Select that range and enter:
=F2>0
Every cell with a positive pending fee will receive the chosen formatting style.
Example C: Highlight an Entire Row for Low Marks
To highlight the full student record when Marks in column D are below 40, select the data range A2:F9 and use:
=$D2<40
The dollar sign locks column D, while the row number remains relative. Excel checks the Marks value in column D for each row and formats the entire selected row when the condition is TRUE.
13. Manage and Remove Rules
- Manage Rules: Open Conditional Formatting → Manage Rules to review, edit, reorder, or delete rules.
- Applies To: Check the range to which each rule applies.
- Rule Priority: When multiple rules apply, their order and Stop If True settings can affect the final appearance.
- Clear Rules: Use Conditional Formatting → Clear Rules to remove formatting rules from selected cells or the entire worksheet.
- Existing Formatting: Clearing Conditional Formatting rules is different from clearing cell contents or all cell formatting.
14. Practical Exercises
Task 1: Low Marks
Highlight all marks below 40 in red using Highlight Cells Rules.
Task 2: High Fee Payments
Highlight FeesPaid values greater than 50000 using a red fill.
Task 3: Top Performers
Highlight the top three scores in the Marks column.
Task 4: Color Scale
Apply a three-color scale to compare marks from low to high.
Task 5: Duplicate Student IDs
Use Duplicate Values on the StudentID range to identify repeated IDs. Add a duplicate ID temporarily to test the rule.
Task 6: Entire Row Highlight
Use the formula =$D2<40 to highlight the full
student record whenever Marks are below 40.
Important Points to Remember
- Conditional Formatting changes cell appearance, not cell values.
- Choose the correct range before creating a rule.
- Use Highlight Cells Rules for simple conditions.
- Use Color Scales, Data Bars, and Icon Sets for visual comparisons.
- Use formula-based rules for conditions involving other cells.
- Check relative and absolute references in formulas.
- Review rule priority when multiple rules apply to the same cells.
- Test formatting with both valid and invalid or boundary values.
Summary
Conditional Formatting makes Excel reports more visual by highlighting values that meet specified conditions. Highlight Cells Rules, Top/Bottom Rules, Color Scales, Data Bars, and Icon Sets help users compare data and identify important patterns.
Formula-based rules provide additional flexibility when formatting depends on other cells or entire rows. Correct ranges, formula references, and rule priorities help ensure accurate results.