How-To

How to Filter Unique Values in Excel

By Sandy Writtenhouse

Published April 5, 2021

When you’re trying to track down distinctive data in a column or cell range in a sheet, here are two ways to filter unique values in Excel.

There are several things we may need to do to analyze data in our Excel spreadsheets. It could be as simple as finding duplicates or something more complex like answering “what if” questions using the Goal Seek feature.

Luckily, Microsoft Excel offers tools such as filters for locating those basic pieces of data like the above-mentioned duplicates. And at the same time, you can use filters to find the opposite, unique values. Plus, if you take advantage of the advanced filter feature, you can display the data you find in a specific spot.

Here, we’ll show you two ways to filter those unique values in Excel. So, you can use whichever one will work best for your situation.

How to Filter Specific Unique Values in Excel

You can filter unique values that are specific to your data for anything from numbers to text. For example, you may have a sheet of employee email addresses and want to find those without your company domain. Or perhaps you have phone numbers for customers and want to see those from outside your area code.

Using a basic filter in Excel with the “does not equal” setting, you can easily locate these types of data.

1. Select the column(s) you want to apply a filter to in your sheet.
2. Go to the Data tab and click Filter in the Sort & Filter section of the ribbon. This will apply an arrow to the column header for the filter.
3. Click the arrow to display the filter and move your cursor to Number, Date, or Text Filters depending on the data type.
4. In the pop-out menu, pick Does Not Equal. If you don’t see this option, choose Custom Filter at the bottom.
5. Confirm the first drop-down shows does not equal and then enter the value in the box to the right.
6. Click OK.

You’ll see your sheet update to display data that contains those unique values.

And when you finish using the filter, just click the Filter button in the ribbon again to turn it off. Your sheet will then return to normal.

How to Filter Any Unique Values in Excel

For another way to find unique values, you can use an advanced filter. This works a bit differently in that it will locate all distinctive values in your data range. This is useful when you expect all data to be exactly the same. And it’s handy for finding errors too. For instance, maybe you changed prices for a product and want to see if you missed one. Or perhaps you want to locate misspellings for a company name.

Using the advanced filter, you can see the values in a different location, like the adjacent cells. But you can also display them within the list of data you select if you prefer.

1. Select the cells(s) you want to apply a filter to in your sheet. Keep in mind that if you choose the whole column, this will filter a column header if you have one.
2. Go to the Data tab and click Advanced in the Sort & Filter section of the ribbon.
3. In the Advanced Filter pop-up window, choose the location for the filtered data under Action. You can pick Filter the list, in-place or Copy to another location (see more below). If you decide to copy to another spot, go down to the Copy to box and enter the location. For example, you can choose a cell range to the right by dragging through those cells.
4. Confirm the List range to be sure you’re filtering the exact data you want.
5. Check the box for Unique records only. This is the most important part of the filter to find those distinctive values.
6. Click OK.

One thing to consider with Step 3 is that if you copy the filter results to another location, it may be more difficult to adjust the data if needed. By filtering the list in-place, your sheet will update so that any pertinent data will still be “attached” and viewable as in the following screenshot.

In the below screenshot, the filtered data was copied to the cells on the right. You can see here that all you receive is the unique values. The rest of the sheet remains the same.

So, depending on the type of unique values you seek, be sure to choose the Action in the Advanced Filter pop-up for the filter results location that works best for you.

Find Data Fast When You Filter Unique Values in Excel

If you have a lengthy spreadsheet, filters can help you find the data you need much faster than a line-by-line review. And for those truly distinctive pieces of data, keep these two types of filters in mind.

And if you use Google Sheets in addition to Excel, check out how to create and use filter views in Sheets.