A quick and dirty way to compare columns of Excel data

By Susan Harkins

April 6, 2012, 11:41 AM PDT

Takeaway: Using this little-known feature in Excel, you can quickly compare one column of data to another.

There are a number of ways to compare values. For example, conditional formatting is an easy way to highlight differences as they occur. However, there’s a quicker way if you don’t need a dynamic and more permanent solution. When you need just a quick one-time comparison, use Go To instead.

Automatically sign up for our Microsoft Office newsletter!

Now, let’s work through a simple example, as follows:

1. Select the multi-columnar data. This technique works with two or more columns. It’s important to remember that the feature compares all selected columns to the first column in the selection (as anchored, which isn’t necessarily the left-most column). In this case, you’d select cells A27:C27 (see below).
2. Press [F5] to launch the Go To dialog.
3. Click the Special button at the bottom.
4. In the resulting dialog, select Row Differences.
5. Click OK and Excel highlights the values in columns B and C that don’t match their corresponding values in column A.

In step 1, I mentioned anchoring. If you anchor the selection by selecting from the last cell in the right-most column and then highlighting from right to left, Excel will compare values in columns B and A to the values in column C.

The selection is temporary, but if that’s all you need, this is a quick and easy way to find differences between columns.
An example Excel worksheet of this technique is also available.