How to Find a Duplicate in Excel

By Melissa McKean

Microsoft Excel is a powerful data management tool that uses formulas and conditional formatting to organize and analyze information. However, sometimes Excel can be so complex that you are unable to determine exactly what formula is required to analyze that data. Finding duplicate information in Excel is possible using conditional formatting. If you need to find duplicate records in multiple cells or rows, use the Microsoft Excel conditional formatting feature.

Things You'll Need

  • Microsoft Excel spreadsheet

Step 1

Open a worksheet in Microsoft Excel that contains data with duplicate entries. Click on the first cell that contains duplicate information. For instance, if you wanted to find all of the occurrences of the word “Apple” in your spreadsheet, click on the first “Apple” in the list.

Step 2

Click on “Format,” in the menu, and then select “Conditional Formatting.” The conditional formatting box will open, and under “Condition 1,” you will see a drop down menu. Click on the drop down menu and select “Formula Is.” A space will appear next to the “Formula Is” that allows you to type in it, and there will be a little square icon with small square dots at the end of the field.

Step 3

Type the following formula in the space next to “Formula Is:” =COUNTIF(A:A,A1)>1 Be sure to include the equal sign, parenthesis, colon, comma, and greater than symbol in the formula, or it will not work correctly.

Step 4

Click the button that says, “Format…” Choose a color that will stand out. This will make all of the duplicate entries of “Apple” change to that text color so you can see them in your list. Click “OK.” The cell you have selected should turn the color that you chose for your formatting.

Step 5

Keep the cell highlighted and click on the paintbrush icon, or the “Format Painter” icon in the toolbar. If you don’t have this button in your toolbar, you can find it by clicking on “View” in the menu, highlighting “Toolbars,” and selecting “Formatting” in the menu that appears next to the cursor. The “Format Painter” icon will then be added to your toolbar.

Step 6

Highlight the rest of the list in which you are looking for duplicates of the word, “Apple.” Do not highlight the first occurrence of “Apple,” however that cell may continue to blink because the formatting has been copied. After you have highlighted the entire row, release the mouse. All of the instances of “Apple” should have turned the specified color.

References & Resources