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.
Now, let's work through a simple example, as follows:
- 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).
- Press [F5] to launch the Go To dialog.
- Click the Special button at the bottom.
- In the resulting dialog, select Row Differences.
- Click OK and Excel highlights the values in columns B and C that don't match their corresponding values in column A.
LEARN MORE: Office 365 Consumer pricing and features
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.
- How to add a drop-down list to an Excel cell (TechRepublic)
- 13 handy Excel data entry shortcuts (free PDF) (TechRepublic)
- Six clicks: Excel power tips to make you an instant expert (ZDNet)
- Microsoft 365: A cheat sheet (TechRepublic)
- You've been using Excel wrong all along (and that's okay) (ZDNet)
- 10 time-saving tips to speed your work in Excel (TechRepublic)
Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.