Excel Conditional Formatting-Top/Bottom Rules Practice Questions with Solutions

Introduction

Excel Conditional Formatting can quickly identify important values without manually checking every row. In this chapter, you will practice Highlight Cells Rules, Duplicate Values, Top/Bottom Rules, Above Average, Below Average, and multiple conditional rules. The 10 questions use different datasets so you can practice these features in realistic situations. Excel Conditional Formatting-Top/Bottom Rules Practice Questions with Solutions to help you understand the concepts.

Question 1: Highlight Sales Greater Than ₹75,000

Problem Statement

Identify employees whose sales are greater than ₹75,000 using Conditional Formatting.

Excel Data

Excel Solution

Select B2:B8.

Go to:

Home → Conditional Formatting → Highlight Cells Rules → Greater Than

Enter:

75000

Choose a formatting style and click OK.

Expected Result

The following values should be highlighted:

• ₹82,000
• ₹95,000
• ₹1,15,000

Concepts Covered

• Greater Than rule
• Number comparison
• Conditional highlighting

Question 2: Highlight Salaries Between ₹40,000 and ₹60,000

Problem Statement

Highlight employees whose salary is between ₹40,000 and ₹60,000.

Excel Data

Excel Solution

Select B2:B8.

Go to:

Home → Conditional Formatting → Highlight Cells Rules → Between

Enter:

40000

and:

60000

Apply a formatting style.

Expected Result

These values should be highlighted:

• ₹45,000
• ₹52,000
• ₹60,000
• ₹42,000

Concepts Covered

• Between rule
• Salary analysis
• Range-based highlighting

Question 3: Highlight Employees From a Specific Department

Problem Statement

Highlight all employees who belong to the Sales department.

Excel Data

Excel Solution

Select B2:B8.

Go to:

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

Enter:

Sales

Apply a formatting style.

Expected Result

All cells containing Sales should be highlighted.

Concepts Covered

• Text-based highlighting
• Department filtering
• Conditional Formatting

Question 4: Find Duplicate Customer IDs

Problem Statement

Identify duplicate Customer IDs using the Duplicate Values rule.

Excel Data

Excel Solution

Select A2:A8.

Go to:

Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values

Keep Duplicate selected and click OK after selecting the required formatting.

Expected Result

The duplicate IDs should be highlighted:

• C101
• C101
• C102
• C102

Concepts Covered

• Duplicate Values
• Duplicate ID detection
• Data quality checking

Question 5: Find Unique Product Codes

Problem Statement

Use Conditional Formatting to identify unique Product Codes instead of duplicates.

Excel Data

Excel Solution

Select A2:A8.

Go to:

Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values

In the dialog box, change:

Duplicate → Unique

Choose a formatting style and click OK.

Expected Result

The unique codes should be highlighted:

• P102
• P104
• P105

Concepts Covered

• Unique Values
• Duplicate Values
• Data validation

Question 6: Highlight Top 3 Sales Values

Problem Statement

Find the three highest sales values using Top/Bottom Rules.

Excel Data

Excel Solution

Select B2:B8.

Go to:

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

Change:

10

to:

3

Apply a formatting style.

Expected Result

The top three values should be highlighted:

• ₹1,25,000
• ₹1,05,000
• ₹92,000

Concepts Covered

• Top 10 Items
• Top 3 analysis
• Sales performance

Question 7: Highlight Bottom 3 Product Sales

Problem Statement

Find the three products with the lowest sales.

Excel Data

Excel Solution

Select B2:B8.

Go to:

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

Change:

10

to:

3

Apply a formatting style.

Expected Result

The bottom three values should be highlighted:

• ₹35,000
• ₹48,000
• ₹58,000

Concepts Covered

• Bottom 10 Items
• Bottom 3 analysis
• Product performance

Question 8: Highlight Values Above Average

Problem Statement

Identify employees whose sales are above the average sales.

Excel Data

Excel Solution

Select B2:B8.

Go to:

Home → Conditional Formatting → Top/Bottom Rules → Above Average

Choose a formatting style and click OK.

Expected Result

Excel will automatically calculate the average and highlight values above it.

The average of these sales is approximately ₹74,286, so the highlighted values should be:

• ₹95,000
• ₹85,000
• ₹1,10,000

Concepts Covered

• Above Average rule
• Automatic average calculation
• Performance comparison

Question 9: Highlight Values Below Average

Problem Statement

Identify products whose sales are below the average product sales.

Excel Data

Excel Solution

Select B2:B7.

Go to:

Home → Conditional Formatting → Top/Bottom Rules → Below Average

Choose a formatting style and click OK.

Expected Result

The average sales are approximately ₹108,833.

The values below average should be highlighted:

• ₹65,000
• ₹48,000
• ₹35,000

Concepts Covered

• Below Average rule
• Automatic calculation
• Product comparison

Question 10: Create Multiple Highlight and Top/Bottom Rules

Problem Statement

Create a Conditional Formatting setup for employee performance:

• Highlight sales greater than ₹90,000.
• Highlight the bottom 2 sales values.
• Identify any duplicate sales values.

Excel Data

Excel Solution

Rule 1: Sales Greater Than ₹90,000

Select B2:B9.

Go to:

Home → Conditional Formatting → Highlight Cells Rules → Greater Than

Enter:

90000

Apply a formatting style.

Rule 2: Bottom 2 Sales

Create another rule using:

Conditional Formatting → Top/Bottom Rules → Bottom 10 Items

Change:

10

to:

2

Apply another formatting style.

Rule 3: Duplicate Sales

Create another rule using:

Conditional Formatting → Highlight Cells Rules → Duplicate Values

Select Duplicate and apply another formatting style.

Expected Result

The dataset contains:

Greater than ₹90,000:

• ₹92,000
• ₹1,05,000
• ₹1,25,000

Bottom 2 values:

• ₹45,000
• ₹45,000

Duplicate values:

• ₹45,000
• ₹45,000
• ₹65,000
• ₹65,000

Concepts Covered

• Multiple Conditional Formatting rules
• Greater Than
• Bottom N
• Duplicate Values
• Rule combination
• Performance analysis

Key Takeaways

• Highlight Cells Rules are useful for quickly identifying values or text that meet a condition.
• Duplicate Values can help find repeated IDs, codes, names, or numbers.
• The Duplicate Values feature can also identify unique values.
• Top 10 Items can be changed to Top 3, Top 5, Top 20, or another number.
• Bottom 10 Items works in the same way for the lowest values.
• Above Average automatically compares values against the average of the selected range.
• Below Average identifies values below the calculated average.
• Multiple Conditional Formatting rules can be applied to the same range.
• Conditional Formatting is useful for finding unusual, important, repeated, high, and low values quickly.
• These rules are especially useful when working with large datasets.

FAQs

1. What are Highlight Cells Rules in Excel?

Highlight Cells Rules are predefined Conditional Formatting options that can highlight cells based on values or text.

Examples include:

• Greater Than
• Less Than
• Between
• Equal To
• Text that Contains
• A Date Occurring
• Duplicate Values

2. How do I highlight duplicate values in Excel?

Select the data and go to:

Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values

Then select the required formatting.

3. Can Conditional Formatting identify unique values?

Yes. In the Duplicate Values dialog box, you can change the selection from Duplicate to Unique.

4. How do I highlight the top 5 values?

Select the data and go to:

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

Change 10 to 5.

5. How do I highlight the lowest values?

Use:

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

You can change the number to 3, 5, 10, or another required value.

6. What does Above Average Conditional Formatting do?

It automatically calculates the average of the selected values and highlights values that are greater than that average.

7. What does Below Average Conditional Formatting do?

It highlights values that are lower than the average of the selected range.

8. Can I apply multiple Conditional Formatting rules to the same cells?

Yes. You can create multiple rules for the same range. For example, one rule can identify high sales, another can identify duplicates, and another can identify the lowest values.

9. Can I use Conditional Formatting for text?

Yes. Text that Contains can highlight cells containing specific words, names, departments, product names, or other text.

10. Where can I manage existing Conditional Formatting rules?

Go to:

Home → Conditional Formatting → Manage Rules

From there, you can view, edit, delete, or change the priority of existing rules.

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

Rishabh Kumar