Feel Tips - Compare Columns of Excel Data with a Quicker Way

Microsoft Excel is the most popular application for users to handle different data information. Sometimes we need compare data in worksheet. Microsoft Excel offers a number of ways to compare data. For instance, conditional formatting is an easy way to highlight differences as they occur. However, if you don’t need a dynamic and more permanent solution, there is a quicker way.

In this post, you will learn a little-known feature in Excel which can quickly compare one or more columns of data to another. Now, follow the steps below to work through a simple example. 

1. Select the multi-column data in Excel worksheet. This solution works with one 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 as the image shows.

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.

It's temporary to compare columns of Excel data by using this solution. But if you just need one-time comparison, this should be a quicker and easier way to find differences between columns.