SORT and FILTER Functions in Google Sheets with an Example

The SORT and FILTER functions in Google Sheets are a strong pair: FILTER keeps only the rows that meet a condition, and SORT puts those rows in the order you want. Together they build live “Top 5” and “Bottom 5” tables that update the moment the source data changes.

This guide is for sales managers, team leads and analysts who want a leaderboard without copying and pasting. Using a list of 17 sales agents from the practice sheet, you will build a Top 5 and a Bottom 5 table, see the exact output, learn why the sort-order argument matters, and pick up variations with SORTN, QUERY and range conditions.

Open the Free Practice Sheet

SORT and FILTER Syntax

=FILTER(range, condition1, [condition2, ...])
=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])

Two helper functions set the cut-off: LARGE(range, n) returns the nth largest value and SMALL(range, n) the nth smallest.

The Example Data

The practice sheet has 17 agents in A4:B20 under the headers Sales Agent and Sales, with empty Top 5 Sales and Bottom 5 Sales tables on the right.

The sales values range from Cienna’s 2094 to Darnell’s 4976. The 5th largest value is 3995 (Essa) and the 5th smallest is 2546 (Indigo); these are the cut-offs LARGE and SMALL will find.

Top 5 Sales Agents

=SORT(FILTER(A4:B20,B4:B20>=LARGE(B4:B20,5)),2,FALSE)

1. LARGE(B4:B20,5) returns 3995, the 5th highest sale.
2. FILTER(A4:B20, B4:B20>=3995) keeps the five agents at or above that value.
3. SORT(…,2,FALSE) sorts them by column 2 (Sales), largest first.

Bottom 5 Sales Agents

=SORT(FILTER(A4:B20,B4:B20<=SMALL(B4:B20,5)),2,TRUE)

1. SMALL(B4:B20,5) returns 2546, the 5th lowest sale.
2. FILTER keeps the agents at or below 2546.
3. SORT(…,2,TRUE) lists them smallest first, so the weakest result is on top.

Why use TRUE or FALSE, not -1

The third argument of SORT is a TRUE/FALSE switch. A number such as -1 is read as TRUE, which means ascending. That is why a Top 5 built with SORT(...,2,-1) lists Essa (3995) first and Darnell (4976) last, as in the screenshot. Use FALSE for largest-first and TRUE for smallest-first.

Practical Variations

1. Top 5 in one function with SORTN

=SORTN(A4:B20,5,0,2,FALSE)

SORTN sorts and limits the rows in one step and returns the same Darnell-to-Essa list. Change the third argument to 1 if you want to include every agent tied with the 5th value.

2. The same table with QUERY

=QUERY(A3:B20,"select A, B order by B desc limit 5",1)

QUERY returns the header row plus the top five. It is convenient if you already use QUERY elsewhere in the workbook.

3. Agents above the team average

=SORT(FILTER(A4:B20,B4:B20>AVERAGE(B4:B20)),2,FALSE)

The average of the 17 values is about 3352, so this returns seven agents: Darnell, Zayn, Lenny Fritz, Courtney, Essa, Sidrah and Aden.

4. Sales within a band

=SORT(FILTER(A4:B20,B4:B20>=3000,B4:B20<4000),2,FALSE)

Two conditions separated by a comma must both be true. This returns seven agents from Essa (3995) down to Briana (3017).

5. Make “5” a setting

=SORT(FILTER(A4:B20,B4:B20>=LARGE(B4:B20,$G$1)),2,FALSE)

Put the number of rows you want in G1. Typing 3 or 10 there changes the size of the leaderboard without editing the formula.

Common Errors and Fixes

Tips

• Keep result tables on a separate area or tab so their spill range never collides with source data.
• Use open ranges like A4:B and B4:B so new agents are picked up automatically. Blank cells count as 0, so they fail a “greater than” test but pass a “less than” test; for a Bottom 5 on an open range add a second condition, B4:B<>"", to skip empty rows.
• Add a header row above each result table by hand; FILTER and SORT return data only.
• Keep the source list clean: numbers stored as text (often from imports) are skipped by LARGE and SMALL, which can push the wrong agent into a top or bottom list.

Related tutorials: FILTER and LARGE in Google Sheets, FILTER and SMALL step by step, SORT and UNIQUE functions, FILTER with ISBLANK and the RANK function in Google Sheets when you want a rank number next to each row instead of a separate table.

If you want structured lessons on dynamic array formulas such as FILTER, SORT and lookups, the Excel AI Mastery: 100 Formulas course on NextGenTemplates Academy covers them with practice files; Excel’s FILTER and SORT behave very similarly.

Frequently Asked Questions

How do I sort in descending order with the SORT function?

Set the third argument to FALSE, for example =SORT(A4:B20,2,FALSE). TRUE, or any non-zero number, sorts ascending.

Can FILTER use more than one condition?

Yes. Separate conditions with commas for AND logic, as in the 3000 to 4000 band example. For OR logic, add the conditions together inside one argument: (B4:B20>4500)+(B4:B20<2200).

What is the difference between SORT(FILTER()) and SORTN?

SORT with FILTER returns every row that meets a condition, which can be more than five if values tie. SORTN returns a fixed number of rows and has options for how ties are handled.

Why does my FILTER formula show #REF!?

The results need empty cells to spill into. If any cell in the output area has content, Google Sheets shows #REF!. Clear that area and the results appear.

Will the Top 5 table update automatically?

Yes. Both functions recalculate whenever the source data changes, so editing a sales value or adding an agent within the range updates the tables immediately.

Wrapping Up

FILTER picks the rows and SORT orders them. With LARGE and SMALL as cut-offs, two formulas produced a Top 5 led by Darnell’s 4976 and a Bottom 5 starting at Cienna’s 2094, and the same pattern handles above-average lists and value bands. Remember to use FALSE for largest-first. Make a copy of the practice sheet with File > Make a copy and rebuild both tables yourself.

Open the Free Practice Sheet

📅 Last updated: October 2026