SORT, SORTBY, FILTER and UNIQUE Functions in Excel

Part of the free Module 4: Sort and Filter · Lesson 9 of 11 · Full Excel course

Updated on 5 September 2026 · Requires Excel 365 or Excel 2021 (dynamic arrays). In Excel 2019 and 2016 these functions return #NAME? and you should use the Sort and Filter commands, Advanced Filter or a pivot table instead.

The SORT and FILTER functions in Excel return a sorted or filtered copy of a range as a formula result, so the output updates automatically whenever the source data changes. Together with SORTBY and UNIQUE they replace the manual Sort, AutoFilter and Remove Duplicates commands with live, chainable formulas that leave the original data untouched.

Why formulas beat the Sort and Filter commands

The Data tab commands you learned in the earlier lessons rearrange or hide the source rows themselves. That is fine for a one-off tidy-up, but it has three weaknesses: the result goes stale the moment someone adds a row, the original order is lost unless you kept a helper column, and you cannot build a report on top of it without copying and pasting.

The dynamic array functions solve all three problems:

Results update automatically. Change a value, add a row to the source Table, and the sorted or filtered list recalculates.
The source is never touched. The output lives in a separate range, so the raw data keeps its order and every row stays visible.
They can be chained. FILTER inside SORT, UNIQUE inside SORT, TAKE around SORT: each function returns an array that the next one consumes.

Spill ranges and the # operator

When a formula returns more than one value, Excel 365 writes the results into neighbouring cells. This block is called the spill range and it is outlined with a thin blue border while a cell in it is selected. Only the top-left cell holds the formula; the other cells show grey text in the formula bar and cannot be edited on their own.

To refer to a whole spill range, add # to the top-left cell address: =COUNTA(E2#) counts every row the formula in E2 spilled. If the cells the result needs are not empty, Excel shows #SPILL! and a dashed outline of the blocked area. Clear the obstruction and the formula spills normally.

SORT function: sort a range with a formula

Syntax: =SORT(array, [sort_index], [sort_order], [by_col]). Only array is required. sort_index is the column number to sort by (default 1), sort_order is 1 for ascending or -1 for descending, and by_col is FALSE to sort rows (default) or TRUE to sort columns left to right.

Single column, ascending: =SORT(A2:A50)
Descending: =SORT(A2:A50,,-1) (the empty argument keeps sort_index at 1)
Sort a block by its third column: =SORT(A2:D50,3,-1) sorts the whole block by Sales, highest first
Left to right: =SORT(B1:M2,2,1,TRUE) reorders month columns by the values in row 2

SORT sorts numbers before text in ascending order, and blank cells always go to the bottom whichever direction you choose. Text sorts alphabetically and is not case-sensitive.

SORTBY function: sort by another column or by a custom order

Syntax: =SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...). SORTBY sorts array using one or more separate ranges as the keys, so the key does not need to be part of the output and you can use as many keys as you like.

Return names sorted by sales: =SORTBY(A2:A50,D2:D50,-1)
Two keys, Region A-Z then Sales high to low: =SORTBY(A2:D50,B2:B50,1,D2:D50,-1)
Custom order using MATCH: =SORTBY(A2:D50,MATCH(B2:B50,{"North","South","East","West"},0)) sorts regions in the order listed rather than alphabetically. You can replace the constant with a range that holds your custom list.

FILTER function: return only the rows that match

Syntax: =FILTER(array, include, [if_empty]). include is a TRUE/FALSE array the same height as array; if_empty is what to show when nothing matches. Combine conditions with * for AND and + for OR, because multiplying and adding TRUE/FALSE values gives 1 or 0.

Single condition: =FILTER(A2:D50,B2:B50="North")
AND: =FILTER(A2:D50,(B2:B50="North")*(D2:D50>1000))
OR: =FILTER(A2:D50,(B2:B50="North")+(B2:B50="South"))
Contains text: =FILTER(A2:D50,ISNUMBER(SEARCH("pro",C2:C50))) keeps rows whose Product contains “pro” in any case
Date range: =FILTER(A2:D50,(E2:E50>=DATE(2026,1,1))*(E2:E50<=DATE(2026,3,31)))
if_empty text: =FILTER(A2:D50,B2:B50=G1,"No matches") avoids #CALC! when the criteria cell G1 finds nothing

UNIQUE function: a distinct list without Remove Duplicates

Syntax: =UNIQUE(array, [by_col], [exactly_once]). By default it returns each distinct value once, comparing rows. Set by_col to TRUE to compare columns, and exactly_once to TRUE to return only values that appear a single time.

Distinct list: =UNIQUE(B2:B50) gives every region once
Values that appear exactly once: =UNIQUE(B2:B50,,TRUE)
Unique rows across several columns: =UNIQUE(A2:C50) treats each Name, Region, Product combination as one row and removes exact repeats

Chaining SORT, FILTER, UNIQUE and TAKE

Each function returns an array, so you can nest them. Read nested formulas from the inside out.

Filtered and sorted: =SORT(FILTER(A2:D50,B2:B50="North"),4,-1)
Sorted distinct list: =SORT(UNIQUE(B2:B50)) is the standard drop-down source
Top 5 with TAKE (Excel 365 only): =TAKE(SORT(A2:D50,4,-1),5)
Top 5 with INDEX (also Excel 2021): =INDEX(SORT(A2:D50,4,-1),SEQUENCE(5),{1,2,3,4})

Using them with Excel Tables so the source grows

Point the functions at structured references instead of fixed addresses and new rows are picked up automatically. If your data is in a Table named tblSales, write =SORT(tblSales,4,-1) or =FILTER(tblSales,tblSales[Region]="North"). The formula itself must sit outside the Table, because a Table cannot contain a spilling formula. Converting data to a Table is covered in Sort and Filter with Excel Tables.

Syntax and arguments at a glance

Function	Syntax	Required	Optional arguments
SORT	`SORT(array,[sort_index],[sort_order],[by_col])`	array	sort_index (default 1), sort_order 1 or -1, by_col TRUE/FALSE
SORTBY	`SORTBY(array,by_array1,[order1],[by_array2,order2],...)`	array, by_array1	order 1 or -1 per key; up to 126 key pairs
FILTER	`FILTER(array,include,[if_empty])`	array, include	if_empty: value returned when no rows match
UNIQUE	`UNIQUE(array,[by_col],[exactly_once])`	array	by_col TRUE/FALSE, exactly_once TRUE/FALSE

Worked example: a small sales table

Sample data in A1:D7, with the header row in row 1:

Name (A)	Region (B)	Product (C)	Sales (D)
Asha	North	Laptop	1200
Ben	South	Monitor	450
Chen	North	Printer	300
Dana	East	Laptop	1500
Eli	South	Laptop	980
Fatima	North	Monitor	620

Enter each formula in a blank area, for example F2, with enough empty cells below and to the right.

=SORT(A2:D7,4,-1)
Result rows: Dana 1500, Asha 1200, Eli 980, Fatima 620, Ben 450, Chen 300

=FILTER(A2:D7,(B2:B7="North")*(D2:D7>500),"None")
Result rows: Asha North Laptop 1200, Fatima North Monitor 620

=SORT(UNIQUE(C2:C7))
Result: Laptop, Monitor, Printer

=SORTBY(A2:A7,B2:B7,1,D2:D7,-1)
Result: Dana, Asha, Fatima, Chen, Eli, Ben

=TAKE(SORT(A2:D7,4,-1),3)
Result rows: Dana 1500, Asha 1200, Eli 980

Change Chen’s sales to 2000 and every result reorders on its own. That is the difference between a formula and the Sort command.

Tips and common mistakes

#SPILL! usually means the cells below are not empty. Click the error flag, choose Select Obstructing Cells and clear or move them.
Tables cannot hold spilling formulas. Put the SORT or FILTER formula on a report sheet, not inside the Table it reads.
FILTER arrays must be the same height. FILTER(A2:D50,B2:B49="x") fails with #VALUE! because the include range is one row short.
Use $ or Table references. If you copy a formula that uses relative ranges, the ranges shift. Structured references never move.
Always supply if_empty in FILTER. Without it an empty result shows #CALC!, which looks like a broken formula to users.
SORT is not case-sensitive and orders numbers before text. Blanks always sort last, in both directions.
Do not select the output range first. Type the formula in a single cell and press Enter; Excel sizes the spill itself. Selecting a block and pressing Ctrl+Shift+Enter is the old array method.

Errors and how to fix them

Error	Cause	Fix
#SPILL!	Cells the result needs are occupied, merged, or inside a Table	Clear the obstructing cells, unmerge, or move the formula outside the Table
#CALC!	FILTER found no matching rows and no if_empty was given	Add a third argument such as “No data”
#VALUE!	FILTER include array is a different height from array, or an argument is not a valid range	Make both ranges cover the same rows; check sort_index is within the column count
#NAME?	Excel 2019, 2016 or older does not know the function	Upgrade to 365 or 2021, or use Sort, AutoFilter, Advanced Filter or a pivot table
#N/A	Spill formula referenced with # in a version that cannot spill, or MATCH in a custom-order SORTBY found no match	Open the file in Excel 365/2021; check every value exists in the custom list

Practice exercise

Open the course practice file Sort-and-Filter.xlsx in Excel 365 or 2021 and work on a blank sheet.

Write a SORT formula that lists the whole data range by the amount column, highest first.
Write a FILTER formula that returns only rows for one region with an amount above 500, showing “No rows” when nothing matches.
Build a sorted distinct list of the category column with =SORT(UNIQUE(...)).
Use SORTBY with two keys: region ascending, then amount descending.
Return the top three rows by amount with TAKE, or with INDEX and SEQUENCE if you are on Excel 2021.

Key takeaways

SORT, SORTBY, FILTER and UNIQUE return live results that update when the source changes and never alter the original rows.
They need Excel 365 or 2021; older versions show #NAME?.
Combine conditions in FILTER with * for AND and + for OR, and always give an if_empty value.
Refer to a spill range with the # operator, and keep the cells below the formula empty to avoid #SPILL!.
Nest the functions and feed them Table references so reports grow with the data.

Related lessons

Frequently asked questions

Why do SORT and FILTER return #NAME? in my Excel?

SORT, SORTBY, FILTER and UNIQUE were added with dynamic arrays in Excel 365 and are also in Excel 2021. Excel 2019, 2016 and earlier do not recognise the names, so they show #NAME?. In those versions use the Sort and Filter commands on the Data tab, Advanced Filter with a criteria range, or a pivot table.

What is the difference between SORT and SORTBY?

SORT orders a range by one of its own columns, chosen by index number. SORTBY orders a range by one or more separate ranges that do not need to be in the output, and supports several keys in a single call. Use SORT for simple cases and SORTBY when you need multiple keys or a custom order.

How do I filter with multiple conditions in Excel?

Multiply the conditions for AND, for example (B2:B50="North")*(D2:D50>1000), or add them for OR, for example (B2:B50="North")+(B2:B50="South"). Each comparison gives TRUE or FALSE, and the arithmetic converts them into 1 or 0 that FILTER treats as include or exclude.

Can I put a FILTER formula inside an Excel Table?

No. A Table cannot contain a formula that spills into more than one cell, so Excel shows #SPILL!. Keep the Table as the source and write the FILTER, SORT or UNIQUE formula on a normal range elsewhere, ideally using structured references such as tblSales[Region] so new rows are included.

How do I get the top 10 values with SORT?

Sort descending and keep the first rows: =TAKE(SORT(A2:D50,4,-1),10) in Excel 365. In Excel 2021, which lacks TAKE, use =INDEX(SORT(A2:D50,4,-1),SEQUENCE(10),{1,2,3,4}) to return the first ten rows and four columns of the sorted array.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.