Removing duplicates in Excel seems like a straightforward task. You select your data, hit Remove Duplicates, and you’re done. But if you look closer, you’ll find out that skipping proper preparation can lead to unintended data loss and inaccurate reports. Before you remove a single duplicate, there are a few steps necessary for you to go through to ensure the process is accurate. 

In this article, I’ll walk you through the most important practice you should follow before attempting to remove duplicates in your workbook.

1. Always Back Up Your Original Data

Before performing any action that modifies your dataset, especially something as destructive as removing rows or columns, create a full backup of that data. Knowing that you have another copy of your data, you can now proceed to the next action, but never touch the original. 

 To create a copy:

• Copy the worksheet: Right-click the sheet tab, select Move or Copy, and create a duplicate.
• Save a separate file: In your folder, where the file is located, you can copy and paste in another folder. That copied file will be your backup
• Use version control: If you’re working in a shared environment, save a version before making changes.

All these are important, so if something goes wrong, you need a clean restore point.

2. Clean Your Data Before Comparing

Raw Excel data is rarely clean, so before running Remove Duplicates, make sure your data is clean through the following: 

•  Remove Leading and Trailing Spaces

Let’s say your data looks like this:

If you look closely at the first Bill Smith, you will notice a space before the B, unlike the second one, which has no space. Yet when you try to remove duplicates, Excel can treat them as different values, keeping both of them when you intend to remove one. And if you have more duplicates with the same issues as Bill Smith, then you’ll have a problem. To fix this, you can use an Excel function called TRIM.

To TRIM:

Look for an empty cell. Adviseably, the one next to the column you want to TRIM.

Since every formula in Excel starts with an = type =TRIM, click on the first value in the column you want to TRIM

Now press Enter. You should see that the leading spaces have been removed in the new cell. But this has only fixed one value, and we want to do the same for the entire column. To apply the Trim function to the rest, click on the already Trimmed cell, place your cursor on the fill handle, and drag down through the column.

The fill handle is the small square located at the bottom-right corner of any cell you select.  

Once you drag down, you should see that the entire column has no spaces as opposed to the original column.

•  Standardize Case

Let’s say your data looked like this:

All three cells will be treated as separate values because Excel is case-sensitive.

To fix this, there are three different functions we could use, depending on how we want the letters to appear.

1.  =UPPER() — Converts all letters to uppercase
2.  =LOWER() — Converts all letters to lowercase
3. =PROPER() — Capitalizes the first letter of each word and converts the remaining letters to lowercase. 

To apply this to your data, in the top empty cell next to the column with your data,

type the function for the case you want your data to appear in, click on the first value in the column you want to change and press Enter. It is similar to the step we used while trimming earlier. The function is the only thing different.

Once your preferred case has been added, drag down using the fill handle to automatically apply the function to the rest of the data in the column.

3. Identify duplicates before removing them

Don’t just remove duplicates blindly. The best thing to do is to detect and analyze them before removing them. This is where tools like Conditional formatting and filtering come in handy.

• Conditional Formatting

Highlight the cells you want to remove duplicates from. In your Home Tab, head to the Styles group and click on Conditional Formatting.

Once you click on Conditional Formatting, a dialogue box opens up. Go to Highlight Cell rules and select Duplicate Values

You should now see that all duplicate values are highlighted. Analyse them, and when you are satisfied, you can proceed with removing duplicates.

But let’s say you have thousands of rows of data. Scrolling through them might take some time, so rather than using Conditional Formatting, you could use the Filter tool. 

To apply Filter:

• First, select any cell in your data  
• Move to the Home Tab
• In the Editing group, click on the Sort & Filter dropdown
• Select Filter

Or you could just press Ctrl + Shift + L as a shortcut

You should now see a Filter drop-down arrow added to the top right corner of your column. 

To see the Duplicates before removing them: 

• Click on the drop-down arrow
• Unselect everything
• Then select the values you want to remove duplicates from (In my case, it’s Bill Smith, so I’ll select only that)

Now you can see that only the values you want to work on are visible. The rest have been filtered out. This is where it’s safe to remove duplicates.

•  Highlight all the cells
• On the Data Tab, move to Data tools
• Select Remove Duplicates

A dialogue box will appear. Confirm the column you want to remove duplicates from and click on OK. In my case, it only shows Contact because that’s the only column I have. If you have more than one column in your sheet, it may select more columns than you don’t want. So do well to make sure only the column you want is selected.

Duplicates have now been successfully removed. To remove the filter, press Ctrl + Shift + L. Now you can see all the data you have. If you go through it carefully, you will notice that there are no more duplicates. To confirm this, you can also use Conditional Formatting to highlight Duplicate Values. If you followed the process, nothing will be highlighted. 

Conclusion

Removing duplicates in Excel feels easy. The tool is easy to navigate, and the dialog is also straightforward, which is why many people fail to understand the downside of not going through the data first. To be on the safe side, don’t just click Remove Duplicates. Learn how to prepare the data, define its rules, and verify the result. 

Excel removes rows permanently. There’s no undo for Remove Duplicates beyond closing without saving. Back up first, clean second, verify third, and only then do you go ahead with Removing Duplicates.