compare two columns in excel conditional formatting

Select New Rule and type ‘=A1Sheet2!A1’ into the dialogue box.Click the Home menu, then select Conditional Formatting.Then select the first cell in the sheet (e.g., cell A1) then press Ctrl + Shift + End simultaneously.Open the sheet in which you want Excel to highlight the duplicates.If you want to compare data from two different sheets within the same workbook, you can use conditional formatting to do the comparisons, enabling you to compare data using a range of criteria: Change them to suit your own workbook as you see fit. The formula assumes you are comparing Sheet1 against Sheet2 both beginning at cell A1. The differences should then be highlighted as Sheet1 vs Sheet2:Difference1 etc.Drag the formula down the page for as many cells as the columns you are comparing contain.Open a new sheet and highlight the same cell the two columns you’re comparing start on.For this example, let’s say we have a column on Sheet 1 (starting at A1) and another column on Sheet 2 (also starting at A1) that we want to compare. So we have checked two columns for duplicates but what if you want to find differences? That is almost as straightforward. Compare two columns for differences in Excel

Change them to reflect the columns you want to compare in your own workbook.

Note that this formula assumes you are comparing Sheet1 against Sheet2 both beginning at cell A1. The differences should then be highlighted as Sheet1 vs Sheet2:Difference1 in the cell containing the differences. This simple process of checking for duplicates will make you more efficient and productive using Excel. The result of this process will be that each duplicate cell will be highlighted in both columns you are comparing.

Drag the formula down the column for each of the cells you want to compare in the two columns in question.

Highlight the same top cell (i.e., A1) in the column in Sheet1.

Compare two columns in excel conditional formatting