compare two columns in excel to find differences

COMPARE TWO COLUMNS IN EXCEL TO FIND DIFFERENCES HOW TO

To select the format we want to use to the matching cells, click the Format button.įinally, all the cells where names are equal in each row will be highlighted. Enter the formula: =$E2=$F2 into the formula field.ħ. Locate the ‘New Formatting Rule’ dialog box and click on the ‘Use a formula to determine which cells to format.’Ħ. Click on the “ new rule” from the drop-down list.ĥ. Click on the ‘ Conditional Formatting’ icon.Ĥ. Select the range which contains the data set.ģ. The given cells will be highlighted instead of creating an additional column.ġ.

COMPARE TWO COLUMNS IN EXCEL TO FIND DIFFERENCES HOW TO

How to highlight the same rows in place? The best space-saving solution to do that using conditional formatting. =IF(EXACT(A2,B2),'equal','different') Example: Highlight Matching Data using conditional formatting Tip: This expression is case sensitive! “BERMUDA” and “Bermuda” will be identified as the same and will be marked as DIFFERENT items. Good to know: If we want to get case sensitive results, we have to combine the IF and the EXACT functions. =IF(A2=B2,'Equal','Not Equal') Example: Case Sensitive cell comparison using the EXACT function The result is “Not equal” when the names are different. The IF formula will return “Equal” when the names are the same. How to get an easier understandable result? Chose “equal” as a second parameter, and “Different” as the third parameter of the IF formula.

Example: Apply IF formula to Compare Cells in the same row If the two value is different, we’ll get the result as FALSE.

If cell E3 is equal to F2, Excel will write a TRUE string into the column G. To quickly get the result, use a simple equal sign. Now, we find it is the same in the second column, or it’s different. We will compare the names row by row without using built-in Excel functions. Example: Compare cells in the same row – Use the Equal Sign In this section, we’ll show you how to compare and identify which rows contain the same value and which ones are different. Let us begin to compare two columns in Excel with some detailed tutorials. In this tutorial, we will discover several techniques to compare two columns in Excel and highlight matches and differences between them.

Furthermore, we’ll use complex VBA functions to check the similarity between records. The chosen solution depends on user requirements and the structure of the initial data.įor example, we can apply simple conditional formatting to highlight all the matching data points in two columns. Learn how to compare two columns in Excel to highlight matches and differences using functions and conditional formatting! It can be a challenge to use the most effective way to reach the result.

Compare two columns in excel to find differences