SubscribeQuickly Compare Data using Row Differences
Posted on February 14th, 2011 in Excel Howtos - 22 comments
Since Financial Modeling School 2nd batch is opening next week, things have been a bit crazy at chandoo.org HQ.
So we will start the week with an ultra quick tip. It always surprises me that not many people know this. So here it goes,
Lets say you have some data in 2 columns and you want to compare row by row to spot the differences. Of course you can write a formula or apply conditional formatting. But there is a quick and dirty solution that works just as fine.
- Select both columns with data
- Press F5 and select special (alternatively, from home ribbon, click on Find & Select and then choose Goto Special)
- Now, click on “row differences” and press OK.
- Excel instantly highlights all the cells in 2nd column that do not match with first column.
- Just change their color or something so you know where to focus your attention.
- Done!
See this demo to follow the steps:
Want more? Here is more:
- A ridiculously fast way to highlight mismatches in data using Conditional formatting
- Learn more quick tips and become an Excel rock-star.
| ||
|
Spread some love,
Posts & Navigation
Tags: Excel 101, excel basics, goto special, keyboard shortcuts, Learn Excel, quick tip, screencasts, select special, spreadsheets, using excel |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
22 Responses to “Quickly Compare Data using Row Differences”
[...] This post was mentioned on Twitter by Chandoo.org and Stray__Cat, Excel Insider. Excel Insider said: Quickly Compare Data using Row Differences: Since Financial Modeling School 2nd batch is opening next week, thin… http://bit.ly/ii5AOk [...]
Thanks for the tips! I also found out that if there are 3 or more columns of data, it will hi-light the ones that are not the same as in the first column on the left.
I always wondered what the “row differences” features was about. You’ve solved the mystery. Nice tip.
excellent tip, thanks.
Awesome tip. Time and effort saver. Thanks!
Really, It has been an year or two.. since i am reading this blog.. but never though of commenting on any of the post.. but recently… learnt that by posting I can interact more with the excel lover around the world and learn more and more…
Never saw anything else… “Blank Cell” after pressing goto special or F5…
Thanks.. this is want it makes you higher then others… you explore and explore.. we just do what we know……
Onces again Thanks… would be interacting more often now onwards on Chadooo……..
wow that was oddly specific. there should be to a goto special for everything..
thanks Chandoo..
also works in Excel 2003 (shortcut: CTRL-G, alt-s).
can’t believe I have overlooked this for so many years, never found it mentioned in any Excel courses or manuals. many thanks!
Dear Chandoo…
i got to know about your blog from a newspaper…and since then its been very valuable addition of my daily visits to blogs…..u rock
recently i encountered a small hiccup with my excel work…..it was a long multi thousand rows spreadsheet with a heading on top…and my manager wanted it in print with one top row for headings of each column on each page…..is there a way we can fix some thing to be printed on top or bottom on every page and remaining text gets printed below on excel….
in word its easy as header and footer….but in excel…no header footer.. any suggestions??
I use the Goto Special dialog every day and can honestly say I’ve never even set eyes on this option before, and here’s me thinking I’m pretty clued up in Excel. I guess it only proves that you don’t know what you don’t know! Cheers, Chandoo!
Dear Vineet,
I think your requirement is to have a header and footer on every page for your few thousand rows when they get printed. Its easy…please follow the following simple steps:
1) Arrange your data for the print
2) In Excel 2007, Select Insert Menu and insert Header & Footer from the menu
3) You can now add your desired text in the header and/or footer.
When printing it will be displayed properly on each printed page.
Let me know if you need any further help on this.
Cheers
Ankur
Chandoo you are the Daddy! That is such a great timesaver. Love your site and your spirit which shines through. In answer to the guys query about printing there is also the option in Page setup to select Rows to print on each sheet which might work better than header or footer. Not in front of Excel now so tricky to explain from memory but maybe a post on printing options would be useful?
Thanks
Great tip. How would this work for 2 list of names?
Vineet,
in Excel 2003, click on File >page set up> sheet and in print titles, next to rows to repeat at top, choose your header row, when your report prints this will print your header row on each page
Cheers
Thnks dude for this tip
simply awesome….thanks for nice trick!
I know I’m a little late to the party, but the GoTo Special menu has some real nuggets of gold in there, especially when you combine it with some VBA…for example, if your code is something like this:
For each cell in Range(“A:A”)
‘do something here
Next
A more efficient option would be to use:
For each cell in Range(“A:A”).SpecialCells(xlCellTypeContstants)
‘do something else here
Next
We can use Ctrl +\ after selecting data
Can this be done for 2 columns in different sheets of the same workbook?
Awesome
Please see if you or someone can help me in this.
I have workbook1 with many rows. I have another workbook with few of these rows. Rows in workbook2 are not in any order or as per any sequence with respect to workbook1. These are derived/output from some application. My job is to find uncommon rows in workbook1.
Awesome………………..