Compare columns in Google Sheets | 2 min easy guide

Compare columns in Google Sheets | 2 min easy guide
Reading Time: 4 minutes

In this article, we would have a look at how to compare columns in Google Sheets to look for matching and non-matching values.

Compare columns in Google Sheets
Compare Columns In Google Sheets

Sometimes you would want to compare two columns and spot the differences between the two. For example, while comparing shopping lists for two individuals, you might want to look at the common item or unique items. Another scenario might be comparing responses of a form to compare to commonly used responses.

Compare columns in Google Sheets per row

Let’s say we have a sheet that contains a shopping list of two individuals as columns.

Sample table
Figure 1: Sample Table

We want to check what items are common in order. It is pretty simple as we have to check the equality for the adjacent cells. For example, compare A2 to B2, A3 to B3 and so on. To do so we can just use the formula “=A2=B2” (to compare A2 and B2). It would return True if the values are equal and False otherwise. Applying this to every row we get the following result. 

Displaying equality for each row
Figure 2: Displaying equality for each row

Displaying message for equality 

Displaying true and false works, but it is a very bad design in terms of readability and user friendliness. What we can do to improve it is display messages “Equal” and “Not Equal” for true and false values respectively. To implement the same, we will use the conditional formula IF. 

For the first cell, the formula would be “=IF(A2=B2, “Equal”, “Not Equal”)”. This would print “Equal” if A2=B2, otherwise it would print false. Do this for every row and you’ll the following final result. 

You can read more about logical functions here

Displaying messages based on equality
Figure 3: Displaying Messages based on equality

Highlight cells based on Equality

Rather than printing “Equal” or “Not equal”, a better way would be to highlight cells based on equality. This can be achieved using Conditional Formatting. Conditional formatting allows you to format cells based on any condition you want. Here were would compare two cells and make the cell color light green to indicate that matches.

To do so follow the given steps

  • Select the second column 
  • Go to Format>Conditional Formatting
Conditioning formatting
Figure 4: Conditional Formatting
  • A sidebar like such would open up.
  • In the “Format cells if”, select the Custom Formula.
  • Another field would appear asking for the formula.
  • Enter “=$A2=$B2” and then select the color in the next field.
  • Click Done.
Coloring cells using conditional formatting
Figure 5: Coloring cells using Conditional Formatting

You’ll notice that the adjacent cells with equal values would get colored in green. 

If, you want to highlight the unequal values then you can use the formula =$A2<>$B2. Here <> is the inequality operator.

Compare columns in Google Sheets irrespective of the order

So far, we have been comparing cells based on the equality of the adjacent cells, but in real life the two shopping lists can be of different order, i.e. it is not necessary that equal items would be adjacent to each other. What do we do in this case. Here we can write our custom formula bit differently. We would search each cells value in the entire other column to see if they exist there. To do so we would make use of the COUNTIF function. You can read more about COUNTIF function here.

This time in the Custom formula section in Conditional Formatting side bar, enter the formula

“=COUNTIF($A$2:$A$11,B2)>0” 

Checking for equality, irrespective of the order
Figure 6: Checking for equality, irrespective of the order

As you can see, some entries which aren’t adjacent but appear in the first column have been highlighted. 

Note: If you want to highlight the entries which are not present in the first columns, you’ll have to enter the formula “=COUNTIF($A$2:$A$11,B2)=0”

Conclusion

We saw how to compare columns in Google Sheets to check for matching or different entries. We also checked for entries that are not adjacent and performed a search for them as well. We made use of different Google Sheets formulas which you can go through to get a better understanding of the entire topic. 

See Also

You can check out other equally good articles on Google Sheets here.

Delete Empty rows in Google Sheets: In this article, we would see how to delete empty rows in Google Sheets using two different ways.

How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.

One response to “Compare columns in Google Sheets | 2 min easy guide”

Leave a Reply