How to find duplicates in Excel

Excel is a software program provided by Microsoft. It is usually part of the Microsoft 365 suite for home or business. Excel allows users to record data in a spreadsheet. The data will be presented in a sheet of rows and columns within cells.

The software also uses functions to extract the data and present them in pie charts or graph forms. It is especially useful to use when recording data within similar categories. However, it can be hard to identify mistakes from large amounts of data. Hence, here are ways to locate duplicates in Excel.

How to find duplicates in Excel

While duplicates may be mistakes, they could also be purposefully placed as fundamental data. Hence, there are different methods used to extract duplicates.

Method 1: To highlight all existing duplicates

1. On your device, pull up the Excel file with the sheet you want to analyze. Click the sheet's name at the bottom of the Excel application to make changes to a different sheet.
2. Select the data area you wish to analyze and check for duplicates. If you select cells adjacent to one another, drag your cursor across the cells. Alternatively, you can choose a single cell, hold down the shift key on your keyboard and use the arrow key to select other cells. If you are choosing non-adjacent cells, click on one cell, then hold down the ctrl key on your keyboard and use your cursor to select other cells. Click on the number beside the row or column to choose full rows or columns.
3. From the tools above your spreadsheet, ensure you are on the Home option. It should be the second option from the left.
4. Select the Conditional Formatting option in the tools available. It should be located in the center of the screen.
5. Then select Highlight Cell Rules from the drop-down menu that appears. It should be the first option on the drop-down menu.
6. A new drop-down menu will appear. Click on the option named Duplicate values. This is likely the last option on the list.
7. When a pop-up appears, you may choose the color to highlight cells with duplicated values. Do take note that all duplicates will be highlighted in the same color.
8. Select OK.

Method 2: Locating cells with specific values

1. Follow steps 1 to 4 from method 1.
2. From the dropdown menu that appears, select the option, New Rule.
3. Choose the last option from the pop-up that appears: Use a formula to determine which cells to format.
4. The Rule description box has a bar to type in a formula. Key in this formula: =COUNTIF($A$1:$B$30,A1)=3. $A$1 can be changed to the top-most cell on the far-left you have selected. $B$30 should change the description of the bottom-most cell on the far-right within your selected range. A1 should be changed to the cell with the value you are looking for. The portion of the formula (=3) is the count you want to check. It can be changed to a different mathematical operation, such as <2 or >5.
5. Select the format you want to display when the software has located the duplicates by clicking on the Format button. You can change the font, color fill, or cell border. This will help you locate the cells with duplicate values easily.
6. Choose the OK option in the format window and the formula description window.

Closing advice

Using these methods, you should be able to locate duplicates within your spreadsheets and fix mistakes easily.