Conditional Formatting to Compare Worksheets

You can use conditional formatting in Excel to show where there have been changes between two versions of a worksheet.

In this example, we see we have one worksheet where not all of the data in the first column for States has been filled in for all of entries in column B for Cities:

. . . the second worksheet has all of the cells in the column for States filled in:

We can run conditional formatting on Sheet2 to show exactly which cells have new entries. First select the array that you want to apply the conditional formatting to. On the Home tab go to the Styles section and select 'Conditional Formatting' and enter a new Rule. Choose the Rule Type 'Use a formula to determine which cells to format'. In the description enter a condition, but without a formula operator:

. . . then select the format to be applied. So in this case I have entered the condition based on the first cell in the array, but Excel will apply it to the entire array.

After the Rule is applied we get this result: