This is my first post on Excel and is an aide-mémoire as I know this will come in useful to a future me and if it helps other people then that is even better.

I recently needed to compare two columns of data in Excel and identify differences between them. For this example I will use the first 20 values from the Fibonacci sequence. Below is the  two columns with data that should be identical:

However it doesn’t take long to see the values differ on row 15. If this data set was twice as long or had more inconsistencies the chances of me missing something will increase. So over to Excel to do the heavy lifting of comparing these columns and flagging any differences.

A quick review of the available functions within Excel and the IF function looks like it will be a good starting point and after some experiments I settled on the following:

=IF(A1=C1, "Match", "ERROR")

What this formula will do is; if Row 1, Column A1 matches row 1 of C1 I will see the text Match and if they do not I will see ERROR.

After adding to a new column, I can see that for the row where the data is different, Excel reports ERROR.

This is a good start but I could still miss the ERROR, so I really would like it to stand out. One method is to achieve this is to use Excel’s Conditional Formatting.

You can find the Conditional Formatting on the Home tab of the Ribbon.

First select all the values in column E, the ones with either Match or Error then select Conditional Formatting followed by Highlight Cell Rules and finally Text that Contains… which will bring up the following dialog box:

I am most interested in cells that contain the ERROR text so I enter that in the text box and left the default colour choices.  After pressing OK I see that the ERROR now stands out.

I hope you found this useful.