Bird
Raised Fist0
Excelspreadsheet~5 mins

Dynamic arrays (SORT, FILTER, UNIQUE) in Excel - Step-by-Step Guide

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Introduction
Dynamic arrays let you create formulas that spill results into multiple cells automatically. Functions like SORT, FILTER, and UNIQUE help you organize and extract data easily without copying formulas to many cells.
When you want to list unique customer names from a sales list without duplicates.
When you need to sort a list of product prices from lowest to highest automatically.
When you want to show only the sales records for a specific region from a big table.
When you want to create a dynamic dropdown list that updates as data changes.
When you want to quickly clean up data by removing repeated entries.
Steps
Step 1: Click
- cell where you want the results to start
The cell is selected and ready for formula input
Step 2: Type
- the selected cell
Formula is entered and ready to run
💡 Start with =SORT(range) to sort data, =FILTER(range, condition) to filter, or =UNIQUE(range) to get unique values
Step 3: Press
- Enter key
The formula runs and spills results into adjacent cells automatically
Step 4: Check
- cells below and to the right of the formula cell
You see the full list of sorted, filtered, or unique data without extra formulas
Step 5: Modify
- the formula if needed
Results update automatically when source data changes
Before vs After
Before
A column with repeated customer names like: John, Mary, John, Alex, Mary
After
A list showing unique customer names only: John, Mary, Alex
Settings Reference
Calculation Options
📍 Formulas tab > Calculation group > Calculation Options
Ensures formulas recalculate automatically so dynamic arrays update when data changes
Default: Automatic
Spill Range
📍 Formula cell and adjacent cells
Dynamic arrays need empty cells next to the formula cell to show all results
Default: Empty cells to spill into
Common Mistakes
Entering a dynamic array formula but cells next to it are not empty
Dynamic arrays need empty cells to spill results; blocked cells cause errors
Clear cells next to the formula cell before entering the formula
Using FILTER without a proper condition
FILTER needs a condition that returns TRUE or FALSE for each row to work
Write a condition like =FILTER(A2:A10, B2:B10="East") to filter by region East
Trying to edit part of the spilled array range
You can only edit the formula cell; other cells are output only
Edit the formula in the original cell to change the results
Summary
Dynamic arrays automatically fill multiple cells with results from one formula.
SORT, FILTER, and UNIQUE help organize and extract data easily without copying formulas.
Make sure cells next to the formula are empty so results can spill without errors.

Practice

(1/5)
1. What does the UNIQUE function do in Excel dynamic arrays?
easy
A. It removes duplicate values from a list and shows only unique items.
B. It sorts the list in ascending order.
C. It filters the list based on a condition.
D. It counts the number of items in a list.

Solution

  1. Step 1: Understand the purpose of UNIQUE

    The UNIQUE function scans a list and removes any repeated values, leaving only one of each item.

  2. Step 2: Compare with other functions

    SORT arranges items, FILTER picks items by condition, and COUNT counts items. UNIQUE specifically removes duplicates.

  3. Final Answer:

    It removes duplicate values from a list and shows only unique items. -> Option A

  4. Quick Check:

    UNIQUE = remove duplicates [OK]
Hint: UNIQUE = only one of each item shown [OK]
Common Mistakes:
  • Confusing UNIQUE with SORT or FILTER
  • Thinking UNIQUE counts items
  • Assuming UNIQUE sorts the list
2. Which of these is the correct syntax to sort the range A1:A10 in ascending order using dynamic arrays?
easy
A. =SORT(A1:A10)
B. =SORT(A1:A10, 2)
C. =SORT(A1:A10, 1, FALSE)
D. =SORT(A1:A10, TRUE)

Solution

  1. Step 1: Recall SORT function syntax

    The basic SORT syntax is =SORT(array, [sort_index], [sort_order]). If you want to sort by the first column ascending, you can omit optional arguments.

  2. Step 2: Analyze options

    =SORT(A1:A10) uses =SORT(A1:A10) which sorts ascending by default. =SORT(A1:A10, 2) uses 2 as sort_index but the range is one column only, so 2 is invalid. =SORT(A1:A10, 1, FALSE) uses FALSE for sort_order which means descending, not ascending. =SORT(A1:A10, TRUE) uses TRUE as second argument which is invalid position.

  3. Final Answer:

    =SORT(A1:A10) -> Option A

  4. Quick Check:

    Default SORT sorts ascending [OK]
Hint: SORT(range) sorts ascending by default [OK]
Common Mistakes:
  • Using wrong argument positions
  • Using sort_index beyond column count
  • Confusing TRUE/FALSE for sort_order
3. Given the list in cells A1:A6: {5, 3, 5, 2, 3, 1}, what is the result of =SORT(UNIQUE(A1:A6))?
medium
A. {1, 3, 5}
B. {5, 3, 2, 1}
C. {1, 2, 3, 5}
D. {5, 3, 5, 2, 3, 1}

Solution

  1. Step 1: Apply UNIQUE to the list

    UNIQUE removes duplicates from {5, 3, 5, 2, 3, 1}, resulting in {5, 3, 2, 1} but order is preserved as first occurrences.

  2. Step 2: Apply SORT to the unique list

    SORT arranges {5, 3, 2, 1} in ascending order: {1, 2, 3, 5}.

  3. Final Answer:

    {1, 2, 3, 5} -> Option C

  4. Quick Check:

    UNIQUE then SORT = sorted unique values [OK]
Hint: UNIQUE removes duplicates, SORT orders ascending [OK]
Common Mistakes:
  • Forgetting to sort after UNIQUE
  • Assuming UNIQUE sorts automatically
  • Mixing order of functions
4. The formula =FILTER(A1:A5, A1:A5>10) returns an error. What is the likely cause?
medium
A. FILTER requires a third argument for empty results.
B. The range A1:A5 contains text values that cause the comparison to fail.
C. The formula syntax is incorrect; >10 should be in quotes.
D. FILTER cannot be used with numeric conditions.

Solution

  1. Step 1: Understand FILTER condition

    FILTER filters values where condition is TRUE. The condition A1:A5>10 compares each cell to 10.

  2. Step 2: Identify cause of error

    If A1:A5 contains text, comparing text to number causes a #VALUE! error. FILTER itself is correct syntax and does not require a third argument.

  3. Final Answer:

    The range A1:A5 contains text values that cause the comparison to fail. -> Option B

  4. Quick Check:

    Text in numeric comparison causes error [OK]
Hint: Check data types before filtering numeric conditions [OK]
Common Mistakes:
  • Thinking FILTER needs 3 arguments
  • Putting >10 in quotes incorrectly
  • Assuming FILTER can't handle numbers
5. You have a list of names in A1:A10 with duplicates and some blank cells. Which formula returns a sorted list of unique names excluding blanks?
hard
A. =SORT(FILTER(UNIQUE(A1:A10), A1:A10<>"") )
B. =UNIQUE(FILTER(SORT(A1:A10), A1:A10<>""))
C. =FILTER(SORT(UNIQUE(A1:A10)), A1:A10<>"")
D. =SORT(UNIQUE(FILTER(A1:A10, A1:A10<>"")))

Solution

  1. Step 1: Remove blanks using FILTER

    FILTER(A1:A10, A1:A10<>"") removes blank cells from the list.

  2. Step 2: Remove duplicates with UNIQUE

    Applying UNIQUE to the filtered list removes duplicate names.

  3. Step 3: Sort the unique filtered list

    SORT arranges the unique names alphabetically.

  4. Final Answer:

    =SORT(UNIQUE(FILTER(A1:A10, A1:A10<>""))) -> Option D

  5. Quick Check:

    Filter blanks, then UNIQUE, then SORT [OK]
Hint: Filter blanks first, then UNIQUE, then SORT [OK]
Common Mistakes:
  • Sorting before filtering blanks
  • Filtering blanks after UNIQUE causing blanks to remain
  • Incorrect nesting order of functions
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard