How to Compare Cells in Excel (10 Easy Methods)

We will use the sample dataset below to demonstrate how to compare two cells in Excel.

sample dataset


Method 1 – Compare Two Cells Side by Side Using Equal to Sign

Steps: 

  • Add the following formula in Cell D5 (to compare B5 & C5).
=B5=C5

Compare Two Cells Side by Side Using the Equal to Sign

  • Drag down the Fill Handle (+) to copy the formula to the rest of the cells.

Pulling down the fill handle to cell D8 to get whether two names are same or not


Method 2 – Use IF Function to Compare Two Cells

Steps:

  • Insert the IF function in Cell D5 and select the arguments.
=IF(B6=C6,"Match","Not a Match")

Use IF Function to Compare Two Cells in Excel

  • Drag down the Fill Handle (+) of Cell D5 to copy the formula to the rest of the cells.

drag down the fill handle to know whether two values in the column list 1 or list 2 is same or not


Method 3 – Insert Excel EXACT Function to Compare Two Cells

Steps:

  • To compare Cell B5 and Cell C5, enter EXACT function and select required cells for arguments.     
=EXACT(B5,C5)

Insert Excel EXACT Function to Compare Two Cells in Excel

  • Drag down the Fill Handle (+) of Cell D5 to copy the formula to the rest of the column.

use of autofull to determine whether the data 1 or data 2 is same or not


Method 4 – Combine IF and EXACT Functions to Compare Two Cells in Excel

Steps:

  • To compare Cell B5 and Cell C5, enter the formula combining both functions:
=IF(EXACT(B5,C5), "Match","")

Combine IF and EXACT Functions to Compare Two Cells in Excel

  • You will get the following result.

pulling down the fill handle reveal whether there is match in between the list 1 or list 2 values

Read More: Check If Multiple Cells Are Equal in Excel


Method 5 – Highlight Matching Data to Compare Two Cells

Steps:

  • Select the dataset.

Highlight Matching Data to Compare Two Cells using the conditional formatting

  • Go to Home > Conditional Formatting from the Styles group.

activate conditional formatting with setting styles

  • Click New Rule From Conditional Formatting.

then create a new rules

  • A new dialogue box will show up. Select the rule “Use a formula to determine which cells to format”.

from the new formatting rule we select which type of formatting we want

  • Add the following formula in the description box.
=$B5=$C5

then we set which cells are tobe formatted using the range box in the new formatting rule window

  • Click the Format button, go to the Fill tab, and choose the color. Click OK.

we also set the fill color of the selected cells

All matched cells in the two columns will be highlighted. Conversely, differently named rows would not be highlighted.

after conditionally formatting, we get whether the values matched or not


Method 6 – Compare and Highlight Two Cells with Unique Data in Excel

Steps:

  • Select the dataset.

Compare and Highlight Two Cells with Unique Data in Excel

  • Go to Home > Conditional Formatting from the Styles group.

initiation of the conditional formatting from the home tab with style

  • Press the Duplicate Values option from Highlight Cell Rules.

setting up the condtional formatting rules

  • The Duplicate Values dialogue box will show up. Choose the Unique option from the drop-down.

setting unique values in the duplicate cells dialog box

  • You can also choose the color of the highlight from the drop-down by using the Custom Format option.

setting cell fill color and text color in the duplicate values

  • Enter OK.

Compare and Highlight Two Cells with Unique Data in Excel

  • All the unique values between cells are highlighted.

final output showing after the conditionally formatting the cells

Read More: Compare Text in Excel and Highlight Differences


7. Use LEFT & RIGHT Functions to Compare Two Cells Partially 

7.1. Compare Using LEFT Function

Steps:

  • To match the first 3 characters of Cell B5 and Cell C5, add formula using LEFT Function:
=LEFT(B5,3)=LEFT(C5,3)

Use LEFT & RIGHT Functions to Compare Two Cells Partially

  • Click Fill Handle (+) of Cell D5 to copy the formula to the rest of the column.

drag the fill handle to cell D6 to get the result whether cell values are matched or not


7.2. Compare Using RIGHT Function

Steps:

  • To match the last 3 characters of cell H5 and cell  I5, Insert the RIGHT function and select or type arguments. Here is the Formula:
=RIGHT(H5,3)=RIGHT(I5,3)

Use LEFT & RIGHT Functions to Compare Two Cells Partially

  • Click the Fill Handle (+) of cell D5 to copy the formula to the rest of the column.

result of the comparison output after dragging the fill handle


Method 8 – Using VLOOKUP and Find Matches in Excel

Steps:

  • To match the value of Cell C5 in Column Name 1, use the formula:
=IFERROR(VLOOKUP(C5,$B$5:$B$11,1,0),"No Match")

Compare Cells Using VLOOKUP and Find Matches in Excel

Breakdown of the Formula:

  • VLOOKUP(C5,$B$5:$B$11,1,0)

The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify.  The function will look for C5 in the range B5:B11 and return:

{John}

When the function finds C6 in range B5:B11, it will return a #N/A error because C6 is not present in the prescribed range.

  • IFERROR(VLOOKUP(C5,$B$5:$B$11,1,0), “No Match”)

The IFERROR function returns value_if_error if the expression is an error and the value of the expression itself otherwise. In our example, we have put “No Match” as an argument. As a result, when we look for C6 in the above-mentioned range, the formula returns:

{No Match}

  • After entering the formula, you will get the matched name in a 3rd column. Use the Fill Handle (+) to copy the formula for the rest of the cells.

whether the matched found or not is displayed in the column output after dragging the fill handle


Method 9 – Using VLOOKUP and Find Differences

Steps:

  • To find data in Cell C5 in Column Name 1, the formula will be:
=IF(ISERROR(VLOOKUP(C5,$B$5:$B$11,1,0)),"Not Available","Available")

Compare Cells Using VLOOKUP and Find Differences

Breakdown of the Formula:

  • VLOOKUP(C5,$B$5:$B$11,1,0)

The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify.  The function will return:

{John}

This is not the end result we want from this method. We want to know whether any value is present in a range or not. The next part of the formula is:

  • ISERROR(VLOOKUP(C5,$B$5:$B$11,1,0))

The ISERROR function checks whether a value is an error, and returns TRUE or FALSE. So, for D5, the function will find the value of  C5 in the range B5:B11 and return:

{FALSE}

The reason is, C5 is present in the mentioned range. For other cells when an error is found, it will return “TRUE”.

  • IF(ISERROR(VLOOKUP(C5,$B$5:$B$11,1,0)),”Not Available”,”Available”)

The IF function checks whether a condition is met, and returns one value if true, and another value if false. We put “Not Available” and “Available” as arguments. For D5, the function returns:

{Available}

Step 2:

  • After entering the formula you will find the differences in the Output Column.
  • Use the Fill Handle (+) to copy the formula for the rest of the cells.

whether there is any differences or not between the names are showing in the column output

Read More: Return YES If 2 Cells Match in Excel


Method 10 – Compare Two Cells with Greater Than or Less Than Criteria

Steps:

  • To compare Cell B5 and Cell C5,  we have used the following formula:
=IF(B5>C5,"Yes","No")

Compare Two Cells with Greater Than or Less Than Criteria

  • In our dataset, the date in Cell B5 is not greater than the date in Cell C5 so the output is No.

the greater value among the column data 1 and data 2 are showing in the result column


Download Excel Workbook


Excel Compare Cells: Knowledge Hub


<< Go Back To Compare in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

2 Comments
  1. In “10. Compare Two Cells with Greater Than or Less Than Criteria”, I would like to determine the actual numerical difference between the two cells and the percentage of that difference to one of the cells. Eg., if one cell is 2 and the other cell is 10 … the difference is 8, which is 80% less than 10 (10 would be the mean of my interested dataset). How to I go about writing the formula?
    Thank you.

    J

    • Hi J,
      Hope you are doing well.
      You can get the actual numerical difference between two cells by using subtraction (-) in the formula. In case of, percentage value use the formula given below.
      =IF(AND(0<D5,D5<$D$10),(D5/$D$10)*100&"% Less Than 10",(-D5/$D$10)*100&"% Greater Than 10")

      If the condition is True then the formula will return the result as follows.

      1.compare two cells

      On the other hand, if the condition is False then the formula will return the result as follows.

      2.Compare Two Cells

      Here, the AND function will return True if both the conditions are true else it will return False.
      Thanks.

      Regards,
      Arin Islam

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo