How to Find Duplicate Values in Excel

Duplicate Values in Excel

Thankfully, if you have Microsoft Excel 2007 or above, finding duplicate values in your excel spreadsheet is relatively easy. First, highlight the range of cells you want to test. You can do this the typically way by clicking and dragging, or you can select a single cell and hit CTRL-A.

Next, find Excel’s Home tab. From the home menu, find the Styles grouping. Once there, click Conditional Formatting > Highlight Cells Rules > Duplicate Values. Once you click Duplicate Values, a dialog box will appear. Select the formatting style you wish to use to identify the duplicates, and then click OK to filter for duplicates. If there are a number of duplicate values, however, it can sometimes be difficult to comb through the list and find them. Fortunately, isolating duplicate values is also relatively easy.

First, right-click on one of the duplicate cells. Click Filter from the drop-down menu, and then choose Filter by Selected Cell’s Color. This will collapse the original list until it’s only showing you the duplicates. Better still, the duplicate values will remain highlighted while you’re working with them, so they are easy to find as you conduct your business. To remove the highlighting from the duplicate values, simply go back to the home menu. Click Conditional Formatting > Clear Rules > Clear Rules from Entire Worksheet. This will remove the highlights from your duplicate values so that you can continue to work on or share your spreadsheet as normal.

business

If you don’t want to highlight your values at all, there is another way to isolate duplicate values in your excel sheet. First, identify the column in your spreadsheet that you wish to test. In the second cell (A2), add the formula =COUNTIF (A:A, A2). Once you’ve entered this formula, double click on the fill handle in that cell to copy the formula down the entire column. Once this is done, you can filter the column for any values greater than one. Click on the first cell in the column (A1). Find the Data tab on the Excel ribbon, and choose Filter from the menu. Click the Filter arrow that appears in cell C1, choose Number Filters from the menu, and then select Greater Than. Enter 1 in the box that appears, and click ok to filter your column for duplicate values.

Related Articles

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top