SORT and UNIQUE Functions

Dynamic arrays on the MO-210 objective

Certiport's MO-210 objective domain for Calculate and transform data specifically includes transforming lists with SORT and UNIQUE. These are Microsoft 365 dynamic array functions: you enter one formula in one cell, and Excel "spills" multiple results into a block of cells automatically. That behavior is different from Auto Fill of IF or SUM, and different from sorting a table with the Data tab.

Expect wording such as "In cell H2, enter a formula that lists the unique regions from column C", "Spill a sorted list of product names in ascending order", or "Create a distinct, alphabetized list of customer names starting in cell F2." Graders look for UNIQUE/SORT formulas and a successful spill, not a one-time Data → Sort that permanently reordered the source table unless that was a Domain 3 task.

Spill ranges in plain language

When a dynamic array formula returns more than one value, Excel writes the first value in the formula cell and continues into adjacent empty cells—the spill range. Selecting any spilled cell shows a blue outline around the whole spill. The formula lives only in the top-left cell; other spilled cells display results but are not separately editable formulas.

Rules that matter on the exam:

1. Cells in the spill path must be empty. If something blocks the spill, Excel shows a spill error (often #SPILL!) until you clear or move the blocking content.
2. You usually enter the formula in the first destination cell named by the project (for example H2), with enough blank rows below for all unique items.
3. You can reference an entire spill with the spill operator #, as in H2#, in later formulas—but many Associate tasks only require creating the spill itself.

UNIQUE syntax and behavior

For MO-210, =UNIQUE(range) is the workhorse. It spills the distinct list vertically when the source is a column.

Worked example: distinct regions

Column C (C2:C12) contains: East, West, East, North, West, South, East, North, West, East, South.

In H2 enter:

=UNIQUE(C2:C12)

Spill result in H2:H5 (order of first appearance by default):

Four unique regions from eleven rows. If H3 already held a label, UNIQUE would fail with a spill error until H3 is cleared.

Exactly once (optional awareness)

=UNIQUE(C2:C12,,TRUE)

The third argument TRUE keeps values that appear exactly once in the array (rare on Associate prompts, but recognize the argument exists). Default FALSE/omitted returns every distinct value regardless of frequency.

SORT syntax and behavior

Associate defaults you must know:

• sort_index — which column to sort by when the array has multiple columns (1 = first column). For a single column, use 1 or omit when default works.
• sort_order — 1 ascending (default), -1 descending.
• by_col — usually omitted/FALSE to sort rows (vertical lists).

Worked example: sort a name list ascending

Names in A2:A8: Diaz, Chen, Abel, Diaz, Brown, Abel, Chen.

In F2:

=SORT(A2:A8)

spills alphabetical order (duplicates included):

Abel, Abel, Brown, Chen, Chen, Diaz, Diaz.

SORT alone does not remove duplicates—that is UNIQUE's job.

Descending numeric sort

Scores in D2:D6: 88, 92, 75, 95, 80.

=SORT(D2:D6,1,-1)

spills 95, 92, 88, 80, 75. The -1 sets descending order.

Combining UNIQUE and SORT (exam favorite)

Projects often want a sorted unique list. Nest the functions:

=SORT(UNIQUE(C2:C12))

or

=UNIQUE(SORT(C2:C12))

For a single column of text, SORT(UNIQUE(...)) is the clearest Associate pattern: distinct first, then ascending alpha spill.

Worked example: alphabetized distinct customers

Customer column B2:B20 has repeats. Destination J2 must show each customer once, A to Z, without changing the original table order.

=SORT(UNIQUE(B2:B20))

Source table stays unsorted (important when Domain 3 already filtered or arranged the table a certain way). The spill in column J is a formula-driven list for a dashboard or lookup helper.

SORT/UNIQUE vs Data → Sort / table sort

If the task says "enter a formula in cell G2 that…," use SORT/UNIQUE. If it says "sort the table by Date ascending," use table/Data sort tools from Domain 3. Using the wrong tool can leave the workbook looking right while failing the automated check for formula text.

Multi-column arrays (light touch)

SORT can spill multiple columns when the array includes them:

=SORT(A2:C15,2,1)

sorts the block by the second column ascending and spills all three columns. UNIQUE on a multi-column array returns unique rows (combinations). Associate tasks more often use a single column; still, leave room for multi-column spills so you do not overwrite other content.

Practical exam checklist

1. Read the destination cell and clear spill space below (and across if multi-column).
2. Decide: unique only, sorted only, or sorted unique.
3. Type =UNIQUE(range), =SORT(range), or =SORT(UNIQUE(range)).
4. Confirm ascending vs descending (1 vs -1).
5. Do not Data → Sort the source unless asked.
6. If #SPILL! appears, find and clear the blocking cell—do not convert to values unless instructed.

Common MO-210 traps

• Confusing UNIQUE with COUNT of uniques—UNIQUE lists values; COUNTA on a UNIQUE spill can count them: =COUNTA(UNIQUE(A2:A50)) (advanced combo; only if asked).
• Sorting the source table when a spill formula was required.
• Entering SORT/UNIQUE but leaving junk in the spill path → #SPILL!.
• Expecting SORT to remove duplicates.
• Editing a value in the middle of a spill (you must edit the parent formula cell).
• Using older Excel without dynamic arrays—MO-210 assumes Microsoft 365 Apps behavior where these functions spill.

Practice sequence

1. Create a messy region column with repeats; spill =UNIQUE(...) into a clear column.
2. Spill =SORT(...) on unsorted names; then replace with =SORT(UNIQUE(...)) and compare.
3. Force a #SPILL! by typing in the cell below, clear it, and watch the spill restore.
4. Sort a numeric column descending with sort_order -1.

Master spill mechanics plus UNIQUE and SORT nesting, and you finish the transform half of Domain 4's Calculate and transform data skill for Exam MO-210.