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.
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.
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.
6. Notice that Duplicate is selected by default in the combo box on the left side.
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.
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.
4. Click the New Rule button. The New Formatting Rule dialog box pops up.
5. Select the Format only unique or duplicate values option from the Select a Rule Type list.
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.
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!