Software

A quick and dirty way to compare columns of Excel data

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:

  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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

25 comments
scandent
scandent

This is a cool trick, but I have a different need. I wish to compare a column of 2 digit alpha characters to a master list on a different sheet and have the result identify which strings are missing, or which ones are used more than a single time. Background is its a gantt style workforce plan that uses employee initials in each day's column. I have set up data validation so that only the initial sets from the master personnel list can be entered, However i need to know if someone enters a person twice on different tasks, and also who hasnt been scheduled on a given day. Any ideas?

ljubisab
ljubisab

If you like keyboard shortcuts, like me, you can use: - Ctrl-Space bar, for selecting first column - Shift-Right arrow, for selecting adjacent columns to compare (or Ctrl-Left click if they are not adjacent) - Ctrl-G, Alt-S, Alt-W, Enter (Go To, Special, Row differences, OK) And it becomes magic to people who watch.

montwood
montwood

Brilliant - thanks wish I'd known it 10 years ago

DBlayney
DBlayney

How long has that feature been there, unnoticed by most Excel users I am sure. One extra "wrinkle" - if you want compare columns A and C against column B: select A, then C and then B (Control key held down for C and B)

venkat1926
venkat1926

good tip. but this highlights non duplicate value in the same row.not in the any row of the range am I right?

andrewmrichards
andrewmrichards

This is a great tip and one I often show my delegates when running training courses. But you're right that it is a very temporary step - one false click and you've lost the information. So, what I get people to do is follow all the steps above so that cells are selected where differences exist, and then simply click on the fill tool to colour those cells - meaning that if you now accidentally click away, you can still spot the cells with differences! Andrew

lynn.preston
lynn.preston

You take two lists in Excel. You can compare for changes in only one column or you can compare two columns. I designed this because in the Billing Dept., our clients might add, change, or delete items in a monthly data report that they would return to us in an Excel format. This is my link at http://www.prestonofficesolutions.com/comparing_lists.html. I enjoyed all of your input and this article by Susan today. It's all helpful.

Dave-22
Dave-22

More permanent is the =B1=B2 method to compare say values in B1 & B2, gives result TRUE or FALSE

itboss
itboss

Hi useful article - thanks RW

aikimark
aikimark

Susan Very slick. Thanks for posting. But wait...there's more: * There's also a ColumnDifferences() method * We can use the RowDifferences() and ColumnDifferences() methods in VBA. * We can specify non-contiguous areas. * Since these methods return range objects, we can name the range, select the range, or apply some formatting. Examples: activesheet.range("a1:a12,f1:f12").rowdifferences(activesheet.range("f1")).Select activesheet.range("a1:a12,f1:f12").rowdifferences(activesheet.range("a1")).interior.color=vbyellow

mail
mail

Thanks for that, Susan, and to Tracy and Marc.

marc.lowy
marc.lowy

After you get your results with the temporary shading of the cells, just click on the "Home" menu item (for Excel 2010) and then format the text or cell however you want it to look. (Bold, italic, a color, etc.).

traceymccabe
traceymccabe

Select the 2 columns you want to compare, Select conditional formatting, Select Highlight Cells Rules, Select Duplicate Values. Then select the type of formatting you desire, or click OK to use the default.

mark.robinson
mark.robinson

Unfortunately, some of us are still stuck with our companies' older versions of MS Office. Personally, I'm still having to use Excel 2000, for which this tip isn't relevant, so it would be useful (and less of a tease) if you could highlight the applicable Excel versions at the beginning of the articles! Maybe one day..........

HAL 9000
HAL 9000

Try reposting this in the 'Q&A' forum. The 'Discussion' forum is for matters of general discussion, not specific problems in search of a solution. The 'Water Cooler' is for non-technical discussions. You can submit a question to 'Q&A' here: http://www.techrepublic.com/forum/questions/post?tag=mantle_skin;content There are TR members who specifically seek out problems in need of a solution. Although there is some overlap between the forums, you'll find more of those members in 'Q&A' than in 'Discussions' or 'Water Cooler'. Be sure to use the voting buttons to provide your feedback. Voting a '+' does not necessarily mean that a given response contained the complete solution to your problem, but that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours. If they have a ready source of reference available, perhaps won't need to repeat questions previously asked and answered. If a post did contain the solution to your problem, you can also close the question by marking the helpful post as "The Answer". . [b]You really need to post this as a Question as it's now more than a bit old and is unlikely to be seen by anyone related to the original Blog.[/b] ;) Col

hochspeyer
hochspeyer

It is a neat little utility, but it appears that in 2003, one has to select the entire column rather just anchoring the range at the bottom of the columns- looks it works that way for noncontiguous columns as well.

Glenn from Iowa
Glenn from Iowa

Another (sometimes quicker) way to compare against column B is to select columns A:C (starting in column A), then press Tab, which keeps the selection, but moves the active cell to the next cell in the selection, in column B. By the way, the Tab key can also help if you select a range from the top-left, but want to get to the last cell in the range - just press Shift-Tab to move backwards one cell. Also, thank you Susan, for providing a demo spreadsheet to experiment with!

Glenn from Iowa
Glenn from Iowa

I agree - it is very useful, and I will probably use it when I have multiple columns (or rows) to compare! However, I often have long lists (several thousand +) of two columns to compare , and it's not very efficient to scroll down and try to see which cells are selected, highlighted, etc. so I came up with this formula: =IF(A2=B2,0,1) Then copy this down the column (scroll to the bottom of the column, Shift-Click on the bottom cell, and use Ctrl-D to fill down) and look in the bottom right corner of the Excel window. If the Sum: is 0, I can tell instantly that there are no differences (or if > 0, how many differences) without scrolling.

ssharkins
ssharkins

All tips are written for 2010, with instructions for 2007 and 2003 included, when necessary. It just isn't practical to support anything older than 2003, and it'll slip into history sooner than most of us would like.

ssharkins
ssharkins

It works the same in 2003. Highlight the data range, press [F5], click Special, select Row Differences, click OK. Where are you losing the process?

Editor's Picks