How to Compare Two Excel Sheets and Find Differences

👍👍If you have found this content useful and want to show your appreciation, please use this link to buy me a beer 🍺.
Thank you! 👍👍

In this video I demonstrate how to compare two sheets in Excel and find or highlight differences. I look at doing this where the worksheets are in the same workbook and where they are in different workbooks.
------------------------

[ncrhosigep]

The second method that is explained starting at minute 3:10 is exactly what I was looking for. Finally after reviewing dozens of "how to compare values in two sheets" videos I came across yours. Thank you.

[Lokinkende]

This is the by far the best tutorial
I found on YouTube that explains how to compare data in two spreadsheets

[HangNguyen-dkcp]

Millions thanks for the tips with easy-to-follow details!

[MarshaPautler]

QQ - how would you approach this if the data in the two versions are not in the same order and also if one version has more rows than the other (i.e. A sales report where opportunities are added and removed week over week and we want to track and flag the movement of changes to dates, revenue and also net new or net closed opportunities).

[billywright]

Very helpful and very clearly explained. Nearly stopped watching too soon and missed the last section on comparing sheets on 2 different workbooks/spreadsheets. That saves having to copy everything into the one file which I was having to do previously.

[codemonster]

Very helpful. Thank you. I wonder if there is a method to compare 2 excel tables when the rows are mixed, not at the same order?

[leanne]

Would this work if one of the workbooks or sheets has more data than the other (so additional customers on version 2 that are not on version 1), please? And would that additional data be transferred once the formula is run? Thank you. :)

[johnnybo]

Excellent explanation! I was using the vlookup formula on multiple columns to compare data from another sheet that is 20k+ rows. This definitely saves time and does not slow down the sheet. Thank you!

[hassantaha]

Very nice vedio, i need to ask what if i compare between two tables one of them has 50 columns and 300 rows and the other has 60 columnsand 306 rows, some of Columns and raws are similar but i need to find the differances in separate sheet

[valentinagrecchi]

Thank you - that was a great timesaver and made an impossible task a breeze.

[OCPrunsthecops]

I'm unable to do this. In the end, a pop up came up & read "you may not use references to other workbooks for conditional formatting criteria" is this a security issue?

[ShardulShrimani]

Thank you for the tutorial. How do you deal with a different order of transaction IDs please (and apply a conditional formatting colour to the changes)

[antoniossurvivor]

Awesome. I really loved and appreciated when you zoom at the formula in its tab. I was just wondering whether this also works for an update column in comparison to its old that has mo0re irrelevant items and values that need to be corrected

[renekirkwood]

I love this tutorial. I have to up the game a little though, I need to compare multiple sheets of contact lists, 6 to be exact. What changes do I need to do in the formula, if any, to compare all 6?

[Zues]

Thankyou for sharing this comparison method. I have a follow-up question pls. I want to compare two different worksheets containing a list of stocks. Each month, i get an updated stock list to the previous month's list. Some months will add or subtract stocks and/or add or subtract shares of certain stocks. Hence, the size of each worksheet could be different and indeed often are. It appears your method of comparing requires the worksheets to be of the same size. Is there another way that i could easily compare my two worksheets to show me what changed occured i.e., (what stocks or shares were added, subtracted, altered, etc.)? Appreciate the advise. Mike

[magdalachannelst]

Hi I got an error message saying 'you may not use refrences to other workbooks for conditioning formatting ' may you kindly explain why? Pls

[narendersingh]

Thanks, Sir. I am not able to compare two excel sheets. Error is showing as : "You cannot use references to other worksheets or workbooks for conditional formatting criteria" Sir, please give solution.

[lessisterswendyetmarine]

Excellent video! Do you have any tip to display the result quickly if we have 10 000 rows ? :) Thanks

[Rasheed]

Hello Chester, I watch most of your videos and have found it very helpful. I have a question, What to do in order to avoid the 0.01 cent difference between excel and calculator machine, example a total sum of few amount are either more or less that the calculator? Thanks,

[Tanquen]

How can you handle two sheets were the data location is not identical? Where sheets Ver1 and or Ver2 have rows the other does not?