Don’t Be Duped: Top Tips for Cleaning Up Duplicate Data in Excel

The business world may be abuzz about Windows 8, BYOD, tablets and more, but many corporate workers are focused on one thing only – the work that needs to be done now.

And if that work involves handling large amounts of data, you are no stranger to the dupe problem. On the one hand, having large amounts of data can be helpful when you are doing analyses, as you are more likely to get an accurate answer. However, if your data contains duplicates, your numbers will be inaccurate. Not good.

Here are some tips for identifying and removing duplicates. Your day just got better.

Tips written by Vitalyst staff.

Using Conditional Formatting to Identify Duplicate Values in a Column, Excel 2010

You may not necessarily want to remove the duplicates; you just want to find and be made aware of which values are duplicated. This can be achieved using conditional formatting, which is exactly what its name indicates: formatting that is applied to a cell that meets a certain condition. Click here for full tip.

For Excel 2007 instructions, click here.

Getting a Unique List of Items, Excel 2010

If you have a set of data that has repeating values, and you would like to get a list of the unique values but keep your original data intact, you will want to use the Advanced Filter feature.

Getting a Unique List of Items in Excel 2010The first problem you might run into is what constitutes unique data. For instance, if your data has a Quantity column, each row of the data might be unique because each entry could have a different quantity. Excel looks at all columns selected to determine a unique record. If all the columns match, the record is considered a duplicate. If one of the cells in the row or selection is different, the record is considered unique. Click here for full tip.

To watch a video on this tip, click here.

For Excel 2007, see this tip.

Understanding the Remove Duplicates Tool, Excel 2010

Merging companies, yearly sales summaries, and poor data retrieval can all cause duplicated data. Fortunately, newer versions of Excel provide a quick tool found on the Data tab in the Data Tools group. The Remove Duplicates button will look at a range and take the first instance of criteria, then delete every other instance of it. There are three main scenarios when working with duplicated records. Click here to read full tip.

For Excel 2007, click here.

Share on facebook
Share on twitter
Share on linkedin

Talk to us.

We look forward to helping you on your journey to Digital Transformation.

Skip to content