Use Conditional Formatting To Find Duplicate Data In Excel 2007

Fortunately, conditional formatting in the 2007 version of MS Excel has widened its scope to cater to more conditions and problem scenarios. One of the conditions that it aids in looking for is duplicate values.

You can easily highlight or apply a unique format to the cells that contain duplicate or repeated values. This ability of MS Excel 2007 becomes handy when you have to review a lot of data in a worksheet. In such a situation, it is never easy to hunt for duplicate values. You can manually go through 20 cells for catching hold of repeated values, but the same approach is not practical when it comes to looking for the same values in 200 cells.

Conditional Formatting To Find Duplicate Data

MS Excel 2007 offers two ways of finding the repeated data namely, filters and conditional formatting. In this article, we shall restrict ourselves to the conditional formatting method. There are two ways of using the conditional formatting feature to spot cells with duplicate content. So, let us check them out now!

Using The Highlight Cells Rules Option

This option is present in the drop-down menu of the Conditional Formatting button in the Home tab. Choosing this option is actually a fast and easy way of spotting the duplicate values in an Excel 2007 spreadsheet. Here’s how to use this option.

1. Open the desired Excel spreadsheet.

2. Select the required cells to search for duplicate values or entries.

3. Ensure that the Home tab is selected on the Ribbon.

Conditional Formatting To Find Duplicate Data in Excel 2007

4. Click the Conditional Formatting drop-down button in the Styles group. A drop-down menu is displayed

5. Select Highlight Cells Rules –> Duplicate Values. This will display the Duplicate Values dialog box.

conditional formatting for duplicate data in excel 2007

6. Notice that Duplicate is selected by default in the combo box on the left side.

conditional formatting for duplicate data in excel 2007

7. Select the appropriate formatting option in the combo box on the right side. If you want to apply your own format, you can even choose Custom Format to open the Format Cells dialog box.

8. Click OK. You will now spot the cells with duplicate values so that you can either delete or change them. In some cases, you might have to even leave the values as they are.

conditional formatting for duplicate data in excel 2007

Using The Conditional Formatting Rules Manager Dialog Box

If you are a real geek, you will surely love to use this dialog box for spotting the duplicate content. Here’s how to use this dialog box for highlighting the cells having repeated values.

1. Open the Excel spreadsheet.

2. Select the desired cells that tend to contain duplicate values or entries.

3. Select Conditional Formatting –> Manage Rules in the Styles group under the Home tab. The Conditional Formatting Rules Manager dialog box becomes visible.

conditional formatting for duplicate data in excel 2007

4. Click the New Rule button. The New Formatting Rule dialog box pops up.

conditional formatting for duplicate data in excel 2007

5. Select the Format only unique or duplicate values option from the Select a Rule Type list.

conditional formatting for duplicate data in excel 2007

Also Read

Import Text Into Excel 2007 
Copy Website Tables Into Excel 2007 Spreadsheets 
Make Excel 2007 Read Spreadsheets To You 
How To Change The Gridlines Color In Excel 2007 
How To Change The Default Font In Excel 2007 

6. Ensure that duplicate is selected in Format all combo box.

conditional formatting for duplicate data in excel 2007

7. Click the Format button. The Format Cells dialog box pops up.

8. Select the different options from the various tabs.

9. Click OK twice.

10. Click the Apply button. You will now see that the cells with duplicate content are highlighted with the specified format.

Do share with us which method you liked the most via comments!



Dipaligandhi