How to find duplicate rows in Excel

Maybe you are working with a lot of information in Excel. Duplicate rows do not make the process easier. You’ll want to remove these to make your database readable, neat, and tidy. However, before you delete them, you will need to find them first. Fortunately, some methods and functions automatically identify these rows.

Continue reading to learn how to find duplicate rows in Excel.

How to locate duplicate rows in Excel using the COUNTIF formula

You will need to use the COUNTIF formula in Excel’s formatting option to identify and highlight duplicate rows. Here’s how to do it:

1. Select the desired range where you want to check for duplicate rows. If it is the entire book, use the CTRL + A command.
   
2. Navigate to the home tab and select the “conditional formatting” option in the styles group. It’s the grid icon with blue, white, and red squares.
   
3. Select the “new rule” option and “use a formula to determine which cells to format.”
   
4. Add the formula COUNTIFS($A$2:$A$100, $A2, $B2:B$100, $B2)>1 in the box and choose a formatting style.
   
5. Select “OK.”
   

However, note that the formula assumes that the data starts at A2 and ends at B100. You should change the function based on your specific range and add arguments for any additional columns (like “, $C$2:$C$100, $C2“) to the COUNTIF formula.

Excel will automatically highlight all duplicate rows within the entire selected range.

If this doesn’t work, copy the formula into an auxiliary column for each row and format it according to the result of that cell.

Highlight duplicate values ​​in Excel

This is the easiest way to identify duplicate values ​​in your workbook. You won’t have to use complicated functions, just the home tab. Here’s how to do it:

1. Select the entire range in which you want to check for duplicate rows.
   
2. Go to the home tab and select the “Conditional Formatting” option in the styles group. The icon shows a grid with red, white and blue squares.
   
3. In the opened tab, select “Highlight Cell Rules” and then select the “Duplicate Values” option.
   
4. Select the formatting style you want in the window that appears and click “OK.”
   

Excel will then highlight any duplicate values ​​within the selected cells. You can even change the highlight color within the formatting pop-up menu when it appears.

This is an imperfect method since it identifies duplicate values ​​instead of entire rows. It may produce inaccurate results if a row contains duplicate cells in the columns.

However, it can be much easier to implement and view in smaller spreadsheets.

Highlight triplicate values ​​in Excel

If you want to clean up your workbook, you can also identify triplicate values. The process requires creating a new rule instead of using Excel’s default formatting options. Here’s how to do it:

1. Select the desired range of values ​​where you want to check for triplicates.
   
2. Go to the Home tab and select the “Conditional Formatting” option in the Styles group.
   
3. Select the “New rule” option.
   
4. Click “Use a formula to determine which cells to format.”
   
5. Use this formula: =COUNTIF($A$2:$A$100, A2, $B2:B$100, B2)=3. Using this formula checks if the row appears three times within the chosen range. Again, this assumes you are using a range from A2 to B100.
   

It’s as simple as that! Excel will identify and highlight triple values ​​within the selected range. However, note that the formula above identifies triple values ​​for the cell you set (identified based on (($A$2).

You can simplify the formula with COUNTIF($A$2:$B$10, A2) instead. This only checks the contents of the current cell within the range, so it will highlight cells in rows and columns.

Remove duplicates and triplicates in Excel

Perhaps you have highlighted all the duplicates or triplicates in your workbook using the method above. Afterwards, you may want to delete them. This will make your entire workbook more organized and less confusing.

Fortunately, removing duplicates once highlighted is a simple task.

Here’s how to do it:

1. Select the highlighted rows by clicking the row numbers while holding down CTRL (Command on Mac). This will cause Excel to select all highlighted rows.
   
2. Hover over one of the selected row numbers, right-click and select the “Delete” option.
   

Deleting rows can alter the overall formatting of your book. You should also consider logging the information in the deleted rows in case you need it later. There are also a few other ways to quickly remove highlighted duplicate and triplicate rows:

1. Select the highlighted rows by clicking their numbers while holding down the CTRL/Command key.
   
2. Navigate to “Home”, located in the top ribbon of Excel, in the “Cells” option,
   
3. Select “Delete Sheet Rows.”
   

Another convenient way to delete rows is by using a command once you highlight them. Just use the key shortcut “CTRL + -“, “Cmd + -“. This will delete all the selected rows (or values) and you are done.

When to highlight and remove duplicates and triplicates in Excel

There are some cases where highlighting duplicates and triplicates and removing them will be useful for your workbook. Even if you don’t need it immediately, the above methods can be used for some of the following real-world examples:

• Database entry errors: You may accidentally enter the same information twice. With such errors, highlighting duplicates can be an easy way to identify and subsequently eliminate the errors.
• Database merge errors: Sometimes, you will need to combine multiple databases into one. During the merging process, additional errors may occur resulting in duplicate rows.
• Email lists and customer database duplicates: Duplicates in customer databases can result in customer marketing material, such as the same newsletter, being sent twice. This can be annoying and deter customers from your service. Deleting these rows is the best course of action.
• Inventory Management Issues: Every product must be accounted for in warehouse inventory management environments. If duplicates and incorrect data are left in important databases, this can lead to management discrepancies. Deleting rows in these cases leads to much more accurate tracking.

Other spreadsheets that need identification of duplicate rows include employee records, research, surveys, and financial data. Removing the extra rows will increase accuracy, save time searching for relevant information, and make data analysis easier.

Other Useful Excel Methods You May Need

Duplicating rows is just one aspect of a wide range of useful Excel skills. You won’t need to spend hours studying them. They can be done with just a few simple keyboard commands. For example, you may need:

• Copy Rows: Simply select the rows you need and then use the CTRL + C command.
• Paste Rows – Once the rows have been copied, you can go ahead and paste them using the CTRL + V command. Alternatively, you can right-click and select the “Insert Copied Cells” option.
• Delete Certain Cells: To get rid of certain cells, all you need to do is select them and use the Ctrl + X command to cut them and then delete the data.

These are all small but necessary commands that you may need when dealing with duplicate rows within your workbook.

Frequent questions

Do I need to use formulas and functions to remove duplicate rows in Excel?

Yes, you will need to use the COUNTIF function for this task. However, highlighting and deleting inpidual cells does not require a formula or function.

Should I delete duplicate rows once I find them in Excel?

That depends on the purpose of your workbook. If it is to maintain inventory, duplicate rows can ruin the integrity and accuracy of the document. In such cases, it is better to combine your data and delete the leftover rows.

What if my book has a lot of data?

You can easily select all the data in your spreadsheet, regardless of how much there is, using the CTRL+A command. Once selected, you can continue with one of the methods to highlight and delete inpidual rows above.

Clean up your data by highlighting and removing duplicates in Excel

Highlighting and removing duplicates is a reasonably simple process for Excel users. You must use the conditional formatting option for inpidual cells and the COUNTIF or COUNTIF formula after entering the “new rule” option. Once all duplicates and triplicates are highlighted, you can remove them using the “delete sheet rows” option in the top ribbon of Excel or the “CTRL + -” command. Knowing how to highlight and remove duplicates is beneficial for multiple real-world scenarios, including inventory checks and database error management.

Did you find it easy to highlight duplicate rows in Excel? What method did you use? Let us know in the comments p below.