Skip to content
gr0wpath
Log In

Excel dynamic arrays: UNIQUE, SORT, SORTBY and FILTER

For analysts, finance and operations staff who build reports and dashboards in Excel and want lists and extracts that update themselves. Prerequisite: 'Excel tables and structured references' (you should be comfortable with tables and structured references, which dynamic arrays use as their source). Needs Excel for Microsoft 365, Excel 2021 or Excel 2024; Microsoft's function pages don't list Excel 2019 or 2016, so check what your readers use. You'll write UNIQUE formulas for distinct and exactly-once lists, SORT and SORTBY formulas, FILTER formulas with AND/OR conditions and an empty-result value, combine them with the # spilled range operator, and diagnose #SPILL!, #CALC! and #REF! errors. Grounded in Microsoft Support documentation. Ends with a supervisor-graded spreadsheet task.

Level
Advanced
Length
About 95 minutes
Contents
5 lessons · final exam
Status
Published · updated 10 Oct 2026
Request a demoMore Office / administration courses

Skills you'll practise

  • Write UNIQUE formulas that list distinct values, or values that occur exactly once
  • Write SORT and SORTBY formulas, choosing SORTBY when the sort key is a separate range or columns may move
  • Write FILTER formulas with one or more conditions, using * for AND, + for OR, and an if_empty value
  • Combine dynamic array functions and refer to a whole spill range with the # operator
  • Diagnose #SPILL!, #CALC! and #REF! errors in dynamic array formulas and fix their cause

Course outline

  1. 1.Write UNIQUE formulas that list distinct values, or values that occur exactly onceLesson · 17 min
  2. 2.Write SORT and SORTBY formulas, choosing SORTBY when the sort key is a separate range or columns may moveLesson · 17 min
  3. 3.Write FILTER formulas with one or more conditions, using * for AND, + for OR, and an if_empty valueLesson · 18 min
  4. 4.Combine dynamic array functions and refer to a whole spill range with the # operatorLesson · 13 min
  5. 5.Diagnose #SPILL!, #CALC! and #REF! errors in dynamic array formulas and fix their causeLesson · 15 min
  6. 6.Excel dynamic arrays: UNIQUE, SORT, SORTBY and FILTER: knowledge checkKnowledge check · 13 questions
  7. 7.Excel dynamic arrays: UNIQUE, SORT, SORTBY and FILTER: practical exerciseKnowledge check · 1 question
  8. 8.Final exam10 questions · passing it completes the course, so people who already know the material can test out

Sources it draws on

The lessons and questions are written from these references, so learners can go back to the original.

See it with your own jobs and topics

Tell us about your team and we'll walk you through setup, from choosing jobs to your first skills check.

Request a demoLog in