#275 Quick & Dirty Excel Data Comparison

You can compare your Excel data a bunch of different ways. For example, conditional formatting is one great way automate that comparison. This week I’d like to share something that works really well for quick, one-time comparisons.

How It Works

  1. Select the columns of data you want to compare.
  2. Press the F5 key to open the Go To box.
  3. Select the Row differences option, then click OK

Excel should have highlighted all the values in the other column(s) that don’t match the corresponding data in the first column.

NOTE: This will work with multiple columns and it does matter HOW you select your range. For example, in the 1st example below I selected the cells by starting in A1. Now compare that to the 2nd example where I started in C8.

The comparison is from the first column selected to all the other columns.

  vs.  

Advertisements

5 thoughts on “#275 Quick & Dirty Excel Data Comparison

  1. LeAnn Unverzagt says:

    I’ve always been a big fan of conditional formatting to reduce ‘ocular analysis’. Thanks for showing me this functionality that adds a great dimension to it.

    Like

  2. Craig says:

    Please note that the data to be compared does not necessarily have to be in adjacent columns. If you select the first column by drag selecting, then hold your CTRL key down and select a non-adjacent column (of equal length), the Row Difference and Column Difference functions work just as well.

    Like

  3. Rick Byrd says:

    Row Differences and Column Differences can also be used to find inconsistencies in formulas. This is really handy in maintaining calculation intensive spreadsheets.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s