In any case, when you by mistake copy unwanted rows on the worksheet, you will have to find and remove that duplicate rows. You may have to face duplicate rows when you are making a composite sheet and you will have to delete that duplicate data. Though this is the most tiring, time-consuming, and boring task, however, you still have to manage it.
In this post, you will get to know how to delete duplicate rows in Excel by using multiple methods. Let’s get started with our methods:
How to Remove Duplicate Rows in Excel using the Remove Duplicates Function?
Let’s suppose you have a dataset having duplicate rows, you will have to follow the steps given below to remove the duplicate rows from a sheet.
- Choose the range that has rows you want to remove. If all the rows needed to be removed from a worksheet, press and hold CTRL + A key and select all the sheets.
- Click Remove Duplicates given on the Data tab.
- From the Remove Duplicate dialog box, check all the boxes given below the Columns list box. Also, make sure you have checked the My data has a header option if the data has headers.
- Now, press OK and you will see a message showing that all the duplicate data is removed excluding the first duplicate row.
Note: Using this function, you can remove rows with identical values. Consider checking the columns that needed to be removed with duplicate values in the Remove Duplicates dialog box.
How to Delete Duplicate Rows in Excel using Replace in Excel?
Here is another process you can use to delete duplicate rows in Excel. When you are dealing with smaller datasets, you always have to identify all the duplicated rows. For this, you can use the built-in tool called Replace.
- Start the procedure with a spreadsheet opening.
- Now, select a cell that you need to find and replace. Copy it and press CTRL + C by clicking on the cell. For Windows, you need to press CTRL + C and for Mac, you can use Command + C.
- After copying the word you want to search, press CTRL + H for Windows and it will replace the function. Now, paste the copied word into the Find What field. Press CTRL + V for windows.
- Press Options and select the Match Entire Cell Contents checkbox. It might be possible that sometimes the word you are looking for is placed in some other cells.
- Add a value in the Replace With box. For instance, here we have used number 1 and once you have added the value, press the “Replace All” option.
You will see that all the matched “duplicate” values are changed to “1.” Now, you can easily get to know which rows are duplicated.
You can get back a copy of the duplicates by pasting the original text back in the first row.
Open the document, press and hold CTRL on Windows or Command on Mac when you click on the number of all the duplicate rows.
Once all the rows are selected that needed to be deleted, right-click on grayed-out numbers and choose the Delete option. You have to press the Delete key because it will delete the rows instead of the content.
To Sum Up
You can identify and remove the duplicate rows in Excel by using the above-mentioned methods. Each method is unique and effective. Try any of the methods and you will get the desired outcomes.
