How To Filter Out Duplicates In Excel

Bottom line: In this video you will learn one technique to chop-chop filter a column for indistinguishable values.

Skill level: Beginner

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

Video: Filter for Duplicates with Conditional Formatting

Double-click video to lookout man full screen Hard disk.

The video in a higher place is a sample from The Filters 101 Course. This is an online form that covers tips and techniques for filtering to help prepare and analyze your information.

Download Excel File

Download the Excel file to follow along.

Filter For Duplicates With Provisional 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 like shooting fish in a barrel 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 customs) on my showtime video in the 3-function series on Excel Filters. Thanks again for the bang-up suggestion Jen! 🙂

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

I use an Excel Table in this instance, but you do NOT need to utilise 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: Utilise Conditional Formatting for Duplicates

The kickoff footstep is to select the entire column that you want to discover duplicates in. If y'all are using an Excel Table then you lot tin select whatever prison cell within the column and printing Ctrl+Infinite Bar.

Ctrl+Space Bar is the keyboard shortcut to select the Unabridged Cavalcade of the Table.

One time the column is selected:

Select the entire cavalcade.
Become to the Home tab of the Ribbon.
Open the Conditional Formatting drop-down menu.
Select "Highlight Prison cell Rules".
Then select "Duplicate Values…"
That volition open the Duplicate Values Window.
Choose the formatting you want to use and click OK.

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

Pace two: Filter the Column for Font or Fill Color

Nosotros can now use a Color Filter to the column. If you are using an Excel Table and then the Filters volition already exist turned on and you will see the Filter Driblet-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-Function video serial on Filters to learn how to work with Filters.

To filter for font or make full color:

Press the filter driblet-downwardly button in the header to open the filter drop-downwardly card (keyboard shortcut: Alt+Downwardly Arrow).
Select "Filter by Colour."
Then select the font or make full colour that was created by the conditional formatting. In this example I will choose the crimson font colour because that is the font color practical by the conditional formatting.

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

The cavalcade has been filtered to only display rows that incorporate 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 exist in different rows and look a flake scattered.

Nosotros can grouping the rows together by applying sorting to the cavalcade:

Press the filter drib-down button in the header to open up the filter drop-down menu (keyboard shortcut: Alt+Down Pointer).
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 volition be sorted and the duplicate values will all appear together. This makes it easier to look at the duplicates and farther clarify them.

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

Take Action on the Duplicates

Once we accept the information range filtered for duplicates, we can and then take the next steps to resolve the trouble. This might mean that we demand to go to the source of the data, arrangement or person, and determine why duplicates were created.

We also might want to create metrics on the duplicates to use for further assay. At that place are A LOT of reasons to observe 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 together to The Filters 101 Grade

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

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

There are boosted video lessons in the grade on how to filter for duplicates using the COUNTIF and COUNTIFS functions. Nosotros tin can also use these techniques to filter for unique values.

New Bonus Module on Avant-garde Filter

I have besides added a new bonus module to the course on the Advanced Filter. The Avant-garde Filter is another tool congenital into Excel that allows usa to apply filters with more than 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 relieve you a lot of time when working with Excel Tables. If you lot don't like Tables because of the "weird" formulas, I explain how to plow 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 Grade is an online course that contains simple step-past-pace video tutorials. It is packed with filtering techniques I accept used throughout my career to help me fix and clarify data faster.

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

filter-mate-and-filters-101-logos