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 reorders rows. Data → Sort lets you sort by several columns in turn: region A→Z, then within each region, revenue largest first (use Add Level).

Filtering hides rows that don't match, without deleting them. Turn filters on with Ctrl + Shift + L (Tables have them already). Each column's drop-down offers:

• tick-boxes for specific values;
• Number Filters (Greater Than, Top 10…);
• Date Filters (This Month, Between…), and dates grouped by year and month in the list.

Counting what you see. SUM and COUNT include hidden rows. SUBTOTAL ignores rows hidden by a filter:

=SUBTOTAL(9, Orders[revenue])

The first argument picks the calculation: 9 = sum, 3 = count of non-empty cells, 1 = average. A Table's Total Row (Table Design → Total Row) uses SUBTOTAL automatically.

The FILTER function (Excel 365/2021 and Google Sheets) returns matching rows as a new range, so your original data stays untouched:

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

03Example

To find 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 at ₦24,600 on 27 June 2026, worth ₦713,400.

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 "X of 4266 records found". Or turn on the Table's Total Row and set the order_id total to Count.
3. Clear the filter afterwards: Data → Clear.

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.

Shortcuts for sorting and filtering

05Practice

06Challenge

07Check your understanding