Step 1: Select the columns to be compared and at the Home tab in the Styles group click the Conditional Formatting dropdown menu. highlighting the data points that are present in Column 1 but not in column 2 and the data points that are in column 2 and not in column 1 follow the steps below: Step 3: See in the picture below, the data points in Column 1 that are also present in Column 2 get formatted in the red background as we have described in the Duplicate Values dialog box.įor highlighting the differences i.e. Here, in the first drop-down menu make sure you have selected Duplicate and in the second drop-down menu select the way you want to format the cells containing the duplicates. Step 2: Duplicate Values dialog box will pop out. Here, click on Highlight cells Rules>Duplicate Values. Step 1: Select the columns that you want to compare and at the Home tab, in the Styles group, we have Conditional Formatting drop-down menu. Now, we compare columns to find if a data point in column 1 is present anywhere in column 2. Till now we have compared the columns row by row. 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.
0 kommentar(er)
