"Excel Duplicate Values: How to Find and Remove It?"

An Excel duplicate values is one where all values in the row are an exact match of all the values in another row. Duplicate values are determined by the value displayed in the cell and not necessarily the value stored in the cell.

In Microsoft Office Excel 2007, you have two ways to remove duplicate values:
1) Use the Remove Duplicates command in the Data Tools group on the Data tab.
2) Use the Conditional Formatting command in the Style group on the Home tab.

Using conditional formatting, you can easily highlight duplicate values in your spreadsheet. This will make it easier to identify the duplicates so that you can remove them.

To highlight the Excel duplicate values

• Select one or more cells in a range, or table that is to be checked for excel duplicate values.
• On the Home tab, in the Styles group, click Conditional Formatting icon.
• Select Highlight Cells Rules and click Duplicate Values.

• From the Duplicate Values dialog box displayed, choose the formatting you wish to use from the dropdown. The default is a "Light Red Fill with Dark Red Text".
• Click OK.
• The background color of all values that appear more than once in the selected ranges will show the selected formatting.

What happen to those duplicate values? Remove it?

But, removing duplicates from a spreadsheet manually can be a timely task, fortunately Excel can do this for you easily. Just follow the steps here.

To automatically remove duplicates

• Select the range of data in your spreadsheet.
• On the Data tab, in the Data Tools group, click Remove Duplicates icon.

• From the Remove Duplicates dialog box displayed, check the column(s) you want searched for duplicates.

­ To quickly select all columns, click Select All.
­ To quickly clear all columns, click Unselect All.
• You should check all columns that need to be a match to be considered a duplicate to prevent the accidental removal of data you wish to keep.
• Click OK.
• A message will tell you how many duplicate values were found and removed and how many unique values remain in your data.
• If the result is not what you desired, hit Ctrl+Z to undo the removal of the duplicates and try again.

Related Topics:

Customizing and Adding Commands to the Quick Access Toolbar in Excel 2007
How to Install and Uninstall Add-ins in Excel 2007?
How to View Multiple Worksheets at the Same Time in Excel 2007?
How To Protect Excel Worksheet or Workbook with the Password?
Using the Watch Window Feature in Microsoft Excel 2007!

Can't find what you're looking for? Try Google Search!

Custom Search

Back to Top

You're viewing the Excel duplicate values info page, click here to go back to the Home Page

Add To Your Social Bookmarks: Blink Del.icio.us Digg
Furl Google Simpy Spurl Technorati Y! MyWeb