#Excel find duplicates in named list how to#

This is so you can easily copy your COUNTIF later to other cells in your helper column after you write one.Īfter that, don’t forget to write the “>1” condition after you write your COUNTIF. Write this COUNTIF in the first cell of your helper column.ĭon’t forget to add dollar symbols in front of your row and column coordinates in your COUNTIF cell range input. Then, input the cell which is in parallel with the cell in the helper column where you currently type your COUNTIF. Input the cell range which contains all the data where you want to identify the duplicates of to your COUNTIF.

=COUNTIF(data_cell_range, data_to_check_for_duplicates) > 1 It is pretty easy to use the conditional formatting to identify those duplicates, right? Use conditional formatting if you prefer it compared to the other methods given in this tutorial! The duplicates on your cell range will have been highlighted using the style you choose!

You can also modify the marking style on your duplicates by choosing it from the Format with… dropdown.Īfter you have chosen, click OK. Make sure the dropdown there indicates that you want to mark the duplicates in your cell range. Click the one with the Duplicate Values… label on it.Įxcel will show you the New Formatting Rule dialog box. This will show other choices inside the Highlight Cell Rules choice. Put your pointer on the Highlight Cell Rules choice in the list shown. Then, click on the Conditional Formatting button in the Home tab. In the example, that means we highlight the data in the first name column in our data table. How do we do it using conditional formatting?įirst, you need to highlight the cell range where you want to check the duplicates on. Say we want to identify the duplicates in the “First Name” column there. We will explain the steps below with an example to help you understand them better.įor the example itself, let’s say we have a data table like this in our excel file. The steps to utilize this method are pretty simple too and you should be able to master it fast. That makes it easier for us to find duplicates using this feature. The first method to find duplicates in excel is by using the conditional formatting feature.Ĭonditional formatting has a special menu to identify the duplicates we have in an excel cell range.

#Excel find duplicates in named list how to#

How to Find Duplicates in Excel Using Conditional Formatting