Technobezz

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 Electric Pressure Cookers in 2020

A pressure cooker is a useful cooking appliance as it can reduce the cooking time by up to 70%. They work by raising the boiling point of water inside them

Best Vacuum Sealers in 2020

Cooking at home can be fun and entertaining for the entire family. With the help of the best vacuum sealers, you can save time while keeping most of your kids’

Best Food Processors of 2020

Food processors have been in the market for a long time now and with newer and better technology these days, you get far more value and versatility in these appliances.

Best Phablets To Buy In 2020

Do you find a tablet too big for your needs and a phone too small? Well then, a phablet is the perfect device for you. These huge phones affectionately known

Best Smartphones under $100 to Buy in 2020

Flagship smartphones from the top OEMs are quite expensive, but there are also many affordable. There are plenty of good options in this range as well. Motorola has plenty of

Leave a Reply