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.

More on this topic

Best RAM for Gaming in 2020

If you’re planning to get your gaming PC assembled, then finding the right RAM is one of the most important things to consider. As technology gets better and better with

Top Best Tablets for Musicians to buy in 2020

With newer and advanced technology, music consumption in many ways has changed from its analog form to purely digital. The traditional methods of rendering and listening to music are still

Top Best 5G phones in 2020

The smartphone industry is undergoing a major revolution and the best 5G phones are the way to go if you’re looking forward to future proof your purchase. All major networks

Top Best Rugged Laptops in 2020

The scenario of laptop usage varies drastically from person to person. While most professionals and students use it in a safe environment, there are the select few whose profession or

Top Best RAM to buy in 2020

Building a new PC to use as a workstation or for gaming is an experience on its own. It could be a really intimidating experience but still a lot of

Leave a Reply