The Era of Dynamic Arrays in Excel
Historically, Microsoft Excel forced users to think about data one cell at a time. If a formula returned multiple answers, you had to write complex, fragile INDEX and MATCH arrays and execute them with Ctrl+Shift+Enter (CSE formulas). If your data expanded, you had to manually drag the formulas down.
The introduction of the Dynamic Array engine in Microsoft 365 fundamentally changed this. Now, a single formula typed into one cell can automatically “spill” its results into adjacent empty cells. Among the most powerful tools in this new engine are the UNIQUE and SORT functions, which allow you to instantly deduplicate and organize massive datasets without ever using the traditional “Remove Duplicates” tool or the Data Ribbon.
Understanding the UNIQUE Function
The UNIQUE function looks at a range of data, strips out all identical duplicate entries, and returns a clean list of only the distinct items.
The Syntax
=UNIQUE(array, [by_col], [exactly_once])
- array: The range of cells you want to evaluate (e.g.,
A2:A100). - by_col (Optional): Set to
TRUEto compare columns instead of rows. (Defaults to FALSE). - exactly_once (Optional): Set to
TRUEto return only items that appear exactly one time in the list, excluding anything that is duplicated. (Defaults to FALSE, which returns every distinct item).
Example: Extracting a Unique List
Imagine you have a sales log in Column A containing 5,000 rows of data, but there are only a handful of actual Sales Reps names repeated over and over.
Click into an empty cell (e.g., C2) and type:
=UNIQUE(A2:A5000)
Hit Enter. Excel will instantly scan all 5,000 rows and spill a condensed list of the individual Sales Rep names down Column C. If a new name is added to the bottom of Column A, the list in Column C will automatically update and expand.
Understanding the SORT Function
The SORT function takes a range (or the spilled result of another function) and organizes it alphabetically or numerically.
The Syntax
=SORT(array, [sort_index], [sort_order], [by_col])
- array: The data you want to sort.
- sort_index (Optional): The column number to sort by if selecting a multi-column range. (Defaults to 1).
- sort_order (Optional):
1for Ascending (A-Z),-1for Descending (Z-A). (Defaults to 1). - by_col (Optional): Set to TRUE to sort columns instead of rows.
Combining UNIQUE and SORT (Nesting Functions)
The true power of dynamic arrays is that they can be nested inside one another seamlessly. In the previous example, UNIQUE gave us a list of Sales Reps, but they appeared in the order they were first discovered in the dataset, which is likely random.
To generate a list that is both deduplicated and alphabetized, you simply wrap the UNIQUE function inside the SORT function.
=SORT(UNIQUE(A2:A5000))
When you press Enter, Excel will first extract the unique values, pass that temporary array to the SORT function, and then spill the final, perfectly alphabetized list onto your spreadsheet.
Advanced: Generating Summary Dropdowns
This combination is incredibly useful for creating dynamic Data Validation drop-down menus.
- Create your
=SORT(UNIQUE(A2:A5000))formula in a hidden column, say Z2. - Click the cell where you want your drop-down menu to appear.
- Go to the Data tab and click Data Validation.
- Under Allow, select List.
- In the Source box, type:
=$Z$2#
The hash symbol (#) is the Spill Operator. It tells Excel not just to look at cell Z2, but to look at Z2 and every cell below it that the formula has spilled into. Now, your drop-down menu will automatically grow, shrink, and remain alphabetized as the raw data in Column A changes.
Conclusion
The UNIQUE and SORT functions eliminate the need for destructive data cleaning workflows. By utilizing these dynamic arrays, you can build self-maintaining dashboards and reports that automatically process messy datasets into clean, sorted summaries the moment new data is pasted into the workbook.