In Excel, you may find yourself working with data that contains duplicate values. It’s often useful to highlight these values so that you can see them easily. Let’s learn how to find duplicates in Microsoft Excel.
How to Find Duplicates in Excel
Consider a three-column dataset containing lists of names. There are some repeating names that you can easily pick out. But with a large list of data, it would be easy to overlook duplicates. Fortunately, Excel has a quick way of finding duplicates so they become obvious.
To get started, click and drag to select the data that you want to analyze. In this example, the data lies within cells in the range A1:C5.
Next, go to the Home tab on Excel’s ribbon. On the right side, you’ll find the Conditional Formatting dropdown. Conditional Formatting is a handy Excel feature that applies color-coded shades to cells based on conditions that you select.
Go ahead and click on the dropdown. From the list of options that appears, hover over Highlight Cells Rules. Then, from the new window that opens, choose Duplicate Values.
Excel will launch the New Formatting Rule menu. You can use the default options that automatically populate. The standard conditional formatting shade here is red, but you can choose colors like yellow and green from the Format With: dropdown.
When you’ve made your selections, go ahead and click OK. Watch as all values that appear more than once in the dataset are highlighted in red.
This makes it very easy to visually pick out duplicate values in your data. It’s far better than manually filtering, risking missing duplicates in large datasets.
Histograms in Excel