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.
One Response to “How to compare two Excel sheets using VLOOKUP? [FREE Template]”
Maybe I missed it, but this method doesn't include data from James that isn't contained in Sara's data.
I added a new sheet, and named the ranges for Sara and James.
Maybe something like:
B2: =SORT(UNIQUE(VSTACK(SaraCust, JamesCust)))
C2: =XLOOKUP(B2#,SaraCust,SaraPaid,"Missing")
D2: =XLOOKUP(B2#,JamesCust, JamesPaid,"Missing")
E2: =IF(ISERROR(C2#+D2#),"Missing",IF(C2#=D2#,"Yes","No"))
Then we can still do similar conditional formatting. But this will pull in data missing from Sara's sheet as well.