Have you ever looked at a massive data set in Excel and just wanted to pull out unique values? Whether you want to filter out duplicate customer names or analyze sales item counts, Excel makes it possible with a nifty feature that saves time and reduces hassle: find unique values using the UNIQUE function.

Today, we’re diving into how you can use the UNIQUE function to find unique values in Excel, even across multiple columns. Whether you’re dealing with hundreds of rows or just a handful of data, we’ve got you covered. Let’s break down how to use this powerful tool step by step.

Check out the full video tutorial here:

What Does the UNIQUE Function Do?

The UNIQUE function is exactly what it promises — it helps you find unique values from a data set. When working with large amounts of data, it’s common to have duplicates. Whether you’re looking for unique customer names, product IDs, or sales transactions, UNIQUE allows you to pull these distinct entries in just a few steps.

Step-by-Step: How to Find Unique Values in Excel

1. Set Up Your Data

Let’s start with an example that includes over 200 rows of data. For this tutorial, we’ll pretend you’re trying to identify unique customer names from a list. The data may have people like Randall, Christina, and Mark appearing multiple times, but ideally, you want each of them to show up only once.

2. Insert the UNIQUE Function

  1. Select a Blank Cell: Start by selecting a blank cell where you’d like the results to appear.
  2. Type in =UNIQUE: This is the magic formula. If you don’t see it in your frequently used functions, manually type =UNIQUE and open the function.

Once you’ve entered UNIQUE, Excel will prompt you to choose an array — this is just a fancy way of asking where it should pull the data from.

3. Select Your Data Set

  1. Highlight the Column: If you’re pulling unique customer names, highlight the entire column minus the header. For instance, if Randall appears in row 3, 4, and 22, Excel will only pull him in once.
  2. Ctrl + Shift + Down: For quick selection, hold down Ctrl + Shift + Down to highlight the data without manually dragging.
  3. Press Enter: When you press Enter, Excel will return a list of unique names. So, even if Randall shows up ten times in your data, he’ll only appear once in the new set.

RELATED TOPIC: Excel Made Easy: Can You Spot Formulas vs. Functions?

4. Make Life Easier: Convert to a Table First

Constantly selecting a large amount of rows manually can get tedious. By converting your data into a table, this process gets much easier:

  1. Convert to Table: Highlight your data, press Ctrl + T, and Excel will automatically format your range of data into a table.
  2. Select Table Column: When you run the UNIQUE function again, simply click the drop-down arrow for the column (visible with tables). Excel will grab the entire column while neatly ignoring the header.

This eliminates the need to manually select the rows each time. It’s a small step, but trust me, it saves time when working with big data sets.

5. Pull Unique Entries From Multiple Columns

Sometimes, you need more than just unique values from a single column. Let’s say your data includes both customer names and items they bought — and you want to pull unique combinations.

For example, you want Excel to show “Randall Apples”, “Randall Grapes”, “Christina Oranges”, but only once, even if Randall has bought Apples multiple times.

This is how you do it:

  1. Select Two Columns: Start by selecting both the name column and the item column.
  2. Drag Over: When Excel asks for the array, drag your selection over both these columns.
  3. Run the UNIQUE Function: Once applied, Excel will return unique pairs like Randall + Apples, Randall + Grapes, without any duplicates, even if the same customer bought the same item several times.

RELATED TOPIC: How to Use the VLOOKUP Function in Excel

6. Find Values That Appear Exactly Once

In some cases, you may only want to find values that have appeared in your data exactly once. For example, you may want to see which products have been sold only one time.

Here’s how to do it:

  1. Open the UNIQUE Function: Type =UNIQUE and select the data array you want to analyze.
  2. Use the “Exactly Once” Option: The UNIQUE function has an optional parameter for “exactly once” — by setting this to TRUE, Excel will only return values that appear exactly one time in the dataset.

For instance, if you apply this to your customer names, it will only show you customers who made one purchase.

  1. Press Enter: After hitting Enter, Excel may return values like “Jessa”, “Carlton”, and “Winston”, indicating that these customers only showed up in the data once.

You can follow the same steps for item descriptions to find items only sold once, like “Kiwi” or “Oranges”.

Why Use the UNIQUE Function in Excel to Find Unique Values?

Excel’s UNIQUE function is a fantastic tool for anyone managing large data sets with duplicates. You can quickly analyze and filter your data, whether you’re looking for unique customer names, product IDs, or even specific transactions. The ability to pull unique data from multiple columns and filter for values that appear exactly once makes this function incredibly valuable for detailed reports and analysis.

RELATED TOPIC: How to Use Text to Columns in Microsoft Excel

Conclusion

The UNIQUE function in Excel provides an easier way to manage large datasets, streamline your reporting, and find unique values — all without breaking a sweat. Whether you’re working on a list of customer names or a complicated sales report, this function allows you to automate the process and reduce the time spent filtering through your data manually.

Try it out next time you work with Excel! Want to dig even deeper into Excel functions? Be sure to subscribe to my channel for more Excel tips and tricks.

Have ideas for future tutorials? Let me know — I’d love to cover the topics that matter most to you.

If you enjoyed this guide, be sure to give the video a thumbs up!

Want more content? Subscribe for future tutorials and leave a suggestion for what you’d like to see next!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR