Table of Contents
You can use the following formulas in Excel to check if all values in a specific range of cells are the same:
Formula 1: Check if All Values in One Range are the Same
=AND(EXACT(B2:E2, B2))
This particular formula checks if all cells in the range B2:E2 have the same value and returns either TRUE or FALSE.
Formula 2: Check if All Values Between Two Ranges are the Same
=AND(EXACT(B2:B5, C2:C5))
This particular formula checks if all cells in the range B2:B5 are equal to all corresponding cells in the range C2:C5 and returns either TRUE or FALSE.
The following examples show how to use each formula in practice.
Example 1: Check if All Values in One Range are the Same
Suppose we have the following dataset that shows the employee with the best performance at some company during each quarter of various years:
We can type the following formula into cell F2 to check if the employee name is the same in each cell in the range B2:E2:
=AND(EXACT(B2:E2, B2))
We can then click and drag this formula down to each remaining cell in column F:
Column F now displays TRUE or FALSE to indicate if the names in each row are all the same or not.
Example 2: Check if All Values Between Two Ranges are the Same
We can type the following formula into cell E2 to check if the employee name is the same each quarter between the two companies:
=AND(EXACT(B2:B5, C2:C5))
The following screenshot shows how to use this formula in practice:
Cell E2 returns TRUE since the names in each quarter for Company A match the names in each quarter for Company B.