ExamFinch.com My Study

Study guide

SORT and UNIQUE Functions

Practice this exam

Microsoft Excel Microsoft 365 Apps

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

About the MO-210 Exam Performance-Based Exam Strategy

2 Import Data & Navigate Workbooks

Import Data from Text Files Import Data from Online Sources Search and Navigate to Named Cells, Ranges, Elements Insert and Remove Hyperlinks

3 Format Worksheets, Views & Collaboration

Page Setup, Headers, Footers, Row Height & Column Width Quick Access Toolbar, Views, Freeze Panes, Properties, Display Formulas Print Area, Export Formats, Print Settings, Inspect, Comments & Notes

4 Manipulate Data in Worksheets

Paste Special and Auto Fill Insert and Delete Rows, Columns, and Cells Generate Numeric Data with RANDBETWEEN and SEQUENCE

5 Format Cells and Ranges

Merge, Alignment, Orientation, Indentation, Wrap Text Number Formats, Format Painter, Format Cells Dialog Cell Styles, Clear Formatting, Group Worksheets

6 Named Ranges & Visual Summaries

Define and Reference Named Ranges Insert and Manage Sparklines Apply and Remove Conditional Formatting

7 Create and Format Tables

Create Excel Tables from Cell Ranges Apply Table Styles and Convert Tables to Ranges Add or Remove Table Rows and Columns

8 Table Options, Filter & Sort

Configure Table Style Options Insert and Configure Total Rows Filter Table Records Sort Data by Multiple Columns

9 Formula References

Relative, Absolute, and Mixed References Structured References in Tables

10 Calculate and Transform Data

SUM, AVERAGE, MIN, and MAX COUNT, COUNTA, and COUNTBLANK Conditional Operations with IF SORT and UNIQUE Functions

11 Format and Modify Text

LEFT, RIGHT, and MID UPPER, LOWER, LEN, CONCAT, and TEXTJOIN

12 Create Charts

Create Charts from Data Create Chart Sheets

13 Modify and Format Charts

Add Data Series and Switch Rows/Columns Add and Modify Chart Elements Chart Layouts, Styles, and Alternative Text

SORT and UNIQUE Functions

Key Takeaways Explain This Topic AI

SORT and UNIQUE are dynamic array functions: one formula spills results into neighboring blank cells

UNIQUE returns distinct values from a range; SORT returns a sorted array (by column index and order)

Spill ranges need empty cells below/beside the formula—blocked spills show a spill error until you clear the obstruction

MO-210 includes these Microsoft 365 dynamic array skills; do not confuse them with Data → Sort on tables (Domain 3)

  • Combine UNIQUE then SORT (or SORT around UNIQUE) when a project wants an alphabetized distinct list
  • Last updated: August 2026

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:

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.

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.

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

  • Function Common Associate syntax Purpose
  • UNIQUE =UNIQUE(array) Distinct values from the array

UNIQUE =UNIQUE(array, [by_col], [exactly_once]) Optional: compare by columns; optionally keep values that appear exactly once

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):

  • H
  • East
  • West
  • North
  • South

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

  • Function Syntax Purpose
  • SORT =SORT(array, [sort_index], [sort_order], [by_col]) Returns a sorted array

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

  • Approach What it does Domain
  • Data → Sort or table sort headers Permanently (or view) reorders worksheet/table rows Domain 3 filter & sort

=SORT(...) / =UNIQUE(...) Formula spills a transformed list elsewhere; source can stay put Domain 4 calculate & transform

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

Read the destination cell and clear spill space below (and across if multi-column).

Decide: unique only, sorted only, or sorted unique.

Type =UNIQUE(range) , =SORT(range) , or =SORT(UNIQUE(range)) .

Confirm ascending vs descending ( 1 vs -1 ).

Do not Data → Sort the source unless asked.

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

Create a messy region column with repeats; spill =UNIQUE(...) into a clear column.

Spill =SORT(...) on unsorted names; then replace with =SORT(UNIQUE(...)) and compare.

Force a #SPILL! by typing in the cell below, clear it, and watch the spill restore.

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.