"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.

Excel 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.
  • Excel 2007 remove duplicates

  • 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!

How to Create Excel 2007 Drop Down List? - The Simple Way

How to Create the Excel 2007 Data Validation Rules?



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: add to BlinkBlink add to Del.icio.usDel.icio.us add to DiggDigg
add to FurlFurl add to GoogleGoogle add to SimpySimpy add to SpurlSpurl Bookmark at TechnoratiTechnorati add to YahooY! MyWeb


Google Search:

Custom Search

Recommended Books:

Click on the image to view the details of the book.
Excel 2007 ebook
The Ultimate Guide to
Excel 2007!


PowerPoint 2007 ebook
The Ultimate Guide to
PowerPoint 2007




XML RSS
What is this?
Add to My Yahoo!
Add to My MSN
Add to Google


Enjoy This Site?
Then why not use the button below, to add us to your favorite bookmarking service?

Copyright © www.msoffice-tutorial-training.com. All Rights Reserved.