Using UNIQUE to Identify Distinct Values in Excel

Course: Advanced Excel

Topic: Using UNIQUE to Identify Distinct Values

Course Link: Advanced Excel Course – JustAcademy

Demo Registration: Register for a Course Demo

Training Provider: JustAcademy

1. Introduction to the UNIQUE Function

The UNIQUE function in Microsoft Excel identifies and returns distinct values from a list, column, row, or larger dataset. It helps remove repeated entries from the displayed results without changing the original source data.

For example, if a sales worksheet contains the region names West, East, West, North, and East, the UNIQUE function can return West, East, and North only once each. This is useful for preparing summary reports, identifying customer lists, creating category lists, and analyzing repeated records.

UNIQUE is a dynamic array function. It can return multiple results from one formula, and Excel automatically displays the results in adjacent cells through a process called spilling. If the source data changes and the formula references the updated data, the distinct-value list can update automatically.

2. Learning Objectives

• Understand the meaning of unique and distinct values in Excel.
• Learn the syntax and arguments of the UNIQUE function.
• Extract distinct values from a single column or row.
• Identify records that occur exactly once.
• Combine UNIQUE with SORT, FILTER, COUNTA, and other functions.
• Use distinct-value lists in reports, dashboards, and data-cleaning tasks.
• Understand common errors, compatibility limitations, and best practices.

3. What Are Distinct Values?

Distinct values are values that appear only once in the resulting list, even if they occur multiple times in the original data. The standard UNIQUE function returns one instance of each distinct value.

It is important to distinguish between two related concepts:

• Distinct values: Return each different value once, even if it appears several times in the source data.
• Values occurring exactly once: Return only values whose frequency in the source data is one.

For example, consider the following list:

The standard UNIQUE function returns Apple, Banana, Orange, and Mango. When configured to return only items occurring exactly once, it returns Banana and Orange.

4. Syntax of the UNIQUE Function

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

The UNIQUE function contains three arguments. The first is required, while the second and third are optional.

Important: In the usual single-column example, the formula is simply =UNIQUE(A2:A100). You do not need to specify the optional arguments unless you want different behavior.

5. Extracting Distinct Values from a Single Column

Suppose a company maintains a list of products in cells A2:A10. The product names may repeat because each product appears in multiple sales transactions.

To extract each product once, enter this formula in C2:

=UNIQUE(A2:A10)

Excel returns the following distinct product list:

The formula returns the distinct products in their order of first appearance. The original data remains unchanged, and the output spills below the formula cell.

6. Extracting Distinct Values from a Row

UNIQUE can also identify distinct values across a horizontal row. To compare values by column, set the second argument to TRUE.

Suppose cells A1:F1 contain:

North | South | North | East | West | South

Enter the formula:

=UNIQUE(A1:F1,TRUE)

The result contains North, South, East, and West as a horizontal array. The output spills across the row.

This method is useful when categories, monthly labels, survey responses, or other values are organized horizontally rather than vertically.

7. Identifying Values That Occur Exactly Once

Sometimes a report needs more than one copy of each distinct value. It may need to identify items that appear only once in the source data. This can help locate one-time transactions, single-occurrence product codes, or customers who appear only once in a list.

Use the third argument of UNIQUE by setting exactly_once to TRUE.

=UNIQUE(A2:A20,FALSE,TRUE)

In this formula:

• A2:A20 is the source range.
• FALSE means that Excel compares values by row.
• TRUE means that only values appearing exactly once are returned.

For example, if the source list contains Apple, Banana, Apple, Orange, and Mango, the formula returns Banana, Orange, and Mango because each of those values appears once in that example.

Note: The exact-once option is different from removing duplicates in a standard list. Standard UNIQUE keeps one representative of repeated values; exactly_once=TRUE excludes values that repeat.

8. Combining UNIQUE with SORT

The UNIQUE function returns distinct values in the order of their first appearance. If you need an alphabetical or numerical order, combine UNIQUE with SORT.

Example: Alphabetical Product List

=SORT(UNIQUE(A2:A100))

This formula first extracts distinct product names and then sorts the resulting list alphabetically.

Sort Numbers from Largest to Smallest

=SORT(UNIQUE(B2:B100),1,-1)

Here, the first argument supplied to SORT is the unique list, the sort index is 1, and the sort order of -1 requests descending order.

Why Combine These Functions?

• Produces a clean, organized list for reports.
• Reduces manual sorting and duplicate removal.
• Updates the list when referenced source data changes.
• Helps prepare category lists for dashboard filters and data validation.

9. Combining UNIQUE with FILTER

FILTER returns records that meet a condition, while UNIQUE extracts distinct values from the filtered results. Combining these functions allows users to identify unique items that meet specific criteria.

Example: Unique Products from the West Region

Assume the following worksheet structure:

To list distinct products sold in the West region, use:

=UNIQUE(FILTER(A2:A100,B2:B100="West",""))

The FILTER function first selects product names associated with West. UNIQUE then returns each product once.

Example: Unique Products with Sales Above 50,000

=UNIQUE(FILTER(A2:A100,C2:C100>50000,""))

This formula returns distinct product names from rows where sales exceed 50,000.

When combining functions, verify that all ranges have compatible dimensions and test the formula when no records satisfy the condition.

10. Counting Distinct Values

UNIQUE can be combined with COUNTA to count the number of distinct nonblank values in a range.

Basic Formula

=COUNTA(UNIQUE(A2:A100))

This formula counts the items returned by UNIQUE. However, blank entries in the source range can affect the count, depending on the structure of the data and how the blank values are represented.

Count Distinct Nonblank Values

=COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

This formula filters out blank cells first, extracts distinct values, and then counts the resulting list.

Tip: If the source data contains formulas that return empty strings, filtering out values equal to an empty string can help avoid counting those entries.

11. Using UNIQUE with Excel Tables

Excel Tables provide a structured way to organize datasets. When rows are added to a Table, structured references can automatically include the new records. This makes UNIQUE especially useful for reports that need to reflect changing data.

Suppose an Excel Table is named SalesData and contains a column named Product. To extract distinct product names, enter the formula in a blank cell outside the Table:

=UNIQUE(SalesData[Product])

To return the products in alphabetical order:

=SORT(UNIQUE(SalesData[Product]))

Because the source uses a structured reference, the formula can include new product entries added to the Table. Place the dynamic array formula outside the Table because spilled array formulas cannot spill within an Excel Table.

12. Creating Dynamic Dropdown Lists with UNIQUE

A distinct-value list can be used as the source for a dropdown menu. For example, a sales report may need a dropdown containing each region only once.

Step-by-Step Process

1. Ensure that the source region names are stored in a worksheet range or Excel Table.
2. Enter a UNIQUE formula in a blank area outside the source Table, for example =SORT(UNIQUE(B2:B100)).
3. Confirm that the formula spills all expected region names into the cells below it.
4. Open Data Validation and choose the List option for the cell that should contain the dropdown.
5. Set the list source to the spilled range reference, such as =$E$2#, if the dynamic list begins in E2 and the Excel version supports spill references in that context.
6. Test the dropdown after adding a new region to the source data.

This method reduces duplicate dropdown entries and makes it easier to maintain lists of regions, departments, product categories, and employee names.

13. Using UNIQUE to Identify Distinct Records

UNIQUE can operate on an entire multi-column range. In this situation, Excel evaluates rows by default and returns distinct rows, rather than looking at each column separately.

Suppose A2:B7 contains the following customer and city combinations:

Use the following formula:

=UNIQUE(A2:B7)

The output contains distinct customer-city combinations. Rahul-Mumbai appears once, Priya-Pune appears once, Amit-Nashik appears once, and Rahul-Pune remains a separate record because its city is different.

This is useful for identifying unique combinations of customer and location, employee and department, product and supplier, or other paired attributes.

14. UNIQUE vs. Remove Duplicates

Choose UNIQUE when you need a dynamic list that can update automatically. Choose Remove Duplicates when you intentionally want to modify the original data and remove repeated records.

15. Common Errors and Troubleshooting

16. Best Practices for Using UNIQUE

• Use a clearly defined source range or an Excel Table structured reference.
• Place the formula where enough blank cells are available for the result.
• Use SORT with UNIQUE when alphabetical or numerical ordering is required.
• Use exactly_once=TRUE only when you want to exclude values that repeat.
• Clean source data to remove unwanted spaces and inconsistent text entries.
• Filter out blank values when they should not be part of the final list.
• Use UNIQUE on multiple columns when you need distinct combinations of values.
• Combine UNIQUE with FILTER to generate condition-based lists.
• Use COUNTA with UNIQUE to count distinct nonblank values.
• Test formulas when the source contains no data or no matching records.
• Use structured references to make formulas easier to maintain as data grows.
• Check compatibility before sharing a workbook with users of older Excel versions.

17. Practice Exercises

Exercise 1: Unique Product Names

Create a product list with repeated names. Use UNIQUE to return each product once, then use SORT to arrange the products alphabetically.

Exercise 2: Unique Departments

Create an employee dataset containing department names such as IT, HR, Finance, Sales, and Marketing. Use UNIQUE to generate a department list without repeated entries.

Exercise 3: Values Occurring Exactly Once

Create a list of order numbers with some repeated entries. Use the third argument of UNIQUE to return only order numbers that occur exactly once.

Exercise 4: Count Distinct Customers

Create a customer transaction list and use COUNTA with UNIQUE to count distinct customer names. Modify the formula to exclude blank entries.

Exercise 5: Unique Products by Region

Create a sales dataset with Product, Region, and Sales columns. Use FILTER and UNIQUE to display products sold in one selected region.

Exercise 6: Distinct Customer-City Combinations

Create a two-column dataset containing customer names and cities. Use UNIQUE on both columns to identify distinct customer-city combinations.

18. Mini Project: Customer and Product Summary Report

Project objective: Build a dynamic summary worksheet that identifies distinct customers, products, and regions from a sales dataset.

Sample Dataset

Project Tasks

1. Convert the dataset into an Excel Table named SalesData.
2. Generate a distinct list of customers.
3. Generate a distinct list of products and sort them alphabetically.
4. Generate a distinct list of regions.
5. Count the distinct nonblank customers.
6. Filter the sales records for the West region and identify the distinct products sold there.
7. Create a dropdown list of regions using a spilled UNIQUE formula.
8. Add a new transaction and verify that the lists and counts update.
9. Check for spill errors and verify that the results match the source data.

Sample Project Formulas

Distinct customers:

=SORT(UNIQUE(SalesData[Customer]))

Distinct products:

=SORT(UNIQUE(SalesData[Product]))

Distinct regions:

=SORT(UNIQUE(SalesData[Region]))

Count distinct nonblank customers:

=COUNTA(UNIQUE(FILTER(SalesData[Customer],SalesData[Customer]<>"")))

Distinct products sold in the West region:

=SORT(UNIQUE(FILTER(SalesData[Product],SalesData[Region]="West","")))

Expected outcome: A reusable report that identifies distinct values and combinations, supports filtering, and updates when new records are added to the source Table.

19. Interview Questions and Answers

Q1. What is the UNIQUE function in Excel?

Answer: UNIQUE is a dynamic array function that returns distinct values, rows, or columns from a specified range or array without modifying the original source data.

Q2. What is the syntax of UNIQUE?

Answer: The syntax is =UNIQUE(array,[by_col],[exactly_once]). The array argument is required, while the other two arguments are optional.

Q3. What is the difference between UNIQUE and Remove Duplicates?

Answer: UNIQUE creates a formula-driven list of distinct results that can update when the source changes. Remove Duplicates permanently removes repeated entries from the selected source data.

Q4. What does exactly_once=TRUE do?

Answer: It returns only values or rows that occur exactly once in the source array. It does not return one copy of every repeated value.

Q5. How can UNIQUE results be sorted alphabetically?

Answer: Combine UNIQUE with SORT, for example =SORT(UNIQUE(A2:A100)).

Q6. Can UNIQUE work with multiple columns?

Answer: Yes. When a multi-column range is supplied, UNIQUE compares complete rows by default and returns distinct row combinations. Setting by_col=TRUE compares columns instead.

Q7. How can you count distinct nonblank values?

Answer: Use =COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>""))) to filter out blanks, return distinct values, and count them.

Q8. Why might duplicate-looking values appear in the result?

Answer: The source values may contain leading or trailing spaces, hidden characters, or inconsistent data. Cleaning the source data can help resolve this issue.

Q9. Can UNIQUE be used with an Excel Table?

Answer: Yes. Use a structured reference such as =UNIQUE(SalesData[Product]). Enter the formula outside the Table so that its results can spill into neighboring cells.

Q10. Which Excel versions support UNIQUE?

Answer: UNIQUE is available in Microsoft 365, Excel 2021, Excel 2024, Excel for the web, and supported mobile versions. Excel 2019 and earlier do not natively support this function.

20. Quick Revision Checklist

• UNIQUE extracts distinct values without changing the original data.
• The required argument is the source array or range.
• The default behavior compares rows and returns distinct results.
• Set by_col=TRUE to compare columns.
• Set exactly_once=TRUE to return only entries that occur exactly once.
• Combine UNIQUE with SORT to organize results.
• Combine UNIQUE with FILTER to create condition-based lists.
• Use COUNTA with UNIQUE to count distinct values, and exclude blanks when necessary.
• Use structured references to help formulas include new Table records.
• Place dynamic array formulas outside Excel Tables.
• Ensure the spill range has enough empty cells.
• Check for extra spaces, hidden characters, and inconsistent source data.
• Confirm that the workbook's Excel version supports UNIQUE.

21. Conclusion

The UNIQUE function is a powerful Advanced Excel tool for identifying distinct values, removing repeated entries from report outputs, and creating dynamic lists from changing datasets. It can simplify customer analysis, product reporting, employee summaries, region-based reports, and dropdown list preparation.

By combining UNIQUE with SORT, FILTER, and COUNTA, you can build flexible reports that require fewer manual steps. Understanding the difference between distinct values and values that occur exactly once is particularly important when choosing the correct formula arguments.

Regular practice with real-world datasets will help you use UNIQUE confidently in data cleaning, reporting, dashboard development, and business analysis.

22. Additional Learning Resources

For more details, refer to Microsoft's official documentation: Microsoft Excel UNIQUE Function Guide.

Continue your learning with JustAcademy: Explore the Advanced Excel Course, register for a course demo, or visit the JustAcademy homepage.