FILTER, SORT and UNIQUE

Most formulas return one value. Spilling formulas return a whole table, which fills the cells below and to the right. Type one formula, get a live report that grows and shrinks with the data.

• FILTER(range, condition1, condition2, ...) keeps the rows where every condition is TRUE. =FILTER(A2:D9, C2:C9="West", D2:D9>10000).
• SORT(range, column, is_ascending) orders rows. The column number counts from the left of the range. TRUE is A to Z or smallest first, FALSE the reverse.
• UNIQUE(range) lists each distinct value once. COUNTUNIQUE(range) just counts them.

They nest. =SORT(FILTER(A2:D9, C2:C9="West"), 4, FALSE) filters, then sorts what's left. =SORT(UNIQUE(B2:B200)) builds a clean list for a dropdown.

Three things to know:

• Leave room. If anything sits where the results need to go, you get #REF! with a note that the array result would overwrite data. Clear the cells and it spills.
• Empty results are an error. FILTER with no matches returns #N/A. IFERROR(FILTER(...), "None") handles it.
• OR inside one condition. Separate conditions mean AND. For OR, add them: (C2:C9="West")+(C2:C9="East").

Excel has FILTER and SORT too, with small differences: Excel's FILTER takes one condition and SORT uses 1 and -1 instead of TRUE and FALSE.