Learn Structuring Unique Lists | Dynamic Arrays and Interactive Reporting

Swipe to show menu

Free-text input often breaks dynamic formulas because reporting systems depend on exact matches. In this chapter, create controlled input lists using UNIQUE and Data Validation.

UNIQUE Structure

=UNIQUE(array, [by_col], [exactly_once])

array: dataset being evaluated;
[by_col]: controls orientation;
[exactly_once]: controls duplicate behavior;
FALSE: returns one instance of each value;
TRUE: returns only values appearing exactly once.

Formula used in this chapter:

=UNIQUE(Sales_Data[Region])

This creates a dynamic list of unique regions from the dataset.

Step 1 Create the Reference Sheet

Create a new worksheet named:

Reference_Lists

Add the following headers:

Cell	Value
A1	Regions
B1	Sales Reps

Step 2 Generate the Region List

Inside A2, type:

=UNIQUE(Sales_Data[Region])

The list automatically updates when new regions are added to Sales_Data.

Step 3 Add New Dataset Rows

Copy the following rows into Sales_Data:

21/02/2026,1052,C001,Laptop,Central,Marcos,4
22/02/2026,1053,C015,Phone,Central,Marcos,6
23/02/2026,1054,C023,Monitor,Southwest,Priya,2
24/02/2026,1055,C034,Keyboard,Southwest,Priya,8
25/02/2026,1056,C042,Tablet,Central,Fatima,3
26/02/2026,1057,C008,Mouse,Southwest,Fatima,10
27/02/2026,1058,C019,Laptop,Central,Marcos,5
28/02/2026,1059,C031,Phone,Southwest,Priya,4
01/03/2026,1060,C047,Monitor,Central,Fatima,1
02/03/2026,1061,C003,Keyboard,North,Marcos,7
03/03/2026,1062,C012,Laptop,Southwest,Priya,3
04/03/2026,1063,C025,Mouse,Central,Fatima,9
05/03/2026,1064,C038,Tablet,North,Ana,2
06/03/2026,1065,C011,Phone,Southwest,Marcos,5
07/03/2026,1066,C044,Laptop,East,Priya,4
08/03/2026,1067,C002,Monitor,Central,Fatima,2
09/03/2026,1068,C017,Keyboard,Southwest,Ana,6
10/03/2026,1069,C029,Mouse,North,Marcos,11
11/03/2026,1070,C036,Tablet,Central,Priya,3
12/03/2026,1071,C048,Laptop,Southwest,Fatima,5
13/03/2026,1072,C014,Phone,North,Ricardo,2

Notice that new regions automatically appear inside the spill range.

Step 4 Generate the Sales Rep List

Inside B2, type:

=UNIQUE(Sales_Data[Sales_Rep])

Each sales rep appears only once regardless of transaction count.

Step 5 Apply Data Validation to Regions

Select the Region column inside Sales_Data.

Apply Data Validation:

Allow: List

Use the following source:

=Reference_Lists!$A$2:$A$10000

Only valid regions can now be selected.

Step 6 Apply Data Validation to Sales Reps

Select the Sales_Rep column. Apply Data Validation using:

=Reference_Lists!$B$2:$B$10000

Step 7 Test Dynamic Updates

Add additional rows inside Sales_Data.

Notice that:

The reference lists update automatically;
The dropdown menus update automatically;
New regions and reps become available immediately.

Step 8 Test Advanced UNIQUE Logic

Inside Reference_Lists, type:

=UNIQUE(Sales_Data[Sales_Rep], FALSE, TRUE)

This returns only values appearing exactly once in the dataset.

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 5

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 2. Chapter 5

Structuring Unique Lists

UNIQUE Structure

1. Why is free-text input problematic in dynamic Excel reporting systems?

2. What is the main advantage of using `UNIQUE` with structured table references?

3. What does setting the third argument of `UNIQUE` to `TRUE` do?

Structuring Unique Lists

UNIQUE Structure

1. Why is free-text input problematic in dynamic Excel reporting systems?

2. What is the main advantage of using UNIQUE with structured table references?

3. What does setting the third argument of UNIQUE to TRUE do?

2. What is the main advantage of using `UNIQUE` with structured table references?

3. What does setting the third argument of `UNIQUE` to `TRUE` do?