Basic Conditional Formatting Excel Practice Questions with Solutions

Introduction

Conditional Formatting is one of the most useful Excel features for quickly identifying important data. It can automatically change the appearance of cells when they meet a specific condition. In this chapter, you will practice highlighting numbers, text, dates, duplicates, top values, low values, and ranges. These questions are designed to help you become comfortable with the most commonly used Conditional Formatting options. Basic Conditional Formatting Excel practice questions with solutions to help you understand the concepts.

Question 1: Highlight Sales Greater Than ₹50,000

Problem Statement

You have a list of employee sales. Highlight all sales values greater than ₹50,000.

Excel Data

Excel Solution

1. Select the Sales range B2:B7.
2. Go to Home → Conditional Formatting.
3. Select Highlight Cells Rules → Greater Than.
4. Enter:

50000

5. Choose a formatting style.
6. Click OK.

Expected Result

The cells containing:

• ₹62,000
• ₹75,000
• ₹85,000

should be highlighted.

Concepts Covered

• Conditional Formatting in Excel
• Greater Than rule
• Number-based formatting

Question 2: Highlight Sales Less Than ₹40,000

Problem Statement

Highlight all employees whose sales are below ₹40,000.

Excel Data

Excel Solution

Select B2:B7 and use:

Home → Conditional Formatting → Highlight Cells Rules → Less Than

Enter:

40000

Choose a formatting style and click OK.

Expected Result

The following values should be highlighted:

• ₹38,000
• ₹29,000

Concepts Covered

• Less Than rule
• Number comparison
• Conditional highlighting

Question 3: Highlight Sales Between ₹50,000 and ₹80,000

Problem Statement

Highlight sales values that fall between ₹50,000 and ₹80,000.

Excel Data

Excel Solution

Select B2:B8.

Go to:

Home → Conditional Formatting → Highlight Cells Rules → Between

Enter:

50000

and

80000

Select a formatting style and click OK.

Expected Result

These values should be highlighted:

• ₹62,000
• ₹75,000
• ₹55,000

Concepts Covered

• Between rule
• Range-based formatting
• Conditional Formatting

Question 4: Highlight Employees From the IT Department

Problem Statement

Highlight all cells containing IT in the Department column.

Excel Data

Excel Solution

Select B2:B7.

Go to:

Home → Conditional Formatting → Highlight Cells Rules → Text that Contains

Enter:

IT

Choose a formatting style and click OK.

Expected Result

The cells containing IT should be highlighted.

Concepts Covered

• Text-based Conditional Formatting
• Text matching
• Department analysis

Question 5: Highlight Duplicate Employee Names

Problem Statement

Identify duplicate employee names in the dataset.

Excel Data

Excel Solution

1. Select A2:A8.
2. Go to Home → Conditional Formatting.
3. Select Highlight Cells Rules → Duplicate Values.
4. Keep Duplicate selected.
5. Select a formatting style.
6. Click OK.

Expected Result

The duplicate names should be highlighted:

• Rahul
• Rahul
• Priya
• Priya

Concepts Covered

• Duplicate Values
• Data checking
• Duplicate identification

Question 6: Highlight the Top 3 Sales Values

Problem Statement

Highlight the top 3 highest sales values.

Excel Data

Excel Solution

Select B2:B8.

Go to:

Home → Conditional Formatting → Top/Bottom Rules → Top 10 Items

Change the number from:

10

to:

3

Choose a formatting style and click OK.

Expected Result

The top three values should be highlighted:

• ₹115,000
• ₹105,000
• ₹92,000

Concepts Covered

• Top N values
• Top/Bottom Rules
• Sales analysis

Question 7: Highlight the Bottom 2 Sales Values

Problem Statement

Identify the two lowest sales values using Conditional Formatting.

Excel Data

Excel Solution

Select B2:B8.

Go to:

Home → Conditional Formatting → Top/Bottom Rules → Bottom 10 Items

Change:

10

to:

2

Choose a formatting style and click OK.

Expected Result

The following values should be highlighted:

• ₹38,000
• ₹45,000

Concepts Covered

• Bottom N values
• Top/Bottom Rules
• Low-value identification

Question 8: Highlight Duplicate Product IDs

Problem Statement

A company has received several orders. Identify duplicate Order IDs.

Excel Data

Excel Solution

Select A2:A8.

Go to:

Home → Conditional Formatting in excel → Highlight Cells Rules → Duplicate Values

Keep Duplicate selected and apply a formatting style.

Expected Result

The following Order IDs should be highlighted:

• O101
• O101
• O102
• O102

Concepts Covered

• Duplicate identification
• Order data validation
• Conditional Formatting on IDs

Question 9: Highlight Dates Before a Given Date

Problem Statement

You have employee joining dates. Highlight employees who joined before 1 January 2024.

Excel Data

Excel Solution

Select B2:B7.

Go to:

Home → Conditional Formatting → Highlight Cells Rules → Less Than

Enter:

01-01-2024

Choose a formatting style and click OK.

Expected Result

The dates before 1 January 2024 should be highlighted:

• 15-06-2022
• 10-11-2023
• 18-08-2021

Concepts Covered

• Date-based Conditional Formatting
• Date comparison
• Historical data analysis

Question 10: Create a Sales Performance Highlight Using Multiple Rules

Problem Statement

Create a simple performance indicator for employee sales:

• Sales ₹80,000 or more → highlight strongly.
• Sales between ₹50,000 and ₹79,999 → apply another formatting style.
• Sales below ₹50,000 → apply another formatting style.

Excel Data

Excel Solution

Select B2:B8.

Create the first rule:

Home → Conditional Formatting → Highlight Cells Rules → Greater Than or Equal To

Enter:

80000

Create the second rule:

Conditional Formatting → Highlight Cells Rules → Between

Enter:

50000

and:

79999

Create the third rule:

Conditional Formatting → Highlight Cells Rules → Less Than

Enter:

50000

Apply a different formatting style to each rule.

Expected Result

Concepts Covered

• Multiple Conditional Formatting rules
• Greater Than or Equal To
• Between
• Less Than
• Performance-based formatting

Key Takeaways

• Conditional Formatting changes cell formatting automatically when a condition is met.
• Greater Than can identify values above a specific number.
• Less Than can identify values below a specific number.
• Between can highlight values within a specified range.
• Text that Contains can identify specific text.
• Duplicate Values in Excel helps find duplicate records.
• Top/Bottom Rules can identify the highest or lowest values.
• Conditional Formatting can also be applied to dates.
• Multiple rules can be applied to the same data range.
• Conditional Formatting changes the appearance of data without changing the actual values.

FAQs

1. What is Conditional Formatting in Excel?

Conditional Formatting is an Excel feature that automatically changes the formatting of cells when they meet specified conditions.

2. Does Conditional Formatting in excel change the actual cell value?

No. Conditional Formatting changes how the cell looks. It does not change the underlying value.

3. How do I highlight values greater than a number?

Select the data and use:

Home → Conditional Formatting → Highlight Cells Rules → Greater Than

Then enter the required value.

4. Can Conditional Formatting identify duplicates?

Yes. Excel has a built-in Duplicate Values rule that can quickly highlight duplicate data.

5. Can I highlight text using Conditional Formatting in Excel?

Yes. The Text that Contains rule can highlight cells containing specific text.

6. Can I use Conditional Formatting in excel for dates?

Yes. You can create rules based on dates, such as dates before, after, or within a particular period.

7. Can I apply more than one Conditional Formatting in excel rule to a range?

Yes. Multiple rules can be applied to the same range. This is useful when creating categories such as high, medium, and low performance.

8. What is the Top 10 Items rule used for?

The Top 10 Items rule identifies the highest values in a selected range. You can change 10 to another number, such as 3 or 5.

9. Can I remove Conditional Formatting later?

Yes. Select the cells and use:

Home → Conditional Formatting → Clear Rules

You can clear rules from the selected cells or the entire worksheet.

10. Does Conditional Formatting work automatically when data changes?

Yes. When the cell values change, Excel evaluates the rule again and updates the formatting when the condition is met.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar