UNIQUE, FILTER and SORT: modern Excel formulas that return whole lists at once.
4 articles
#SPILL! means a formula that returns several results can't place them, usually because a cell in the way isn't empty. Click the formula, then the warning icon, to see the reason. Clear or move whatever blocks the spill range, unmerge merged cells, move the formula out of an Excel table, or use a smaller range instead of a whole column.
SORT returns a sorted copy of a range: =SORT(A2:C9,3,-1) sorts by the 3rd column, largest first. SORTBY sorts one range by the values in another, so the column you sort by doesn't have to be in the result, and it handles several sort levels. Both leave the original data unchanged and need Excel 2021, Excel 2024 or Microsoft 365.
FILTER returns every row of a range that passes a test: =FILTER(A2:C9,B2:B9="East"). The matching rows spill into the cells below and update when the data changes. For AND, multiply tests in brackets; for OR, add them. The third argument shows a message when nothing matches. FILTER needs Excel 2021, Excel 2024 or Microsoft 365.
The UNIQUE function returns each different value in a range once, in the order it first appears: =UNIQUE(C2:C9). Type it in one cell and the results spill into the cells below. Wrap it in COUNTA to count distinct values, or in SORT to put them in order. UNIQUE needs Excel 2021, Excel 2024 or Microsoft 365.