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.
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.
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.
