Sunday, December 16, 2012

How to remove duplicate records in Microsoft Excel

When working on large lists of data records in a spreadsheet, especially entering unrelated words, numbers or characters, these data become duplicated or even entered more than twice. You don't have to worry about duplications because Microsoft Excel has a powerful tool to remove duplicates in a list of records. Don't waste your time to check for duplicates as you enter the values and data. Just type as fast as you can and don't even worry about formatting the table or columns at the beginning, because that should be done after duplicates have been eliminated and final formatting and tabulating of unique records will be done.

I wrote this article because I want people to efficiently and quickly create a table with unique records. I learned from my mistake of wasting too much time of checking and editing everytime I enter a data record. This should be helpful especially when dealing with numerical and alphanumeric characters. I have made the procedure simple so that it is easy to follow.

How to remove duplicate records in Microsoft Excel:
1. Enter all data. Type data records as fast as you can.
2. Click the column header, ie. A, B, C, D, etc, to select all data. Do not put it on the first record.
3. Click Data and either Sort Ascending or Descending.
4. Now you can see duplicates and repeated records.
5. Select the column or cells that you want to remove for duplicates. You only want to display unique records.
6. Click Data on the menu bar.
7. Point to Filter.
8. Click Advanced Filter.
9. On Action, select Filter the list, in-place.
10. On List Range, this will show the cells or column that you highlighted.
11. Put a check mark on Unique Records only.
12. Press OK.
13. Check for duplicates again. There should be no more duplicates at this time.
14. Adjust the Table formatting as necessary.
15. For long lists, you can add another column and transfer the data records. 

1 comment: