How to find repeated values in a sheet? – MS Excel 2007 Tips

For those of you who wish to find repeated values in a sheet full of numbers, Using CTRL+f would yield the desired value one at a time and if the sheet is full of data then you are up against some task! To find values simultaneously there is another simple way in Excel 2007- Conditional Formatting.

Suppose you have the following set of numbers in a sheet and you need to find the number 10 in the entire array (the set of data provided)

Conditional Formatting MS Excel 2007
Conditional Formatting MS Excel 2007

Type Alt+h+l and this opens the box for conditional formatting which appears as below.

Conditional Formatting - MS Excel 2007
Conditional Formatting - MS Excel 2007

In the highlight cell rules, click the first option which says Highlight Cell Rules.

Go to the last option – Duplicate Values and click on it. You would find this box appear on your screen.

In the left field, there is a dropdown list which has two options – Duplicate and Unique. To find all the duplicate values in an array, select Duplicate.

Conditional Formatting Duplicate Values - MS Excel 2007
Conditional Formatting Duplicate Values - MS Excel 2007

In the right hand side field, select any of the option in the dropdown list such as “Light Red fill with dark red text

Click OK and you would find that repeated values are highlighted in the sheet.

Conditional Formatting Result
Conditional Formatting Result

The next post shall try to throw some light on finding values in a range and using conditions to find values. Hope this post was of use..

  • http://sajiv.itaumens.com SAJIV

    THANKS A LOT MAN, I WAS FINDING THIS ONLY

  • Ka_eb

    Very Good Thanks