Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

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.
Remember: Conditional Formatting changes how data looks; it does not change the underlying value stored in a cell.

2. Where Is Conditional Formatting in Excel?

Follow these steps to find the Conditional Formatting command.

  1. Select the cells or range where formatting should be applied.
  2. Open the Home tab.
  3. Locate the Styles group.
  4. Click Conditional Formatting.
  5. Select a rule type and configure the condition.
Excel Ribbon — Illustrative Visual
File Home Insert Page Layout Formulas Data Review View Clipboard Paste Copy • Cut Font B I U Color • Borders Styles Conditional Formatting Select a rule Format as Table Cell Styles Home → Styles → Conditional Formatting

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
Data Bars Color Scales Student Marks Student Marks Samadhan Samiksha Pradnya Omkar Neha Samadhan Samiksha Pradnya Omkar Neha 35 60 75 48 70 35 60 75 48 70 Longer bar = higher value Color represents relative value

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
101SamadhanJava35450005000
102SamikshaSQL78600000
103PradnyaPython92320008000
104OmkarJava487500010000
105NehaSQL88280002000
106SamadhanPython65550005000
107OmkarC#35220003000
108NehaJava81650000
Important: The sample contains repeated names intentionally. StudentID values are unique. Use the StudentID column when practising duplicate detection so that duplicate names are not mistaken for duplicate student records.

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
A B C D Student Course Marks Fees Paid Samadhan Java 35 Samiksha SQL 78 Pradnya Python 92 Omkar Java 48 Neha SQL 88 Samadhan Python 65 60000 75000 55000

Illustrative result using the rule FeesPaid greater than 50000.

Step-by-step procedure
  1. Select the FeesPaid data range, for example E2:E9.
  2. Go to Home → Conditional Formatting.
  3. Select Highlight Cells Rules → Greater Than.
  4. Enter 50000.
  5. Choose a red fill style.
  6. 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
  1. Select the Marks range, for example D2:D9.
  2. Open Home → Conditional Formatting.
  3. Select Top/Bottom Rules → Top 10 Items.
  4. Change 10 to 3.
  5. 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.

  1. Select the Marks range.
  2. Go to Home → Conditional Formatting → Color Scales.
  3. Choose a two-color or three-color scale.
  4. Observe how the cells receive different colors based on their values.
Tip: Color scales are useful for comparing values within the selected range. They do not automatically mean that a particular color represents a fixed business status unless you configure the thresholds accordingly.

10. Apply Data Bars

  1. Select the numeric range.
  2. Go to Home → Conditional Formatting → Data Bars.
  3. Select a solid or gradient fill style.
  4. 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.

  1. Select the performance range.
  2. Go to Home → Conditional Formatting → Icon Sets.
  3. Choose an icon style, such as three traffic lights or three arrows.
  4. 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:

Excel Formula
=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:

Excel Formula
=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:

Excel Formula
=$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.

Important: The first cell reference in your formula must correspond to the first row or cell in the selected range. Incorrect relative or absolute references can format the wrong cells.

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.