# How to Filter for Duplicates with Conditional Formatting

Bottom line: In this video you will learn one technique to quickly filter a column for duplicate values.

Skill level: Beginner

## Video: Filter for Duplicates with Conditional Formatting

Double-click video to watch full screen HD.

The video above is a sample from The Filters 101 Course.  This is an online course that covers tips and techniques for filtering to help prepare and analyze your data.

## Filtering for Duplicates

There are a lot of ways to find duplicates in your data set.  The video above covers one technique using conditional formatting.  This is quick and easy technique to highlight duplicates and apply a filter to only display the rows that contain duplicates.

This technique was suggested by Jen Snyder (a member of the Excel Campus community) on my first video in the 3-part series on Excel Filters.  Thanks again for the great suggestion Jen! ðŸ™‚

I use an Excel Table in this example, but you do NOT need to use Excel Tables for this to work.  Tables just save a bit of time with this process.  Checkout my video on a Beginner's Guide to Excel Tables to learn more about this awesome tool.

Here are the steps to filter for duplicates with conditional formatting.

### Step 1: Apply Conditional Formatting for Duplicates

The first step is to select the entire column that you want to find duplicates in.  If you are using an Excel Table then you can select any cell inside the column and press Ctrl+Space Bar.

Ctrl+Space Bar is the keyboard shortcut to select the Entire Column of the Table.

Once the column is selected:

1. Select the entire column.
2. Go to the Home tab of the Ribbon.
3. Open the Conditional Formatting drop-down menu.
4. Select “Highlight Cell Rules”.
5. Then select “Duplicate Values…”
6. That will open the Duplicate Values Window.
7. Choose the formatting you want to apply and click OK.

The formatting will be applied to the cells that contain duplicates in the column.

### Step 2: Filter the Column for Font or Fill Color

We can now apply a Color Filter to the column.  If you are using an Excel Table then the Filters will already be turned on and you will see the Filter Drop-down Menus in the header row of the Table.

If you are not using a Table then you will need to turn on the Filters for the range.  Checkout my 3-Part video series on Filters to learn how to work with Filters.

To filter for font or fill color:

1. Press the filter drop-down button in the header to open the filter drop-down menu (keyboard shortcut: Alt+Down Arrow).
2. Select “Filter by Color.”
3. Then select the font or fill color that was created by the conditional formatting.  In this example I will choose the red font color because that is the font color applied by the conditional formatting.

The column has been filtered to only display rows that contain duplicates.

### Step 3 (Optional): Group the Duplicate Values with Sorting

The filtered range now shows all the duplicate values.  Those duplicates might be in different rows and look a bit scattered.

We can group the rows together by applying sorting to the column:

1. Press the filter drop-down button in the header to open the filter drop-down menu (keyboard shortcut: Alt+Down Arrow).
2. Pres the “Sort A to Z” or “Sort Smallest to Largest” option to sort the column in ascending order.

The rows will be sorted and the duplicate values will all appear together.  This makes it easier to look at the duplicates and further analyze them.

## Take Action on the Duplicates

Once we have the data range filtered for duplicates, we can then take the next steps to resolve the problem.  This might mean that we need to go to the source of the data, system or person, and determine why duplicates were created.

We also might want to create metrics on the duplicates to use for further analysis.  There are A LOT of reasons to find duplicates in our data sets, and this technique is a very quick way to highlight and filter for the duplicates to see them all in one place.

## New Lessons & Bonuses Add to The Filters 101 Course

The video above is a lesson from The Filters 101 Course.  This is one way to filter for duplicates.

There are additional video lessons in the course on how to filter for duplicates using the COUNTIF and COUNTIFS functions.  We can also use these techniques to filter for unique values.

### New Bonus Module on Advanced Filter

I have also added a new bonus module to the course on the Advanced Filter.  The Advanced Filter is another tool built into Excel that allows us to apply filters with more advanced and complex logic.  It works well for applying AND & OR logic filters with multiple criteria across multiple columns.

### New Bonus Video on Shortcuts for Excel Tables

Additionally, I added a bonus video on Shortcuts for Excel Tables.  This includes both mouse and keyboard shortcuts that will save you a lot of time when working with Excel Tables.  If you don't like Tables because of the “weird” formulas, I explain how to turn those formulas off and still benefit from all the great features of Tables.  That entire video is now included with The Filters 101 Course.

### Join Filters 101 Before the Discount Expires

The Filters 101 Course is an online course that contains simple step-by-step video tutorials.  It is packed with filtering techniques I have used throughout my career to help me prepare and analyze data faster.

This course, plus my new Filter Mate Add-in will save you a ton of time when working with filters in Excel.

You can currently save over 40% when you buy Filter Mate & Filters 101.  The discount expires this Friday at midnight PST.

• Kris says:

Great Tip! I wouldn’t have thought about using conditional filters to find duplicates. Thanks!

• Thanks Kris! We can also use conditional formatting to find unique values. Here is a screenshot.

We can select Unique from the drop-down in the Duplicate Values window.

The rest of the process would be the same to filter for unique values by filtering for font or fill color.

• Shannon says:

I had no idea you could search for unique values like this, my jaw literally dropped. This will help me so much! THANK YOU!!!

• Thanks Shannon! There are quite a few ways to search for duplicates, but I really like this method because of how visual it is. It makes it really easy to see the duplicates and work with them. Especially when you are sending the file back to someone else to fix it. ðŸ™‚

• Phil Pickman says:

Excellent presentation. Good idea. I’ll probably use it.
Thanks.

• Thanks Phil! It’s a good one to have in the toolbox. ðŸ™‚

• Vy says:

I love all your video, such a great tips!!! It is very kind of you to created all these video and share with everyone. Greatly appreciated your times and your kindness!!!

• Thank you so much Vy! I really appreciate your support, and happy to hear you are enjoying the videos. Have a great day! ðŸ™‚

• Ivars says:

I am expert in Excel myself, but this was very new to me! Great tip! I have had various solutions for this with a help column (e.g. not showing the first of duplicates just the second and next values). This is great when you need just an easy highlight.

• Thanks Ivars. I agree it’s a nice quick way to see all the duplicate values.

• Gieselle says:

Thank you! This was a good resource on how to address duplicates. It helped me a lot!

• Crysler says:

If there are no duplicates in the selected data, will the “filter by color” appear grayed out (not as an option to click)?

• Bryan says:

Yes, that’s correct. For testing purposes, try adding a duplicate at the very end of the column. You will notice that once a duplicate is present within that column, the “filter by color” option will no longer be grayed out.

• How to highlight lines that are “too long” with conditional formatting – a great example of how to use CF for quality control.

• Ahsan says:

Thanks for the informative video.
I am facing an issue to filter the data after conditional formatting.
I utilized the feature “hight the double values in Column A & B”. The excel marked the double values in both columns. But am not able to filter that. The excel hanged everytime when i click on filter button on the column.

• E says:

thank you for this! made my work easier ðŸ™‚

• Jaime says:

Thanks Jon! Nice and helpful article.

Generic filters
Exact matches only

#### Excel Shortcuts List

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List