One very common use of Excel is: it is used as a tool to clean up data. Cleaning up data means:
- Getting raw data into a worksheet
- Manipulating data so it conforms to various requirements
In summary, data is cleaned up so it can be properly analyzed.
Data is cleaned-up from so many different angles. We shall discuss those techniques one by one. In this post, we shall confine our analysis only in removing duplicate rows.
Remove Duplicate Rows in Excel
Your data may have duplicate rows, whatever the reasons. Most of the time, you need to eliminate the duplicate rows. In old days, removing duplicate data was a manual task- although removal works could be done with advanced techniques. But with the Remove Duplicates command, removing duplication is now an easy job. Remove Duplicates command was introduced in Excel 2007.
Now, see the following image. You will find that there are some duplicate rows in the column. We shall remove the duplicate ones.
Select any cell of column A in your sample file. Then choose Data ➪ Data Tools ➪ Remove Duplicates. The Remove Duplicates dialog box will appear, see the following image.
The Remove Duplicates dialog box lists all the columns in your data range or table. Place check marks on the columns that you want to add in the duplicate search. Generally, you will want to add all the columns and it is the default. Click OK, and Excel clean-out the duplicate rows, and a message will be showed to let you know how many duplicate rows have been removed.
Excel does not give you chance to change your mind. But if you want to undo your actions you can do it by clicking the Undo Button in the Quick Access toolbar or just pressing CTRL + Z.
When duplicate rows are found, the first row is kept and subsequent duplicate rows are deleted.
Currency format is used. The cell contains 500.5 but the cell display £500.50 due to using Currency Format
Download Working File
Download the working file from the link below:
- How to Highlight Duplicate Rows in Excel (3 Ways)
- How to Compare Rows in Excel for Duplicates
- How to Find Matching Values in Two Worksheets in Excel (4 Methods)
- Find Matches or Duplicate Values in Excel (8 Ways)
- Find Duplicates in Two Columns in Excel (6 Suitable Approaches)
- Finding out the number of duplicate rows using COUNTIF formula
- Excel Formula to Find Duplicates in One Column
- Excel Find Similar Text in Two Columns
- Excel Top 10 List with Duplicates