MS Excel 2007/2010/2013 Tips: Working with duplicate values

Printer-friendly versionPrinter-friendly versionSend by emailSend by emailPDF versionPDF version

When you export data from other programs to Excel, either of xls, xlsx or csv format, in some cases, you will encounter duplicate values in cells. Or simply put, values in cells are repeated because of how the originating software manages and stores data, or because of how you input your data in the system.

Managing duplicates in Excel

With MS Excel, there are various ways to deal with duplicate entries in cells. Look at the sample table below:

Sample table showing duplicates

Can you easily see those duplicates? Suppose you are give a task to summarize the data, how would you easily find duplicates especially if the table contains thousands of cells? With MS Excel, it is now easy to work with duplicates.

Easily Delete or Remove Duplicates

Using the sample table shown above, I will show you how to remove duplicates easily. Steps are shown below:

Step 1: Open your Excel file and locate the table of data that you want to work with. Highlight the entire cell range containing the data.

Step 2: With the range of cells selected or highlighted, click on the Data menu.

Step 3: Locate the group called Data Tools and then click on the button that says "Remove Duplicates".

The command showing remove duplicates

Step 4: In the dialog box that appears, click all columns listed, then click OK button.

The dialog box to remove duplicates

Step 5: Then you shall see that Excel will remove duplicates in a split of a second.

Duplicates removed. Showing report dialog box stating the number of items removed

 

Easily Highlight Duplicates

If you do not need to delete duplicates but instead just want a way to mark them, this is how Conditional Formatting comes to play. With Conditional Formatting, you can format certain cells or cell ranges based on certain conditions. In this sample, the condition is "similar contents" or duplicates. Thus, we just need to tell Excel that "in the selected table, format the cells differently if they are duplicates.

Step 1: Highlight or select the range of cells containing your data.

Step 2: Click on the Home tab. In far right of the toolbars, locate Conditional Formatting under the Styles group.

Conditional formatting button in Styles group

Step 3: Then click on Conditional Formatting, then click on Highlight Cell Rules, then click on Duplicate Values.

The format duplicates command

Step 4: In the dialog box that opens, you can pick the formatting to apply to the selected cell range, then click OK to complete the process.

With conditional formatting applied

 

MS Office Software: