Filtering and sorting in queries in Excel - Step-by-Step Guide

Introduction

Filtering and sorting in queries help you find and organize data quickly. You can show only the rows you want and arrange them in order, making it easier to understand your data.

When you want to see only sales from a specific month in a large sales report

When you need to list customers alphabetically to find one quickly

When you want to show only products with stock less than 10

When you want to sort employees by their hire date from newest to oldest

When you want to create a summary table that only includes data from a certain region

Steps

Step 1: Click

- any cell inside your data table

Excel recognizes your data range and activates the Table Tools

Step 2: Go to

- Data tab on the ribbon

You see options for sorting and filtering your data

Step 3: Click

- Filter button in the Sort & Filter group

Dropdown arrows appear in the header cells of your table columns

Step 4: Click

- dropdown arrow in the column you want to filter

A menu shows all unique values in that column with checkboxes

Step 5: Uncheck

- values you do not want to see

Only rows with checked values remain visible in the table

💡 Use the Search box to find specific values quickly

Step 6: Click

- dropdown arrow in the column you want to sort

Sorting options appear such as Sort A to Z or Sort Z to A

Step 7: Select

- Sort A to Z or Sort Z to A

The table rows reorder based on the selected column in ascending or descending order

Before vs After

Before

Table shows 100 rows with mixed sales dates and product names in no particular order

After

Table shows only 20 rows with sales from March, sorted alphabetically by product name

Settings Reference

Filter

📍 Data tab > Sort & Filter group > Filter button

To show only rows that meet certain criteria

Default: No filter applied (all rows visible)

Sort

📍 Data tab > Sort & Filter group > Sort buttons or Sort dialog

To arrange rows in ascending or descending order based on column values

Default: No sorting applied (original order)

Common Mistakes

Trying to filter without selecting a cell inside the data range

Excel may not recognize the correct data range and filtering won't work properly

Click any cell inside your data table before applying filter

Sorting only one column without selecting the entire table

This can mix up rows and cause data to mismatch across columns

Use the Sort options from the Data tab which sort entire rows based on the selected column

Summary

Filtering lets you hide rows that don't match your criteria so you see only what matters.

Sorting arranges your data in order, like A to Z or smallest to largest, to find things faster.

Always select a cell inside your data before filtering or sorting to avoid errors.

Practice

1. What does the FILTER function do in Excel?

easy

A. It sorts data alphabetically or numerically.

B. It changes the color of cells based on rules.

C. It adds numbers in a range.

D. It returns only the rows that meet a specific condition.

Solution

1. Step 1: Understand FILTER function purpose

   The FILTER function is designed to return rows from a range that meet a given condition.

2. Step 2: Compare with other options

   Sorting is done by SORT, summing by SUM, and coloring by Conditional Formatting, not FILTER.

3. Final Answer:

   It returns only the rows that meet a specific condition. -> Option D

4. Quick Check:

   FILTER selects rows by condition [OK]

Hint: FILTER picks rows matching your rule quickly [OK]

Common Mistakes:

• Confusing FILTER with SORT
• Thinking FILTER sums values
• Mixing FILTER with formatting features

2. Which of these is the correct syntax to filter rows where column A equals 10 using FILTER?

easy

A. =FILTER(A1:B10, A1:A10=10)

B. =FILTER(A1:B10, B1:B10=10)

C. =FILTER(A1:B10, A1:B10=10)

D. =FILTER(A1:A10, B1:B10=10)

Solution

1. Step 1: Identify correct range and condition

   The range to return is A1:B10, and the condition is on column A (A1:A10=10).

2. Step 2: Check each option's condition range

   =FILTER(A1:B10, A1:A10=10) correctly uses A1:A10=10 as condition; others use wrong columns or ranges.

3. Final Answer:

   =FILTER(A1:B10, A1:A10=10) -> Option A

4. Quick Check:

   FILTER(range, condition on column A) [OK]

Hint: Condition range must match the filter column [OK]

Common Mistakes:

• Using wrong column for condition
• Mismatching ranges in FILTER
• Filtering only one column but returning multiple

3. Given this data in A1:B5:
Name | Score
Alice | 85
Bob | 90
Carol | 75
Dave | 90
Eve | 80
What is the result of =SORT(FILTER(A2:B6, B2:B6=90), 1, TRUE)?

medium

A. Bob 90Dave 90

B. Dave 90Bob 90

C. Alice 85Eve 80

D. Carol 75Alice 85

Solution

1. Step 1: Apply FILTER to select rows with Score=90

   Rows with 90 are Bob and Dave.

2. Step 2: SORT filtered rows by column 1 (Name) ascending

   Names sorted alphabetically: Bob, then Dave.

3. Final Answer:

   Bob 90
   Dave 90 -> Option A

4. Quick Check:

   FILTER then SORT by name ascending [OK]

Hint: Filter first, then sort by desired column [OK]

Common Mistakes:

• Sorting before filtering
• Sorting by wrong column number
• Including rows with wrong scores

4. What is wrong with this formula to filter scores above 80 and sort descending by score?
=SORT(FILTER(A2:B10, B1:B9>80), 2, FALSE)

medium

A. The SORT function is missing the sort order argument.

B. The FILTER condition should use >= instead of >.

C. The FILTER range and condition ranges do not match.

D. The SORT column index should be 1, not 2.

Solution

1. Step 1: Check ranges in FILTER

   The FILTER array is A2:B10 (rows 2-10), but the condition range is B1:B9 (rows 1-9). The ranges do not match in size.

2. Step 2: Check SORT parameters

   SORT sorts by column 2 (score) descending (FALSE), which is correct.

3. Step 3: Eliminate other options

   >80 correctly filters above 80; SORT has the sort order argument; column 2 is correct for scores.

4. Final Answer:

   The FILTER range and condition ranges do not match. -> Option C

5. Quick Check:

   FILTER range and condition ranges do not match [OK]

Hint: Check that ranges in FILTER condition match output range [OK]

Common Mistakes:

• Using wrong column index in SORT
• Forgetting sort order argument
• Mismatching ranges in FILTER

5. You have a table with columns: Product (A), Category (B), and Price (C). You want to list all products in category "Fruit" sorted by price low to high. Which formula achieves this?

hard

A. =FILTER(SORT(A2:C100, 3, TRUE), B2:B100="Fruit")

B. =SORT(FILTER(A2:C100, B2:B100="Fruit"), 3, TRUE)

C. =FILTER(A2:C100, C2:C100>"Fruit")

D. =SORT(A2:C100, 2, TRUE)

Solution

1. Step 1: Filter rows where Category equals "Fruit"

   Use FILTER with condition B2:B100="Fruit" to get only fruit products.

2. Step 2: Sort filtered results by Price (column 3) ascending

   Use SORT on filtered data by column 3 (Price) with TRUE for ascending order.

3. Final Answer:

   =SORT(FILTER(A2:C100, B2:B100="Fruit"), 3, TRUE) -> Option B

4. Quick Check:

   Filter by category then sort by price ascending [OK]

Hint: Filter first, then sort by price column [OK]

Common Mistakes:

• Sorting before filtering
• Filtering with wrong condition
• Sorting by wrong column