Excel - Unique Values with Advanced Filter & 3 Other Ways - Episode 2045

Microsoft Excel Tutorial: Microsoft Excel - 4 Methods to Get a List of Unique Values.

Welcome to another episode of the MrExcel podcast, where we dive into all things Excel. In today's episode, we will be discussing how to extract unique values from a database using various methods. Our goal is to get a list of unique customers from a database, and we will explore four different ways to achieve this.

The first method we will cover is the old-fashioned way of using the Advanced Filter. This involves selecting the data, going to the Data tab, and selecting Advanced Filter. From there, we can choose to copy the data to a new location and select the option for Unique Records Only. This will give us a list of unique customers, but there are faster and more efficient ways to achieve this.

The second method is by using a PivotTable. Simply go to the Insert tab, select PivotTable, and choose to place it on an existing worksheet. Then, select the Customer column and voila, you have a list of unique customers.

Next, we will explore the Conditional Formatting feature, which allegedly has the ability to highlight unique values. However, this feature only marks values that appear exactly once, which is not very useful. But fear not, we can use a formula to achieve the same result. By using the COUNT function and conditional formatting, we can highlight all unique values in a list.

The final and most efficient method is by using the Remove Duplicates feature. This can be found under the Data tab and will quickly give us a list of unique values without any extra steps. However, if you need to do this with a formula, we have an insanely amazing one from my book "Ctrl+Shift+Enter" that will do the trick.

Speaking of books, my colleague Mike has a fantastic book called "MrExcel XL" that covers an array of formulas, including how to extract unique values from a list. I highly recommend checking it out by clicking the "i" in the top-right corner.

In conclusion, we have covered four different ways to extract unique values from a database in Excel. Whether you prefer the Advanced Filter, PivotTable, Conditional Formatting, or Remove Duplicates, there is a method for everyone. And if you need to do it with a formula, be sure to check out Mike's book for an amazing array formula. Thanks for tuning in and we'll see you next time for another episode of the MrExcel podcast.

#excel
#microsoft
#microsoftexcel
#exceltutorial
#exceltips
#exceltricks
#evergreen
#excelfilter

This video answers these common search terms:
can i remove duplicates in advanced filter excel
how to delete duplicates in excel using advanced filter
how to conditional format duplicates in excel
how to use conditional formatting in excel for duplicate
how to use excell conditional formatting duplicate
how to pivot unique values in excel
how to display unique values in excel pivot
get a unique list from a filter in excel
how find unique values in excel
how to see unique values only on excel
remove duplicate numbers in a column in excel
remove duplicates using a excel formula
how remove duplicate in excel
how remove duplicate rows in excel
how remove duplicates excel
how to remove a duplicate from excel
how to remove duplicate in excel

Table of Contents:
(00:00) Extract Unique Values from Excel!
(00:11) Goal: obtaining a unique list of customers from a database
(00:21) Using the old method of Advanced Filter
(00:31) Faster method: Inserting a PivotTable
(00:56) Attempting to use Conditional Formatting
(01:16) Explanation of why Conditional Formatting does not work for this task
(01:31) Alternative method using a formula and Conditional Formatting
(02:05) Excel 2010's "Remove Duplicates" feature
(02:34) Options for obtaining unique values without advanced Excel skills
(03:27) Clicking Like really helps the algorithm

How to get a list of the unique values
Advanced Filter with Unique Values Only.
Unique Values from a Pivot Table.
Conditional Formatting Formula =COUNTIF(G$1:G1,G2)=0.
Remove Duplicates in Excel.
Array formula from Mike Girvin's book.

[excelisfun]

Unique Excel fun!!! Thanks for the Unique Plug!! : )

[pmsocho]

So many ways to have fun with Excel! But Bill, you've missed the hottest method which is Power Query :)

[DhavalRaval]

Hi Bill,
I have subscribed to your channel and watching every video of your's.
However, currently I have one question for you, so please help me out because in my office I have to use filter almost in each and every worksheet on daily basis that's why.

HOW TO GET HIDE OPTION AFTER APPLYING FILTER?

I want to hide rows and columns at once while having a huge data records in an excel sheet.