IQ Accounting Solutions

If you have a list in Excel, you may need to remove duplicate entries from that list. You could sort your list and manually delete the duplicates, but Excel has an easier way. On the Data tab of the ribbon, you’ll find a Remove Duplicates button in the Data Tools section. The word “remove” tells you that you need to use this tool with caution. And if you don’t like the results, remember that Ctrl+Z is the keyboard shortcut for Undo.

If you have a single column list, then it’s pretty simple:

• Select the range of cells from which you want to remove duplicates, or just select the whole column if there is nothing else below your list.
• Go to the Data tab on the ribbon.
• Click the Remove Duplicates button.
• If you have a heading at the top of your list, make sure the My data has headers box is checked in the Remove Duplicates window.
• Click OK.

Excel will then tell you how many duplicate values it found/removed and how many unique values remain. It will also move the remaining values up to fill in the gaps left by removing the duplicates. That’s a nice finishing touch since you won’t have to manually remove the blank rows, but it is also the reason that you have to be very careful with this tool when your list has multiple columns.
If you have multiple columns of data but only want to check one of them for duplicates, you must select all columns in your list before removing duplicates. If you don’t, then after the duplicates are removed, the remaining values will no longer be on the same line as their corresponding data in the other columns.

Also, when you select multiple columns of data, you can choose how many of those columns should be checked for duplicates by simply checking or clearing the box next to each column name in the Remove Duplicates Windows. Excel will then look for duplicates based on the combined content of the chosen columns, so checking multiple columns will result in fewer lines being removed, not more.

Here is an example of how you will get different results depending on the columns you select:

If you highlight columns A and B, then click the Remove Duplicates button, and select only the Product column in the Remove Duplicates window, lines 3, 5, 7, 9, 10, and 11 will all be removed (including the Color data on those lines). You’ll be left with only one entry each for Widget A, Widget B, Widget C, and Widget D.

But, if you selected both the Product and Color columns in the Check for Duplicates window, only rows 10 and 11 would be removed. That’s because Excel will only consider it a duplicate if the combined contents of both columns create a duplicate.