Sorting and filtering

01The problem

A sales manager asks three quick questions: What was our single biggest order line? How many lines got the full 10% discount? How busy was December? Each answer is buried somewhere in 4,266 rows. Sorting and filtering bring the right rows to the top.

02The concept

Sorting changes the order of the rows. Filtering hides the rows you don't want, for now, without deleting anything. Between them, they answer most "show me…" questions in seconds.

This lesson uses the Orders table with the revenue column you added in lesson 2.

Sorting by one column

Click any cell in the column, then:

Excel names the options by what's in the column: A to Z for text, Smallest to Largest for numbers, Oldest to Newest for dates.

Sort revenue largest to smallest, and the top three order lines are:

Sorting by several columns

To sort by one column and then, within ties, by another, use the Sort dialog: Data → Sort (Alt, A, S, S).

1. Sort by order_date, Order Newest to Oldest.
2. Click Add Level. Then by revenue, Largest to Smallest.
3. OK.

Now the most recent day comes first, and within each day the biggest lines are at the top. Excel applies the levels in order: the second level only decides the order of rows that tie on the first.

Two more options in the Sort dialog:

• Custom List (in the Order box): sort by an order that isn't alphabetical, such as Small, Medium, Large or the days of the week.
• Sort On: Cell Color: bring highlighted rows to the top.

Filtering

Filtering hides rows that don't match, so you can look at a slice. Nothing is deleted: clear the filter and every row comes back.

Turn filters on with Ctrl + Shift + L (or Data → Filter). Tables have them already: the small arrow in each header.

Each column's drop-down offers three kinds of filter, depending on what's in the column:

Some results on Kolanut's orders:

Filters on several columns combine with AND. Filter discount_pct to 10 and quantity to Greater Than or Equal To 20, and you see the 218 lines that pass both.

How to tell a filter is on

Filters are easy to forget, and a forgotten filter gives wrong totals. Three signs:

1. The column's arrow becomes a funnel icon.
2. The row numbers turn blue and skip (2, 5, 9…), because rows in between are hidden.
3. The status bar says "525 of 4266 records found".

Clear one column's filter from its drop-down (Clear Filter From…), or all of them with Data → Clear (Alt, A, C).

Totals that respect the filter: SUBTOTAL

SUM, COUNT and AVERAGE include hidden rows. With a filter on, =SUM(Orders[revenue]) still shows ₦830,541,245. To total only what's visible, use SUBTOTAL:

=SUBTOTAL(function_num, ref1, ...)

With discount_pct filtered to 10:

A Table's Total Row (Table Design → tick Total Row) adds a row under the table with a drop-down in each column: Sum, Count, Average… It uses SUBTOTAL, so it always follows the filter.

The FILTER function

Filtering with the drop-downs changes what you see. The FILTER function copies matching rows to another place, so the original stays untouched and the result updates when the data changes. (Excel 365 and 2021 onwards, and Google Sheets.)

=FILTER(array, include, [if_empty])

=FILTER(Orders, Orders[discount_pct]=10, "none")

Type it in one empty cell on another sheet and the 525 matching rows spill into the cells below and to the right. (Copy the headers yourself: FILTER returns only the data.)

For several conditions, multiply the tests for AND and add them for OR. Each test must be in its own brackets:

=FILTER(Orders, (Orders[discount_pct]=10) * (Orders[quantity]>=20))   → 218 rows: both
=FILTER(Orders, (Orders[discount_pct]=10) + (Orders[quantity]>=25))   → 982 rows: either

SORT and SORTBY

Like FILTER, these return a sorted copy:

=SORT(array, [sort_index], [sort_order])
=SORT(Orders, 8, -1)                          → the whole table by column 8 (revenue), largest first
=SORTBY(Orders, Orders[revenue], -1)          → the same, naming the column

sort_order is 1 for ascending and -1 for descending. They combine with FILTER, working from the inside out:

=SORTBY(FILTER(Orders, Orders[discount_pct]=10), FILTER(Orders[revenue], Orders[discount_pct]=10), -1)

That's every 10%-discount line, biggest first. For one-off looks the drop-downs are quicker; for a report that should update itself, use the functions.

03Example

What is the biggest single order line? Click the revenue drop-down → Sort Largest to Smallest. The top row is order line 14243: 29 packs of Body lotion 400ml (product 13) at ₦24,600 on 27 June 2026, worth ₦713,400.

How much did the 10% discount lines bring in? Filter discount_pct to 10. The status bar shows 525 of 4,266 records. Select the revenue column: the status bar's Sum shows ₦122,835,600 (the status bar, like SUBTOTAL, adds only visible cells). An average of ₦233,973 per line, against ₦194,689 for all lines.

04Walkthrough

This is what filtering discount_pct to 10 looks like:

1. The filter button on the column header. Once a filter is on, it shows a funnel icon.
2. Number Filters: conditions like Greater Than or Top 10. Text columns show Text Filters; date columns show Date Filters.
3. The value list: tick the values to keep. Use the search box above it for long lists.
4. The status bar reports the result: 525 of 4266 records found.

How many lines had a 10% discount?

1. Click the discount_pct drop-down, untick Select All, tick 10, OK.
2. The status bar at the bottom of the window shows "525 of 4266 records found". Or turn on the Table's Total Row and set the order_id total to Count.
3. Under the table, or on another sheet, type =SUBTOTAL(9, Orders[revenue]). It should show 122,835,600.
4. Clear the filter: Data → Clear. The SUBTOTAL changes back to 830,541,245.

How many lines in December 2025?

1. Open the order_date drop-down. Dates are grouped: expand 2025, untick everything except December.
2. Read the count the same way: 334.

The ten biggest lines.

1. Clear all filters.
2. revenue drop-down → Number Filters → Top 10… → Top 10 Items → OK.
3. Sort the result largest to smallest. The smallest of the ten is ₦641,915.

Shortcuts for sorting and filtering

Summary

05Practice

06Challenge

07More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

08Check your understanding