# How can I detect if a range of cells contains only identical values or not in Excel?

I have a range of cells and I want a formula that says if all these contains the same values or not.

Usually for two cells I would use if but I want a solution that would work for ranges. I don't want to have 20 IFs one under another.

I'm comparing string values.

-

If you are dealing with a range of known size then you could use `CountIf` to get the number of times a value appears in the range.

-

If the values are just numbers, you can use the STDEV function - if they are identical, STDEV will be exactly zero.

-

You can use the Exact Function (Text Category, but will work with numbers)

Sort each range the same way. Exact will compare cell to cell and return true if it matches exactly and false if it doesn't. Autofill makes it quick and easy to do to two ranges.

-
This doesn't answer the question as I understand it. OP has a single range and wants to know if all the cells in the range are the same. He is not comparing 2 different ranges. – Shevek Mar 25 '10 at 9:02
I didn't understand it that way, but my answer is still correct. He can use the exact function to compare one cell to all the others and discover if any don't match. Or, he can copy the range and use Exact to compare them both. – Jordan 1GT Mar 26 '10 at 2:06