Select a format to display and click OK.Compare A1 Sheet1 against the same cell in Sheet2.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
