Advanced Excel · Beginner level

Beginner level · Module 3: Dynamic Array Formulas · Lesson 1 of 3

FILTER, SORT and UNIQUE

Return whole result lists with one formula that spills into neighbouring cells.

In this lesson you will

  • Use FILTER with one or more conditions
  • Sort and de-duplicate results

Key concepts

spill range#SPILL!FILTERSORT and UNIQUE

The lesson

Dynamic array formulas return several values that spill into the cells below and to the right. If something blocks the spill area, Excel shows #SPILL! until the cells are cleared.

FILTER returns the rows that meet a condition: =FILTER(Sales, Sales[Branch]="Juba"). Combine conditions with * for AND and + for OR.

SORT orders the result by a column, and UNIQUE returns distinct values, such as the list of branches. Functions can be nested: =SORT(FILTER(...), 3, -1).

Step by step

  1. Filter sales for one branch.
  2. Sort the result by amount, largest first.
  3. List unique branches.
  4. Resolve a #SPILL! error.
Code example

A branch report that builds itself with FILTER, SORT and UNIQUE

Three dynamic array formulas list one branch's sales from largest to smallest, total them and list every branch.

Excel formulaBranch report sheet
' Sales table: Date | Branch | Amount'   01-Mar | Juba    | 14000'   01-Mar | Wau     |  5600'   02-Mar | Juba    |  4800'   03-Mar | Juba    |  8400'   03-Mar | Malakal |  7000 B1: Juba                                         ' selected branch (drop-down)A3: =SORT(FILTER(Sales, Sales[Branch]=B1), 3, -1)E3: =SUM(FILTER(Sales[Amount], Sales[Branch]=B1))G3: =UNIQUE(Sales[Branch])

Output

A3 spills:  01-Mar  Juba  14000
            03-Mar  Juba   8400
            02-Mar  Juba   4800
E3:         27200
G3 spills:  Juba
            Wau
            Malakal

How it works

FILTER keeps only the rows whose branch matches the drop-down in B1, and SORT orders them by the third column (Amount) in descending order (−1). The second formula filters just the Amount column and sums it: 14,000 + 8,400 + 4,800 = 27,200. UNIQUE lists each branch once in order of first appearance, which can feed the drop-down. Change B1 to Wau and every result updates instantly — no copying formulas. Requires Microsoft 365 or Excel 2021 and later; figures are illustrative.

Key concepts

FILTERSORT by column, descendingUNIQUEspill ranges driven by a selection cell

Practice activity

Create the filtered and sorted sales list for the branch report.

Expected result: A list that changes immediately when the branch selection changes.

Check your understanding

  1. What causes #SPILL!?

    Show answer

    Cells in the spill area are not empty.

  2. How do you combine two FILTER conditions with AND?

    Show answer

    Multiply them: (condition1)*(condition2).

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.