How To Filter for Unique Values in Excel in 7 Steps

Microsoft Excel is a useful program that allows you to create spreadsheets to organize and interpret numerical and nonnumerical data. It includes a variety of features that allow you to sort, organize and transform your data easily to help make it more meaningful. Learning about these different functions may help you work more efficiently and assist you in identifying trends in your data.

In this article, we define what unique values in Excel are, discuss when you may use or encounter unique values, explain what the UNIQUE function is and outline how to perform various actions with unique values.

What are unique values in Excel?

Unique values in Excel refer to any values in a range that are different from other values in a range. These unique values may be numerical or non-numerical values.

• For example, if a range contains the five values 100, 200, 200, 300 and 400, only the four values 100, 200, 300 and 400 are unique values.

• Similarly, if a range contains five values like Smith, Smith, Johnson, Roberts and Roberts, the three unique values are Johnson, Roberts and Smith.

Related: 25 Best Excel Tips To Optimize Your Use

When to use unique values in Excel

Unique values in Excel may help you identify trends within your data. Looking for unique values allows you to determine how often a specific value occurs. For example, a teacher may use this function to review the different scores that students earned on an exam to help evaluate what the average performance was.

Using unique values may also be helpful for identifying duplicates on the worksheet. This may be useful for removing unnecessary duplicates and ensuring the records are accurate. For example, a bookkeeper may use this function to check their entries to confirm they didn't record the same transactions multiple times.

Related: How To Sort Data in Excel: A Step-by-Step Guide

How to filter for unique values in Excel

Follow these steps to filter for unique values in Excel:

1. Select the range of cells or table that you want to filter for unique values.

2. Click on "Data."

3. Navigate to "Sort & Filter."

4. Select "Advanced."

5. Press "Filter the list, in-place."

6. Check the box for "Unique records only."

7. Click "OK."

Excel also allows you to filter for unique values and copy the results to a different location. Here are the steps to do so:

1. Select the cell range or table you want to filter for unique values.

2. Press "Data."

3. Locate the "Sort & Filter" group.

4. Click on "Advanced."

5. Select "Copy to another location" in the "Advanced Filter" pop-up box

6. Type the cell reference where you want to paste the results in the "Copy to" box.

7. Choose "Unique records only."

8. Press "OK."

Here is another method of filtering for unique values and copying the results to a separate location:

1. Choose the table or range of cells that you want to filter for unique values.

2. Click on "Data."

3. Find the "Sort & Filter" group.

4. Select "Advanced."

5. Choose "Copy to another location" in the "Advanced Filter" pop-up box.

6. Click on "Collapse Dialog."

7. Choose the cell in the worksheet where you want to copy the information.

8. Click on "Expand."

9. Select "Unique records only."

10. Click "OK."

How to remove duplicate values

Removing duplicate values allows you to eliminate any repeat occurrences of unique values. Excel only keeps the first time the unique value appears, and it permanently deletes any repeat occurrences of the same value. This does not affect the values outside of the selected table or cell range.

Here are the steps to follow to remove duplicate values:

1. Choose your table or range of cells.

2. Navigate to the "Data" tab.

3. Locate the "Data Tools" group.

4. Select "Remove Duplicates."

5. Choose which columns you want to clear of duplicates by choosing one or more under "Columns," selecting all columns with "Select All" or clicking "Unselect All" to clear your initial choice of columns.

6. Click "OK."

7. Review the message about how many duplicates Excel removed the range or table.

8. Click "OK."

It's important to remember that removing duplicates deletes the data permanently. It may be helpful to make a copy of the data before making changes. However, you're able to undo the change by clicking "Undo" or using the shortcut "CTRL+Z" on most keyboards.

Related: 10 Common Excel Formula Errors (And How To Fix Them)

What is the UNIQUE function?

Some versions of Excel include the UNIQUE function. This function allows you to extract a list of unique values from an array or a range and works with all types of data. Here is the syntax for the Excel UNIQUE function:

UNIQUE(array, [by_col], [exactly once])

Here's what each aspect of this function represents:

• Array: Array is a required aspect that specifies the array or range from which to return unique values.

• By_col: By_col is an optional element to indicate how to compare the data, such as "TRUE" for data across columns or "FALSE" to compare data across the rows, which is also the default if you omit this element.

• Exactly_once: Exactly_once is an optional logical value to define whether a value is unique, such as "TRUE" to return all values that occur once or "FALSE" to return all distinct values, which is the default if you omit this element.

Related: Basic Excel Formulas and How To Use Them

How to use the UNIQUE function to extract unique values

Here's an example of how to use the UNIQUE function to extract unique values and output them into a column:

1. Select the cell where you want to place your unique values.

2. Click on the formula bar.

3. Type the equal sign.

4. Add "UNIQUE."

5. Type a set of parentheses.

6. Enter the cell range that you want to extract unique values from inside of the parentheses, such as C2:C11.

7. Press "Enter."

Here's an example of how to use the UNIQUE function to extract unique values and output them into a row:

1. Select the cell where you want to place your unique values.

2. Access the formula bar.

3. Enter the equal sign.

4. Type "UNIQUE."

5. Add a set of parentheses.

6. Open a set of parentheses.

7. Type the cell range you want to extract unique values from, such as A2:J2.

8. Add a comma after the cell range.

9. Type "TRUE."

10. Close the set of parentheses.

11. Press "Enter."

Please note that none of the companies mentioned in this article are affiliated with Indeed.