FILTER, SORT and SORTBY Practice Questions with Solutions

Introduction

FILTER, SORT, and SORTBY are powerful modern Excel functions for creating dynamic reports without manually filtering or sorting your original data. They are especially useful when working with large datasets, dashboards, employee records, sales reports, and analysis sheets. In this chapter, you will practice these functions through different problems, including single-condition filtering, multiple conditions, sorting by different columns, custom sorting, and combining FILTER SORT and SORTBY Practice Questions with Solutions to help you understand the concepts.

Note: These functions are available in Microsoft 365 and supported newer versions of Excel.

Question 1: Filter Employees From the IT Department

Problem Statement

From the employee database, create a separate dynamic list containing only employees who belong to the IT department.

Excel Data

Excel Solution

Enter this formula in an empty area:

=FILTER(A2:C8,B2:B8="IT","No Match")

Expected Output

Concepts Covered

• FILTER
• Dynamic arrays
• Text criteria
• Formula-based extraction

Question 2: Filter Products Above a Specific Price

Problem Statement

Extract all products whose price is ₹5,000 or more.

Excel Data

Excel Solution

Use:

=FILTER(A2:C8,C2:C8>=5000,"No Match")

Expected Output

Concepts Covered

• FILTER
• Numeric criteria
• Greater-than-or-equal condition

Question 3: Filter Data Using Two Conditions

Problem Statement

Extract employees who:

• Belong to the IT department
• Have a salary of ₹60,000 or more

Excel Data

Excel Solution

Use:

=FILTER(A2:C8,(B2:B8="IT")*(C2:C8>=60000),"No Match")

Expected Output

Concepts Covered

• FILTER
• Multiple criteria
• AND logic
• Boolean multiplication

Question 4: Sort Employees by Salary From Highest to Lowest

Problem Statement

Sort the complete employee list according to salary, starting with the highest-paid employee.

Excel Data

Excel Solution

Use:

=SORT(A2:C8,3,-1)

Expected Output

Concepts Covered

• SORT
• Descending sort
• Column index
• Dynamic sorting

Question 5: Sort Employees Alphabetically

Problem Statement

Sort the employee database alphabetically by employee name from A to Z.

Excel Solution

Use:

=SORT(A2:C8,1,1)

Expected Output

Concepts Covered

• SORT
• Ascending order
• Sorting by text
• Dynamic arrays

Question 6: Sort Sales Data Using SORTBY

Problem Statement

Sort the sales report according to Sales Amount, from highest to lowest.

Excel Data

Excel Solution

Use:

=SORTBY(A2:C8,C2:C8,-1)

Expected Output

Concepts Covered

• SORTBY
• Sorting by another range
• Descending order
• Dynamic sorting

Question 7: Sort Products by Category and Then by Price

Problem Statement

Sort the product list using two levels:

1. Category from A to Z.
2. Price from highest to lowest within each category.

Excel Data

Excel Solution

Use:

=SORTBY(A2:C8,B2:B8,1,C2:C8,-1)

Expected Output

Concepts Covered

• SORTBY
• Multiple sorting levels
• Ascending + descending sorting
• Multi-level reports

Question 8: Filter Active Employees and Sort by Salary

Problem Statement

Create a dynamic report that:

1. Includes only employees with status "Active".
2. Sorts them by salary from highest to lowest.

Excel Data

Excel Solution

Use:

=SORT(FILTER(A2:D8,B2:B8="Active"),4,-1)

Expected Output

Concepts Covered

• FILTER
• SORT
• Combined dynamic formulas
• Conditional reporting

Question 9: Filter Sales for Two Selected Regions

Problem Statement

Create a report containing only sales records from North OR South.

Excel Data

Excel Solution

Use:

=FILTER(A2:C8,(B2:B8="North")+(B2:B8="South"),"No Match")

Expected Output

Concepts Covered

• FILTER
• OR logic
• Boolean addition
• Dynamic extraction

Important Concept

In a FILTER formula:

(condition1)*(condition2)

generally works as AND.

While:

(condition1)+(condition2)

can be used for OR logic.

Question 10: Create a Dynamic Top Sales Report

Problem Statement

Create a dynamic report that:

1. Includes only sales of ₹70,000 or more.
2. Sorts the results from highest to lowest sales.
3. Returns only the top 3 records.

Excel Data

Excel Solution

Use:

=TAKE(SORT(FILTER(A2:C9,C2:C9>=70000),3,-1),3)

Expected Output

Formula Concepts

This formula combines three functions:

=TAKE(SORT(FILTER(A2:C9,C2:C9>=70000),3,-1),3)

• FILTER → keeps sales of ₹70,000 or more.
• SORT → sorts the filtered records by Sales from highest to lowest.
• TAKE → returns only the first three records.

This creates a dynamic Top 3 sales report without manually filtering or sorting the original data.

Key Takeaways

• FILTER in Excel extracts records based on conditions.
• SORT dynamically sorts an entire range.
• SORTBY sorts one range based on another range.
• FILTER can handle both AND and OR conditions.
• SORTBY is useful when the sorting range is different from the displayed data.
• FILTER and SORT can be combined to create automated reports.
• Multiple sorting levels can be created with SORTBY.
• Dynamic formulas do not change the original dataset.
• FILTER + SORT + TAKE can create dynamic Top-N reports.
• Dynamic-array formulas automatically spill their results into nearby cells.
• These functions are especially useful for dashboards, reports, analysis, and large datasets.

FAQs

1. What is the FILTER function in Excel?

FILTER returns only the rows or columns that meet a specified condition.

Example:

=FILTER(A2:C20,C2:C20>=50000)

This returns records where column C contains a value of at least 50,000.

2. What is the SORT function used for?

SORT dynamically sorts a range according to a selected row or column.

Example:

=SORT(A2:C20,3,-1)

This sorts the data based on the third column in descending order.

3. What is the difference between SORT and SORTBY?

SORT sorts a range using a column or row within that range.

SORTBY lets you specify a separate range to control the sorting.

Example:

=SORTBY(A2:C20,C2:C20,-1)

4. Can FILTER use two conditions?

Yes.

For AND logic:

=FILTER(A2:C20,(B2:B20="IT")*(C2:C20>=50000))

Both conditions must be satisfied.

5. How do I use OR conditions with FILTER?

You can use addition between conditions.

=FILTER(A2:C20,(B2:B20="IT")+(B2:B20="HR"))

This returns records belonging to either IT or HR.

6. Can FILTER and SORT be used together?

Yes.

For example:

=SORT(FILTER(A2:C20,C2:C20>=50000),3,-1)

First, Excel filters the data and then sorts the filtered result.

7. Can SORTBY sort using more than one condition?

Yes.

For example:

=SORTBY(A2:C20,B2:B20,1,C2:C20,-1)

This sorts first by column B in ascending order and then by column C in descending order.

8. Why does Excel show a #SPILL! error?

A #SPILL! error can occur when cells where a dynamic formula needs to return its results are not empty.

For example, if:

=FILTER(A2:C20,B2:B20="IT")

needs several cells but something is already occupying one of those cells, Excel may display #SPILL!.

9. Can these formulas update automatically?

Yes. Dynamic-array formulas recalculate when the referenced data changes, provided the formula’s source range includes the changed data.

10. Which is better: FILTER, SORT or SORTBY?

They perform different jobs:

• FILTER → extracts matching data.
• SORT → sorts a range.
• SORTBY → sorts a range using one or more specified ranges.

They can also be combined to create more advanced dynamic reports.

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

Rishabh Kumar