Google Sheets and Excel allow one to quickly compare various cells and check for similarities. This feature is helpful while making conclusions and analyzing a given dataset. This article will discuss ways and methods of comparing two lists in Google Sheets and Excel
To compare two lists in Google Sheets
Table of Contents
a) Using the ARRAYFORMULA function
Steps:
1. Visit the Google account and log in using your email detail (That is, https://www.google.com/account).
2. From the Google Apps, click on the Sheets icon and select the existing Sheet.
3. Enter the dataset you want to compare in two columns. Locate another empty cell that will hold the results of the comparison.
4. Type this formula on the selected cell, =ARRAYFORMULA (
5. Next, add the COUNTIF function. That is, =ARRAYFORMULA (COUNTIF(
6. Select the first column that contains the first list. That is =ARRAYFORMULA (COUNTIF(A1:A4
7. Next, select the next column. That is =ARRAYFORMULA (COUNTIF(A1:A4, B1:B4))
8. Finally, hit the Enter Button
NOTE: If the formula returns 1, that means the dataset match. If otherwise, (0) indicates the dataset does not match.
b) Using the Conditional formatting tool
Steps:
1. Visit the Google account and log in using your email detail (That is, https://www.google.com/account).
2. From the Google Apps, click on the Sheets icon and select the existing Sheet.
3. Enter the dataset you want to compare in three columns or three cells of the same column
4. Highlight the whole dataset and click on the format tab in the menu.
5. From the menu, choose the Conditional formatting button. A conditional formatting pane will open on the right side of the screen.
6. Choose the Custom formula if the button in the Format cell If section. In the value or formula section, type this formula =ARRAYFORMULA (COUNTIF(A1:A4, B1:B4))
7. In the Apply range section, choose the three cells you want to compare.
8. In the Formatting style section, choose the fill colour, font colour, and font format you want.
9. Finally, hit the Done button.
To compare two lists in Excel
a) Using the COUNTIF function
Steps:
1. Enter the data to be compared, or open the existing that you want to check the difference.
2. Ensure your data is in two columns.
3. Then, create a separate column containing the comparison’s feedback.
4. On the feedback column, click on the first blank cell and enter this formula.
=IF(COUNTIF($B:$B, $A2)=0, “No match found”)
5. The formula returns zero if no match is found. Therefore, it can be used to check the difference between the data in the column in question.
Drag the formula to other cells and check the difference between the columns.
b) Using Conditional Formatting
Steps:
1. Enter the data to be compared, or open the existing that you want to check the difference.
2. Highlight the two-column that contain your data.
3. On the Home tab, click the Conditional Formatting button.
4. From the drop-down menu, select the New Rule button.
5. On the Edit formatting rule dialogue box, click the Use a formula to determine which cells to format. Type the comparison formula on the formula bar.
6. Finally, format the colour that will differentiate between the cells that match the formula.
