Study guide
11.3 Deduplication & Sequence Arrays: UNIQUE & Array Reshaping
Microsoft Excel Expert Microsoft 365 Apps
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview & MO-211 Fundamentals
1.1 MO-211 Exam Structure, Scoring, & Performance-Based Strategy 1.2 Excel Environment & Microsoft 365 Architecture
2 Chapter 2: Managing Workbooks & File Options
2.1 Workbook Versioning, AutoRecover, & File Properties 2.2 External Data Referencing & Cross-Workbook Formulas 2.3 Macro Security, Portability, & Workbook Distribution
3 Chapter 3: Workbook Protection & Collaboration Settings
3.1 Cell Locking, Range Permissions, & Worksheet Protection 3.2 Workbook Structure Protection & Editing Restrictions 3.3 Formula Calculation Options & Iterative Calculations
4 Chapter 4: Advanced Data Entry & Series
4.1 Flash Fill Patterns & Text Extraction Techniques 4.2 Advanced Fill Series (Linear, Growth, Date, & Step Configurations) 4.3 Dynamic Sequence & Random Data Generation (RANDARRAY, SEQUENCE)
5 Chapter 5: Custom Formatting & Data Validation
5.1 Custom Number Formatting Syntax & Masking Rules 5.2 Data Validation Criteria & Custom Formula Rules 5.3 Validation Input Messages, Error Alerts, & Data Circle Auditing
6 Chapter 6: Data Grouping, Summarization, & Cleansing
6.1 Data Grouping, Ungrouping, & Multi-Level Outlining 6.2 Subtotal Insertion & Summary Function Calculations 6.3 Duplicate Record Removal & Text-to-Columns Cleansing
7 Chapter 7: Advanced Conditional Formatting & Filtering
7.1 Formula-Based Conditional Formatting & Logical Tests 7.2 Data Bars, Color Scales, Icon Sets, & Rule Hierarchy 7.3 Advanced Filtering with Complex Criteria Ranges
8 Chapter 8: Logical Operations & Advanced Multi-Condition Functions
8.1 Nested IF, IFS, & SWITCH Logical Structures 8.2 Compound Boolean Logic: AND, OR, NOT, & XOR 8.3 Multi-Criteria Aggregations: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, & MINIFS
9 Chapter 9: Advanced Lookup Functions & Variable Optimization
9.1 Modern Lookups with XLOOKUP & XMATCH 9.2 Classic Matrix & Two-Way Lookups: INDEX & MATCH 9.3 Formula Optimization & Variable Assignment with LET
10 Chapter 10: Date, Time, Text, & Auditing Functions
10.1 Current Date, Time, & Day-of-Week Functions: NOW, TODAY, & WEEKDAY 10.2 Business Date Calculations: WORKDAY, NETWORKDAYS, EDATE, & EOMONTH 10.3 Modern Text Extraction & Array Splitting: TEXTSPLIT, TEXTJOIN, TEXTBEFORE, TEXTAFTER 10.4 Error Handling & Formula Auditing Tools
11 Chapter 11: Dynamic Array Functions & Spill Operations
11.1 Array Filtering & Extraction: FILTER Function 11.2 Multi-Level Sorting: SORT & SORTBY Functions 11.3 Deduplication & Sequence Arrays: UNIQUE & Array Reshaping
12 Chapter 12: Data Analysis, What-If Tools, & Financial Functions
12.1 Summarizing Multiple Ranges with the Consolidate Feature 12.2 What-If Analysis: Goal Seek & Scenario Manager 12.3 Financial Modeling Functions: PMT, NPER, & Forecast Logic
13 Chapter 13: Macro Automation & VBA Fundamentals
13.1 Recording Macros with Absolute vs. Relative References 13.2 Macro Storage, Shortcut Assignment, & Form Control Buttons 13.3 Managing & Modifying VBA Code in the Visual Basic Editor
14 Chapter 14: Advanced & Specialized Visualizations
14.1 Statistical Visualizations: Histograms & Pareto Charts 14.2 Hierarchy & Financial Visualizations: Waterfall, Funnel, Treemap, & Sunburst 14.3 Distribution & Multi-Axis Charts: Box & Whisker & Dual-Axis Combo Charts
15 Chapter 15: PivotTable Construction & Field Configuration
15.1 PivotTable Data Architecture, Layouts, & Style Configuration 15.2 Field Summarization, Number Formatting, & Custom Calculations 15.3 Value Display Modes: % of Total, Running Total, & Differences
16 Chapter 16: PivotTable Grouping, Slicers, & PivotCharts
16.1 Date & Numeric Field Grouping Strategies 16.2 Calculated Fields & Calculated Items in PivotTables 16.3 Slicers, Timelines, Report Connections, & PivotCharts
11.3 Deduplication & Sequence Arrays: UNIQUE & Array Reshaping
Key Takeaways Explain This Topic AI
The UNIQUE function extracts deduplicated records from an array, defaulting to row-by-row distinct evaluation across all supplied columns.
The [exactly_once] parameter distinguishes between distinct items (FALSE, default) and single-occurrence items (TRUE), allowing analysts to isolate non-duplicate records.
The spill operator (#) appended to an anchor cell (e.g., =F2#) dynamically references the entire spilled array, enabling dependent formulas and dynamic Data Validation dropdown lists to adapt automatically.
A #SPILL! error indicates an obstruction in the spill range, merged cells in the output path, an indeterminate array size, or attempting to spill within an official Excel Table (ListObject).
Composing dynamic pipelines by nesting FILTER, UNIQUE, and SORT produces fully automated data cleaning and summary workflows within a single calculation cell.
Last updated: September 2026
Deduplication & Sequence Arrays: UNIQUE & Array Reshaping
Managing duplicate records and extracting distinct lists are routine operational demands in financial reconciliation, sales auditing, and data modeling. Traditionally, Excel users resolved duplicates via the Data > Remove Duplicates tool. While quick, Remove Duplicates is a destructive operation: it permanently deletes worksheet rows, cannot be reversed once the workbook is saved, and does not update when new records are added. Alternatively, advanced users engineered complex array formulas combining INDEX , MATCH , and COUNTIF , which imposed heavy calculation lag.
Microsoft 365 introduces the UNIQUE function, delivering non-destructive, dynamic deduplication. Working alongside the dynamic array spill operator ( # ) and array composition functions, UNIQUE allows analysts to build clean, self-updating data models and dynamic validation lists.
Anatomy and Argument Architecture of UNIQUE
The UNIQUE function returns a list of unique values from a list or range: =UNIQUE(array, [by_col], [exactly_once])
Argument Required / Optional Default Value Description & Operational Rules
array Required None The source range or array from which to extract unique values.
[by_col] Optional FALSE Logical comparison direction. FALSE (or omitted) compares rows against each other (vertical deduplication). TRUE compares columns against each other (horizontal deduplication).
[exactly_once] Optional FALSE Extraction mode. FALSE (default) returns all distinct values (each unique value appears once). TRUE returns only items that occur exactly once in the source data (isolates true singletons, discarding any item that repeats).
Distinct Values vs. Exactly Once: Deep Dive
Understanding the [exactly_once] argument is critical for the MO-211 exam, as business requirements frequently contrast "distinct lists" with "unique occurrences."
- Consider a list of customer order regions in A2:A7 : {"East", "West", "East", "North", "West", "South"}
- Mode 1: Distinct Values ( [exactly_once] = FALSE or omitted) =UNIQUE(A2:A7)
Output: {"East"; "West"; "North"; "South"} Excel removes duplicates, returning each distinct region that appeared in the dataset. This is the standard setting for building summary tables, report categories, and dropdown lists.
Mode 2: Exactly Once ( [exactly_once] = TRUE) =UNIQUE(A2:A7, FALSE, TRUE)
Output: {"North"; "South"} Excel inspects item frequencies across A2:A7 . Because "East" appears twice and "West" appears twice, both are discarded entirely. Only "North" and "South" occur with a frequency of exactly 1. This mode is indispensable for auditing anomalies—such as finding customers who made only a single purchase or flagging non-reconciled ledger entries.
Multi-Column Deduplication
When array spans multiple columns (e.g., A2:B100 containing First Name and Last Name), UNIQUE evaluates each entire row as a composite key: =UNIQUE(A2:B100)
A row is only treated as a duplicate if both First Name AND Last Name match another row. If John Smith and John Doe both appear, both rows are preserved.
The Dynamic Array Spill Operator ( # )
When a dynamic array formula outputs multiple values, Excel places the formula in the top-left cell (the anchor cell) and spills the results into adjacent rows and columns. A thin blue line borders the active spill range.
The spill operator ( # ) allows downstream formulas to reference this entire spilled array dynamically. By appending # to the anchor cell address, the reference automatically expands or contracts whenever the source array resizes. Anchor Cell: F2 contains =UNIQUE(A2:A100) --> Spills into F2:F15
Downstream Reference Examples:
=COUNTA(F2#) --> Counts the exact number of unique items currently spilled.
=SORT(F2#) --> Sorts the spilled unique list without re-reading column A.
=XLOOKUP(H2, F2#, G2#) --> Dynamic lookup across adjacent spilled vectors.
Dynamic Data Validation Dropdown Lists
A powerful application on the MO-211 exam is linking Data Validation to a spilled array:
In cell F2 , enter =SORT(UNIQUE(Table1[Department])) .
Select target input cells (e.g., H2:H20 ).
Open Data > Data Validation , set Allow to List , and enter the Source: =F2#
Whenever new departments are added to Table1 , F2# expands automatically, and the Data Validation dropdown immediately reflects the updated departments without manual range adjustments.
Taxonomy of #SPILL! Errors & Diagnostics
A #SPILL! error occurs when Excel's calculation engine attempts to spill an array, but encounters an insurmountable physical or structural barrier. Understanding the underlying causes is essential for troubleshooting:
Error Sub-Type / Cause Technical Mechanism Diagnostic & Corrective Action
Spill Obstacle / Collision A non-empty cell (data, space, formula) lies inside the target spill footprint. Click the warning indicator; select "Select Obstructing Cells"; clear or delete the blocking cells.
Merged Cells One or more merged cells exist anywhere within the intended spill range. Dynamic arrays cannot spill into merged cells. Select the target range and click Home > Unmerge Cells.
Inside an Excel Table ( ListObject ) Dynamic array formulas cannot spill when entered inside an official Excel Table. Tables require fixed row structures. Place the formula on the worksheet grid outside the table, or convert table to range.
Indeterminate Array Size The formula uses volatile or random functions where array size changes upon recalc. Functions like SEQUENCE(RANDBETWEEN(1,10)) cannot establish a stable spill grid and trigger #SPILL! .
Worksheet Edge Overflow The spilled output extends beyond row 1,048,576 or column XFD. Occurs when filtering entire columns (e.g., FILTER(A:A, B:B="X") ) where empty rows force an overflow. Limit ranges to explicit boundaries (e.g., A2:A1000 ).
Composing Dynamic Pipelines: FILTER, UNIQUE, & SORT
Dynamic array functions achieve maximum power when composed into multi-stage functional pipelines. Rather than building helper columns or multi-step staging sheets, a single nested formula can ingest raw operational data, filter by business conditions, eliminate duplicates, and sort the final presentation. Raw Transaction Ledger (A2:D500)
- │
- ▼
- [Stage 1: FILTER] --> Isolates records where Sales > 10,000
- │
- ▼
- [Stage 2: UNIQUE] --> Extracts distinct Department names from filtered rows
- │
- ▼
[Stage 3: SORT] --> Orders distinct Departments alphabetically (A to Z) =SORT(UNIQUE(FILTER(B2:B500, D2:D500>10000, "None")))
In this pipeline:
FILTER checks column D for values exceeding 10,000 and extracts the corresponding department names from column B.
UNIQUE takes the resulting vector and discards duplicate department names.
SORT takes the distinct list and sorts it in ascending alphabetical order.
The final clean list spills dynamically into the worksheet.
High-Frequency MO-211 Exam Traps
Dynamic Arrays Inside Excel Tables : Entering =UNIQUE(A2:A50) inside an Excel Table column triggers an immediate #SPILL! error. Excel Tables do not support spilled dynamic arrays because table rows must remain structurally uniform.
Confusing Distinct vs. Exactly Once : If a question asks for a list of "all unique vendors who billed us" , use [exactly_once] = FALSE (or omitted). If it asks for "vendors who billed us only once" , set [exactly_once] = TRUE .
Missing the Spill Operator ( # ) in Data Validation : When configuring a dynamic dropdown from cell F2 , entering =F2 in Data Validation binds the dropdown to only the single top-left cell. You must enter =F2# to capture the entire spilled list.
Ghost Obstructions : A cell in the spill path may appear blank but contain an invisible space character ( " " ) or empty string ( "" ). Excel will throw #SPILL! . Selecting "Select Obstructing Cells" from the error flyout immediately reveals the offending cell.