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.
3 Answers
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.
2
