In Microsoft Excel, you can compare cells using a simple formula. This allows you to determine whether the data entered is true or false.
Compare cells in Excel using a list of players as an example
In our example, we want to determine which player has found the correct solution. To do this, compare the solution you are looking for with the participants’ answers. The formula provided by Microsoft Excel for such calculations is called IDENTICAL. It allows you to find duplicate entries.
- In the example (see Figure 1 below), the names of the players in rows 2 to 5 have been entered in column A in an Excel spreadsheet. The players’ answers are listed in column B.
- The correct solution is in cell B7. A formula is now used to determine which player found the right solution.
- To compare the solution word of the player with the correct solution, use the Excel function EXACT. The syntax of the formula is: “EXACT(Text1;Text2)”.
- This function compares the value of the cell specified as “Text1” with the value of the cell to be checked in “Text2”. Enter the formula in rows 2 to 5 of column C (see Figure 2).
- IDENTICAL: the formula is case sensitive. Normally, Excel makes no distinction when checking cells. The following contents are the same for Excel: “Orange” and “orange”.
Compare cells – step by step
To compare the player’s solution with the result cell, let Microsoft Excel do the work. For large tables, the spreadsheet will find the correct answers in no time.
- Click in cell C2. Now enter the EQUAL function as follows: =EQUAL(B2,B7).
- In the first argument of the formula (Text1), note the first solution from cell B2. This is Katja’s solution.
- As the second argument (Text2), enter the cell with which you want to compare Katja’s solution. In the example, this is cell B7.
- Press Enter to confirm the formula. The result in cell C2 is TRUE because Katja’s solution is correct.
- To check the results of all players, insert this formula into column C for the other players as well. Adjust the formulas to the respective cells. For Iris, Text1=B3, for Mike B4 and for Jessica B5.
- In column C, you can see the correct answers. Both Katja and Jessica found the right solution.
Compare the correct solution with a list
The IDENTICAL function also helps to make comparisons from a long list of cells. This is useful, for example, for password comparisons or usernames where differences between “user” and “user” play a role. We stick to our example, but now use it as an array formula.
- In cell C7, enter the OR function: =OR(). It returns TRUE if the argument in parentheses is TRUE, otherwise FALSE.
- In the edit line, click between the parentheses of the function. This displays the required parameters. The formula requires at least one argument: “TruthValue1”.
- Enter the function IDENTICAL as “TruthValue1”: =OR(IDENTICAL()). Now click between the parentheses of the IDENTICAL function to see the required parameters.
- In the first argument of the EQUAL function, enter the comparison value that contains the correct solution, in this case B7. End the entry with a semicolon: =OR(EQUAL(B7,))
- As the second argument, select the range of cells you want to compare with cell B7, here B2 to B5: =OR(IDENTICAL(B7;B2:B5)).
- Finish the formula as an array formula. To make Excel recognize the range, press the keyboard shortcut: CTRL+SHIFT+ENTER. If you do not hold down the SHIFT key, Excel will return the result “VALUE!”.
- In cell C7, “TRUE” is now displayed because at least one match with cell B7 exists in the cell range B2 to B5. In our example, at least one player has found the correct solution using Microsoft Excel.
