How to Filter for Duplicates with Conditional Formatting - Excel Campus
10

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

filter-for-duplicates-with-conditional-formatting-cover-640

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.

Download Excel File

Download the Excel file to follow along.

Filter For Duplicates With Conditional Formatting.xlsx (619.4 KB)

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! 🙂

how-to-filter-for-duplicates-with-conditional-formatting-in-excel

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…”
    apply-conditional-formatting-for-duplicate-values
  6. That will open the Duplicate Values Window.
  7. Choose the formatting you want to apply and click OK.
    conditional-formatting-for-duplicate-values-window

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.

filter-by-the-font-color-applied-by-the-conditional-formatting

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

filter-for-duplicates-values-with-conditional-formatting

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.

sort-duplicates-values-column-to-group-duplicates-together

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.

sort-column-to-display-duplicate-values-in-adjacent-rows-2

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.

new-online-course-filters-101-excel-campus

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.

advanced-filter-bonus-module-from-filters-101-course

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.

bonus-video-on-tips-and-shortcuts-for-excel-tables

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.

filter-mate-and-filters-101-logos

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

Click here to learn more about The Filters 101 Course & The Filter Mate Add-in

Please leave a comment below with any questions.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 10 comments
Ivars - December 11, 2016

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.

Reply
Vy - December 8, 2016

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

Reply
    Jon Acampora - December 10, 2016

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

    Reply
Phil Pickman - December 8, 2016

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

Reply
Kris - December 8, 2016

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

Reply
    Jon Acampora - December 8, 2016

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

    Highlight Unique Values with Conditional Formatting in Excel

    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.

    Reply
      Shannon - December 8, 2016

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

      Reply
        Jon Acampora - December 10, 2016

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

        Reply

Leave a Reply:

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x