Back

Remove Duplicates in Excel [and other fun stuff]

Depending on how much you use Excel, it may benefit you to go through some of your frequently used files to remove duplicate in the data you consistently sift through.

This tip explains how to remove duplicates or identify them without removing. [Use for Excel 2007 or 2010.]

Removing Duplicates

Warning: Before doing a full removal of duplicates, be sure to have a backup copy of your file, because it is a permanent move. Also, understand that only the values in the range of cells selected will be affected. All other values will remain unaltered.

1.     Select the range of cells or make sure the active cell is in the table.

2.     Under Data tab, go to Data Tools group and click on “Remove Duplicates”.

3.     Do one or more of the following:

  • Under Columns, select one or more of the columns to seek duplicates.
  • To quickly select columns, click Select All
  • To quickly clear all columns, click Unselect All.

4.  Click OK.

5.  A message will pop up indicating how many duplicates were removed, how many were unique or if no duplicate values were found.

This is a great feature if you are sending a mail merge and need to check for duplicate contacts.

Highlighting Duplicates 

If you are not ready to remove duplicate values, but want to locate them this one is for you. Excel will highlight all duplicates for you. This tip is ideal for someone looking to compare lists to see if there is overlap. (Excel 2010 and 2013)

  1. Select the range of cells that you think contains duplicates.
  2. In the Home tab, go to Conditional Formatting>Highlight Cells Rules>Duplicate Values.
  3. In the Duplicate Values box, (below) make sure the Duplicate is selected in the drop down box.
  4. Then select the format for duplicate values (how you want them to highlight).
  5. Click OK and they will all highlight.

Bonus tip: Another spring cleaning exercise is to sift through your desktop, documents and inbox folders to remove any clutter or meaningless documents. If you are nervous about fully deleting, simply mark a folder “Old” or “Archive” and toss many of your files there. That way you can go back to it in an urgent situation. But you are more than likely housing files or shortcuts you will never need again.  Believe it or not, those files are producing clutter in your mind and therefore stealing your time when you are scanning to find what you need.  Organize and label your most important document files and folders while you are at it and create appropriate shortcuts.

Client Feedback

“We are about 60% more effective, since we contracted with NENS. The new IT structure helped our office work more efficiently and NENS was able to help make our performance faster. NENS was able to diagnose issues quickly and fix them in a timely manner.”

—Kristen Hammond
Read More