Excel Sorting Filtering Duplicates and Data Management Practice Questions with Solutions

Introduction

Sorting, filtering, and duplicate handling are important Excel skills when working with large datasets. In this chapter, you will practice arranging records, filtering specific information, applying multiple filters, identifying duplicate values, removing duplicates, and managing data efficiently. Each question uses a different practical situation so you can check your Excel skills rather than simply repeating the same sorting or filtering task. Excel Sorting Filtering Duplicates and Data Management practice questions with solutions to help you understand the concepts.

---

Question 1: Sort Students by Marks

Problem Statement

You have a student marksheet. Sort the students from highest marks to lowest marks.

Excel Data

Roll No.	Student Name	Course	Marks
105	Arjun	Python	72
101	Rahul	Excel	91
104	Neha	SQL	85
102	Priya	Python	96
103	Amit	Excel	78

Solution

1. Select the complete data range.
2. Go to Data → Sort.
3. Select Marks under Sort By.
4. Select Largest to Smallest.
5. Click OK.

Expected Output

Roll No.	Student Name	Course	Marks
102	Priya	Python	96
101	Rahul	Excel	91
104	Neha	SQL	85
103	Amit	Excel	78
105	Arjun	Python	72

---

Question 2: Sort Employees by Department and Salary

Problem Statement

Sort the employee data first by Department alphabetically and then by Salary from highest to lowest within each department.

Excel Data

Employee	Department	Salary
Rahul	IT	55000
Priya	HR	45000
Amit	IT	65000
Neha	Finance	52000
Arjun	HR	60000
Simran	Finance	48000

Solution

1. Select the complete table.
2. Go to Data → Sort.
3. Set the first sorting level:
   • Column: Department
   • Order: A to Z
4. Click Add Level.
5. Set the second level:
   • Column: Salary
   • Order: Largest to Smallest
6. Click OK.

Expected Output

Employee	Department	Salary
Neha	Finance	52000
Simran	Finance	48000
Arjun	HR	60000
Priya	HR	45000
Amit	IT	65000
Rahul	IT	55000

---

Question 3: Filter Employees from a Specific Department

Problem Statement

From the employee dataset, display only employees who work in the IT department.

Excel Data

Employee ID	Employee	Department	City	Salary
E101	Rahul	IT	Delhi	55000
E102	Priya	HR	Noida	45000
E103	Amit	Finance	Delhi	52000
E104	Neha	IT	Gurgaon	62000
E105	Arjun	Marketing	Delhi	48000
E106	Simran	IT	Noida	58000

Solution

1. Select the table.
2. Go to Data → Filter.
3. Open the filter arrow in the Department column.
4. Clear Select All.
5. Select IT.
6. Click OK.

Expected Output

Employee ID	Employee	Department	City	Salary
E101	Rahul	IT	Delhi	55000
E104	Neha	IT	Gurgaon	62000
E106	Simran	IT	Noida	58000

---

Question 4: Apply Multiple Filters

Problem Statement

From the employee data, display employees who:

• Work in Delhi
• Have a salary greater than ₹50,000

Excel Data

Employee	Department	City	Salary
Rahul	IT	Delhi	55000
Priya	HR	Noida	45000
Amit	Finance	Delhi	52000
Neha	IT	Gurgaon	62000
Arjun	Marketing	Delhi	48000
Simran	IT	Delhi	58000

Solution

1. Select the data.
2. Enable Data → Filter.
3. Open the City filter.
4. Select Delhi.
5. Open the Salary filter.
6. Select Number Filters → Greater Than.
7. Enter 50000.
8. Click OK.

Expected Output

Employee	Department	City	Salary
Rahul	IT	Delhi	55000
Amit	Finance	Delhi	52000
Simran	IT	Delhi	58000

---

Question 5: Filter Sales Greater Than a Specific Amount

Problem Statement

A company wants to identify products with sales greater than ₹1,00,000.

Excel Data

Product	Category	Sales
Laptop	Electronics	250000
Keyboard	Accessories	45000
Monitor	Electronics	125000
Mouse	Accessories	35000
Printer	Electronics	95000
Smartphone	Electronics	180000

Solution

1. Select the sales table.
2. Enable the filter.
3. Open the Sales filter.
4. Select Number Filters → Greater Than.
5. Enter 100000.
6. Click OK.

Expected Output

Product	Category	Sales
Laptop	Electronics	250000
Monitor	Electronics	125000
Smartphone	Electronics	180000

---

Question 6: Find Duplicate Customer Records

Problem Statement

Identify duplicate customer email addresses in the following dataset.

Excel Data

Customer ID	Customer Name	Email
C101	Rahul	rahul@gmail.com
C102	Priya	priya@gmail.com
C103	Amit	amit@gmail.com
C104	Neha	rahul@gmail.com
C105	Arjun	arjun@gmail.com
C106	Simran	amit@gmail.com

Solution

1. Select the Email column.
2. Go to Home → Conditional Formatting.
3. Select Highlight Cells Rules → Duplicate Values.
4. Keep Duplicate selected.
5. Choose a formatting option.
6. Click OK.

Expected Output

The following email addresses should be identified as duplicates:

• rahul@gmail.com
• amit@gmail.com

---

Question 7: Remove Duplicates Product Records

Problem Statement

The product list contains duplicate records. Remove the duplicate rows while keeping one copy of each record.

Excel Data

Product ID	Product	Category	Price
P101	Laptop	Electronics	55000
P102	Mouse	Accessories	550
P103	Keyboard	Accessories	850
P101	Laptop	Electronics	55000
P104	Monitor	Electronics	12000
P102	Mouse	Accessories	550

Solution

1. Select the complete dataset.
2. Go to Data → Remove Duplicates.
3. Make sure all relevant columns are selected.
4. Click OK.
5. Excel will remove duplicate rows.

Expected Output

Product ID	Product	Category	Price
P101	Laptop	Electronics	55000
P102	Mouse	Accessories	550
P103	Keyboard	Accessories	850
P104	Monitor	Electronics	12000

---

Question 8: Sort Dates from Newest to Oldest

Problem Statement

Sort employee records according to their joining date, starting with the most recently joined employee.

Excel Data

Employee	Department	Joining Date
Rahul	IT	15-Jan-2024
Priya	HR	20-Mar-2025
Amit	Finance	05-Feb-2024
Neha	IT	10-Aug-2025
Arjun	Marketing	25-Jun-2024

Solution

1. Select the complete table.
2. Go to Data → Sort.
3. Select Joining Date.
4. Select Newest to Oldest.
5. Click OK.

Expected Output

Employee	Department	Joining Date
Neha	IT	10-Aug-2025
Priya	HR	20-Mar-2025
Arjun	Marketing	25-Jun-2024
Amit	Finance	05-Feb-2024
Rahul	IT	15-Jan-2024

---

Question 9: Filter Products by Multiple Categories

Problem Statement

A store wants to display only products belonging to the Electronics or Accessories categories.

Excel Data

Product	Category	Stock
Laptop	Electronics	15
Mouse	Accessories	45
Chair	Furniture	20
Monitor	Electronics	12
Keyboard	Accessories	35
Desk	Furniture	10

Solution

1. Select the complete dataset.
2. Enable Filter.
3. Open the Category filter.
4. Clear Select All.
5. Select:
   • Electronics
   • Accessories
6. Click OK.

Expected Output

Product	Category	Stock
Laptop	Electronics	15
Mouse	Accessories	45
Monitor	Electronics	12
Keyboard	Accessories	35

---

Question 10: Clean and Organize a Customer Dataset

Problem Statement

You have received a customer dataset containing duplicate records. First remove exact duplicate records, then sort the remaining customers by City and then by Customer Name.

Excel Data

Customer ID	Customer Name	City	Purchase
C105	Arjun	Delhi	45000
C101	Rahul	Mumbai	35000
C103	Amit	Delhi	52000
C102	Priya	Jaipur	28000
C101	Rahul	Mumbai	35000
C104	Neha	Delhi	41000
C103	Amit	Delhi	52000
C106	Simran	Mumbai	62000

Solution

First, remove duplicates:

1. Select the complete dataset.
2. Go to Data → Remove Duplicates.
3. Keep all relevant columns selected.
4. Click OK.

Next, sort the cleaned data:

5. Go to Data → Sort.
6. Set the first level:
   • Sort By: City
   • Order: A to Z
7. Click Add Level.
8. Set the second level:
   • Then By: Customer Name
   • Order: A to Z
9. Click OK.

Expected Output

Customer ID	Customer Name	City	Purchase
C103	Amit	Delhi	52000
C105	Arjun	Delhi	45000
C104	Neha	Delhi	41000
C102	Priya	Jaipur	28000
C101	Rahul	Mumbai	35000
C106	Simran	Mumbai	62000

This question combines duplicate removal, multi-level sorting and data organization.

Key Takeaways

• Sort is used to arrange data in a particular order.
• Excel can sort text, numbers and dates.
• Multi-level sorting allows you to sort data using more than one column.
• Filter displays only records that meet selected conditions.
• Multiple filters can be applied to analyze specific records.
• Number filters can find values greater than, less than or equal to a specified value.
• Duplicate values can be identified using Conditional Formatting.
• Remove Duplicates permanently removes duplicate records from the selected data.
• Always select the complete related dataset before sorting or removing duplicates.
• Sorting and filtering are useful for analyzing large datasets without changing the underlying information structure.
• Before using Remove Duplicates, make sure you have selected the correct columns and data.

FAQs

1. What is sorting in Excel?

Sorting arranges data according to a specific order, such as A to Z, Z to A, smallest to largest, largest to smallest, oldest to newest or newest to oldest.

2. What is filtering in Excel?

Filtering temporarily hides records that do not meet selected conditions and displays only the records relevant to your analysis.

3. Can I sort Excel data using multiple columns?

Yes. Excel’s Sort feature allows you to add multiple sorting levels. For example, you can sort first by Department and then by Salary.

4. Does filtering delete the hidden data?

No. Filtering only hides records that do not meet the selected conditions. The original data remains in the worksheet.

5. How can I identify duplicate values in Excel?

Select the relevant cells and use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.

6. What does Remove Duplicates do in Excel?

The Remove Duplicates feature removes duplicate records from the selected dataset while keeping one occurrence of the matching record.

7. What is the difference between finding duplicates and removing duplicates?

Finding duplicates identifies repeated values without deleting them. Removing duplicates deletes repeated records from the selected dataset.

8. Can Excel sort dates?

Yes. Excel can sort dates from oldest to newest or newest to oldest, provided the dates are recognized as actual Excel dates.

9. Can I filter more than one value from the same column?

Yes. For example, you can filter a Department column to show both IT and Finance.

10. Why should I select the entire dataset before sorting?

Selecting the complete dataset helps keep related information together. Sorting only one column can separate values from their corresponding records and cause incorrect data relationships.

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