compare two columns in excel for matches

Highlight for Matches or Differences Highlight the Matches Observe the picture below, where the rows which have an exact match for both the columns have been formatted as described in the New Formatting Rule box. If the formula results True, the cells will be formatted as you describe. So, we will type the formula to find the exact match. In “ Edit the Rule Description” box, type the formula to identify the cells that have to be formatted. In the “ Select a Rule Type:” box click on “ Use a formula to determine which cells to format”. Step 2: A New Formatting Rule dialog box pop out. Step 1: First select the columns to be compared and at Home tab under Styles group, we have Conditional Formatting drop-down menu. Now, we will compare two columns for an exact row match using an inbuilt function. This formula returns “ Match” if an exact match is found and “ Different” if the row has different data in both the column. =IF(A2=B2, “Match”, “Different”) // press enter and drag it down till the entries of compared columns end up. The formula to compare columns for an exact row match using IF(): We have one more variation of this, we can find the exact match using IF() function this allows us to get an organized result. This formula returns TRUE if a row has exact same data in both the columns and FALSE if a row has different data in both the columns.

We will compare these columns by typing a simple formula “=A2=B2” in a cell just beside the first row, of both the compared columns and drag it down till the entries in columns doesn’t end up. Look at the picture below, we have column 1 and column 2 to be compared. Steps to Compare Two Columns in Excel 2016: The events that we will discuss here are given below. In this section, we will discuss some inbuilt functions, and formulas to compare two columns to discover the matches or the differences. There are several ways to compare two columns in an excel sheet. Differences are the data points that are in one column but not present in other column being compared. Matches are the data points that are present in both the compared columns. There can be two reasons to compare two columns in excel either we have to find the matches or to find the differences.

Compare two columns in excel for matches