How to find duplicates in Excel

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 in the form of rows and columns within cells. There are also functions within the software 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.

See also: How to check the storage on Windows 10

How to find duplicates in Excel

While duplicates may be mistakes, they could also be purposefully placed there 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. To change to a different sheet, click the name of the sheet at the bottom of the Excel application.
  2. Select the area of data that you wish to analyze and check for duplicates. If you are selecting cells adjacent to one another, drag your cursor across the cells. Alternatively, you could select a single cell, then hold down the shift key on your keyboard and use the arrow key to select other cells. If you are selecting non-adjacent cells, click on one cell, then hold down the ctrl key on your keyboard and use your cursor to select other cells. For selecting full rows or columns, click on the number listed beside the row or column.
  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. From the pop-up that appears, choose the last option: Use a formula to determine which cells to format.
  4. In the Rule description box, there is 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 be changed to 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 be displayed when the software has located the duplicates by clicking on the button labeled Format. You can change the font, color fill of the cell or border of the cell. 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.

Get more stuff like this
in your inbox

Subscribe to our mailing list and get interesting products and updates to your email inbox.

Thank you for subscribing.

Something went wrong.