{ }Advanced

FILTER, UNIQUE and SORT practice exercises

Modern Excel formulas can return a whole list that spills into the cells below. Practice FILTER, UNIQUE and SORTBY on an inventory table, and combine them with SUM for one-cell answers.

11 exercises0/11
Start practicing →

Try it yourself

A live spreadsheet. Type a formula and press Enter to check it.

Exercise 1 of 11FILTER

List every product in the Furniture category.

Write your formula in the green cell, H2.

Dynamic arrays

Filter a listUnique valuesOut of stockFILTER by a numberCount unique valuesSort by priceTop 3 with TAKEStock value with FILTERFILTER with ANDFILTER with ORName parts with LET
inventory.xlsx
fx
1SKUProductCategoryPriceStock
2P-01Desk lampLighting$39.00120Furniture
3P-02Office chairFurniture$189.0014
4P-03Standing deskFurniture$449.000
5P-04Monitor armAccessories$79.0056
6P-05Floor lampLighting$89.000
7P-06BookshelfFurniture$129.0031
8P-07Cable trayAccessories$25.00210
9P-08Desk matAccessories$19.000
10
11

Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.

All 11 dynamic arrays exercises

  1. 1

    Filter a list

    Free

    List every product in the Furniture category.

  2. 2

    Unique values

    Free

    List each category once.

  3. 3

    Out of stock

    Premium

    List the products that are out of stock (Stock is 0).

  4. 4

    FILTER by a number

    Premium

    List every product that costs under $50.

  5. 5

    Count unique values

    Premium

    How many different categories are there? Use one formula that still works when new categories are added.

  6. 6

    Sort by price

    Premium

    List all product names sorted by price, most expensive first.

  7. 7

    Top 3 with TAKE

    Premium

    List the 3 most expensive products, most expensive first.

  8. 8

    Stock value with FILTER

    Premium

    What's the total stock value (Price × Stock) of Accessories? Use FILTER.

  9. 9

    FILTER with AND

    Premium

    List the Accessories that are in stock (Stock more than 0).

  10. 10

    FILTER with OR

    Premium

    List every product that is in Lighting or is out of stock.

  11. 11

    Name parts with LET

    Premium

    Use LET to work out the total stock value (Price × Stock) of Furniture.

The dynamic arrays functions, with examples

Every example runs on inventory.xlsx, the same sheet the exercises use, and the results are calculated from that data.

  • FILTER

    Returns every row that meets a condition, spilling the results into the cells below. Excel 2021 and later.

    FILTER(array, include, [if_empty])

    Example

    =FILTER(B2:B9, E2:E9=0)returnsStanding deskFloor lampDesk mat

  • UNIQUE

    Lists each distinct value once, in the order they first appear.

    UNIQUE(array, [by_col], [exactly_once])

    Example

    =UNIQUE(C2:C9)returnsLightingFurnitureAccessories

  • SORTBY

    Sorts one range by the values in another, without changing the source data. Use -1 for largest first.

    SORTBY(array, by_array1, [sort_order1], …)

    Example

    =SORTBY(B2:B9, D2:D9, -1)returnsStanding deskOffice chairBookshelfFloor lampMonitor armDesk lampCable trayDesk mat

  • LET

    Gives a value a name inside a formula so you can reuse it. Long formulas get shorter and easier to read, and the value is only calculated once.

    LET(name1, value1, [name2, value2, …], calculation)

    Example

    =LET(value, D2:D9*E2:E9, SUM(value))returns$20,999.00

Common mistakes

  1. 1

    Being stopped by #SPILL!

    A formula that returns a list needs empty cells to spill into. #SPILL! means something is in the way: clear the cells below or beside the formula, or move it.

  2. 2

    Not planning for no matches

    When nothing meets the condition, FILTER returns #CALC!. Give it the third argument for that case: =FILTER(B2:B9,E2:E9=0,"None").

  3. 3

    Pointing at a fixed range instead of the spill

    If a list spills from G2, refer to all of it as G2#. =COUNTA(G2#) keeps counting correctly as the list grows; G2:G10 misses anything past row 10.