How to Compare Two Excel Sheets (and find the differences)

[@trumpexcel]

If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what topics you want me to cover in future videos.

[@mohamedselim3526]

Hello All , what if i need to compare more then 2 months could you please till me how can i do it

[@paulinelawrence3827]

Hi This is a great tutorial - i have two sheets of data listing phone numbers for staff - each month staff join and leave and I want to find the difference between the two sheets - however the lines don't always correspond as a name in line 21 in one sheet is there in second sheet but at postion 14 (for example) is there a way I can just find the new or different values in the second sheet?

[@Owanango]

This is super useful. Your English is super cool too. Thank you

[@Kenny16301]

@@paulinelawrence3827 I'm having the same problem

[@kimchigirl262]

Add the formula to annotate the store number please.

[@begashawabegaz4224]

THE 3 G man Gentle, Genuine, Genius... Respect, from Ethiopia

[@Animesh_Goyal]

The last part is totally awesome. Thank you sir!

[@yogeshmathur3201]

One of the most useful formula, I was searching to find the difference and get in one single sheet! Thanks !

[@gailjurczyk]

Thank you for this clear, easy-to-follow instruction.

[@stenionet]

you know how to teach. thank you very much and greetings from Brazil.

[@varunduggal8920]

The way of explanation is too good thanks for video..

[@biswal999]

You are an awesome faculty, explain things step by step...

[@jorgeespinoza3428]

This video was awesome!!! thanks!! looking forward to new tips!

[@ninabirdsong9910]

You are very good in explaining..very helpful.

[@modisanjay]

Excellent , Easy to understand, Please keep it up

[@Mr_Marwaari]

Hi TrumpExcel, I am working in Aditya Birla Group in Dubai for past 4 years in Field of Excel Automation. And i must appreciate you for the way you are explaining to the learners. I usually do not see the basic videos but your thumbnail was too attractive to refuse. Kudos to Excel. Also in case you or any of your excel enthusiastic friend want to join for some role ill be happy to help. From Mudit Lalwani

[@innovativelearning8212]

For complicated and high volume reconciliations with multiple files, I have been using Foxpro programs since a long time....

[@Zanraff]

Exactly what I wanted to know how to do! Excellent explanation. Thank you. 😊

[@trumpexcel]

Glad you found the video helpful 🙂

[@hkjony]

Undoubtedly this is extremely helpful, keep up the good work Man

[@devendrabain7748]

Thanks you sumit, for covering such a daily occurring problem. Very useful video 👍👌👏

[@trumpexcel]

Glad you found it useful!

[@JumpRopeVeteran]

This is great. This is going to save me tons of time. Thank you.

[@HellenofTroy897]

These were valuable tips. Thank you so much.

[@jagdishagarwal4173]

Very good and very very useful..Thanks for posting..Loved it

[@FaysalEasyExcel]

from few days i am looking for this kind of video to get solution. finally found. thanks.

[@benponderin]

I thought it was going to be a different video and STILL found it useful.

[@CharmingHennaCreations]

You are teaching this so perfectly. This is so much easier and faster way to reconcile 2 data sets among many other methods sown in other videos . Thanks a Ton

[@nealdaringer3484]

No, there is way too much unnecessary information.

[@sujithsanjeewa1890]

Love to learn your tutorials Sumit. Very much useful...🙏

[@trumpexcel]

Thanks Sujith... Glad you're finding the videos useful!

[@powersystems2801]

Sirji Tusi great ho this was the video which I was finding at exactly I got the same. The way of your explanation is also superb

[@trumpexcel]

Thanks for the kind words :) Glad you fount it useful!

[@kimberlyjohnson7838]

Good video, do you have a formula that will compare an entire workbook of 12 pages to a separate worksheet with numbers and names? I need the ones that match to be highlighted.

[@pavanig2880]

Its very useful. Well explained. Thank you

[@muhammadjavediqbal6767]

Asslam Alaikum Sir I am a subscriber of your channel and your excel videos are very useful....

[@Sekhar_Home]

Really it is a mind blowing tutorial on excel. I was looking for it. Thanx u made my large work in easy.. more excel tutorial pls

[@trumpexcel]

Glad you found it useful!

[@bijoysaraf650]

Simply outstanding! Great tips. Liked & subscribed. Thank you for sharing.

[@keyurshah679]

Very nicely explained 👍. Very useful for day to day working

[@sharmilisen2926]

Amazing 👏. I was in need of these features in my process. Thank you so much 🙂👌

[@dannycaduff6342]

Thank you first for your nice videos and explaining things in Excel. In your video "How to Compare Two Excel Sheets (and find the differences)" you are talking of two identical lists and your formula only works with identical sheets. Here my question: Is there also a similar formula when the lists are not identical? (maybe with vlookup, match & index). Thank you for your answer, Danny

[@dataisfun4964]

This is Golden nice one guys, I love the conditional formatting to get the difference between months.

[@mostafakamal9803]

Thank you first for your nice videos and explaining things in Excel.

[@muskaanmathur7467]

Very useful information I was looking for !

[@prabur4027]

Thanks a lot for your video, it's very useful and informative ☺️

[@ijimnel]

Great video, thanks. I have 2 sheets (previous and current month reporting) each have an unique ID field and I want to compare the dates to find if any dates have changed. The current month will have new rows of data and some of the previous month data (completed items) will be removed. Therefore the data sets are not matching using a filter on the unique ID. Any advice?

[@yagitoshinori9272]

I too am curious. I have a similar issue but with tracking incoming and departing employees Edit: so I found that using IF(ISNA(VLOOKUP helped me. I can't post snips of my sheet but you can reverse engineer my formula below: IF(ISNA(VLOOKUP(B62,[ARRAY NAME],1,0)),"REMOVED","NO CHANGE") Don't forget to change the cell to whatever it is in your sheet

[@innovativelearning8212]

From ekm: E-verification, Drill bits, painting brush,submersible heater,chisel,chintheru, Mara ചുറ്റിക, projector motor and transformer. Old fan motor, cheriya vaakkathi, long drill bit, nylon, uli get ready, Rajesh license, അറക്ക പൊടി

[@rosariocoutinho8690]

I was looking for this formula for long time.

[@shannonxueping9493]

Thanks for the tips! Is this only for the comparison between the data in the same row from two workbooks/sheets, i.e. Row 1 to Row 1 and etc.? I have two different workbooks - Workbook A and Workbook B. Workbook A is a full list of a set of data that I exported. One of the columns in Workbook A contains identical data as one of the columns in Workbook B, but they are all in different rows. Is there any excel formula that we can use when we are comparing data in different rows?

[@eapanman7062]

Hi Shannon, did you get a reply back from Sumit Bansal? If not did you figure it out? I would be interested in knowing how to compare 2 files successfully. Thanks.

[@nickbell6311]

I always love ❤ your videos good job men

[@alialbayati9368]

Thank you Sumit for the tutorial.. Very helpful.

[@trumpexcel]

Glad you found it useful!

[@andriikulishov3371]

Dear Sumit, excellent video, thank you very much!

[@mnsoyan]

Thanks for this great trick! Note to myself: CHAR(10) = Add Line Break in Excel Formula

[@av7829]

I didn't get it. Why char(10) used?

[@ZeratulX]

@@av7829 that function is used to put the text in another row but in the same cell, that's why you can see the Feb values in another line or row below the Jan values.

[@Kisu_Famous]

Very very useful info 😊thank you sir 😊

[@razorpit]

The conditional formatting instruction was excellent. Exactly what I needed. Enjoyed the other information as well.

[@rajveerkhangarot5762]

Very well illustrated.

[@CZGrizzly]

Thanks for the video. I have one suggestion for improvement: Earlier I also used in conditional formatting the simple formula you suggest, but this has disadvantage that when you manipulate with sheet cells (eg. add/delete rows or columns), references break down and it doesn't work anymore (formatting must be restored). Much better solution is to use the formula: =INDEX(Sheet1Area,ROW(),COLUMN()) = INDEX(Sheet2Area, ROW(), COLUMN()) This will work no matter what cell manipulations you do in sheets in the future.

[@chowdhurysunzidmahbub79]

Please give a video of this topic.

[@narutomaroc1]

I faced the issue you mentioned. It would be great if you can make a tutorial.

[@abhiseksingh4886]

@@chowdhurysunzidmahbub79 can check this one. kzbin.info/www/bejne/a5fWnqxpqtyimZo

[@redsblack1842]

Thank you for the video but I want to know how will you know if data in one worksheet is present in another worksheet. Since in this video your working on the difference between two cells. Example. Let's say we have the figure 10 in cell A2 and 10 is present in another cell, let's say A4 in another worksheet. How will you know that the figure is present in that cell. Looking forward to your reply. Thanks

[@samantablur5924]

If you are looking for only one colum on each worksheet i think a vlookup would work

[@deeparun6761]

solved my whole office problem 👍👍👍

[@rushikeshkadam8350]

Super and useful information..Thank you!!

[@anaghababu3868]

I found this video very helpful. 👍

[@georgetosounidis5545]

Great tutorial again! Thanks a ton for this Sumit!!!

[@stephenolumide5889]

Great! even with my experience with excel, I haven't done anything like this before. Thank you bro

[@jennyzhang8023]

Extremely helpful! I wish I have learnt this earlier! Thank you so much!

[@wayneedmondson1065]

Hi Sumit.. thanks.. excellent video. Very useful and valuable tips. I especially like the way you set up conditional formatting to compare one version of a sheet to another (assuming same format and structure between the sheets). I can think of many uses for that in my business. I also like how you set up the formula to show the values of each different item on a third sheet.. brilliant! Thanks for sharing all of your great tips and tricks. Thumbs up for Trump Excel!

[@innovativelearning8212]

For complicated and high volume reconciliationI have been using Foxpro programs since as long time....

[@imranurrahman8538]

Hello Sumit. I can't express my gratitude towards you in words. Thank you so so so much. My name is Sami from Bangladesh. You are so talented, indeed. Thanks again.

[@ampm297]

Great video. Thanks. It saved me a lot of time and effort!

[@hammedcs1633]

For lazy people, =if(cell1!A1cell2!A1"cell1 Value:"&cell1!A1&Char(1000)&"cell2 value:"&cell2!A1,"") Thank you for the wonderful ppt, keep up the good work.

[@PandaGSXR]

This is a brilliant video. Thank you so much!

[@kimchigirl262]

What store were those changes in? What is the change and what is the store number the changes relate to? Could take this video and take the next step in relating the store number? Thanks

[@MrJagschoice]

Thanks for this video. Very good explanation. I have a question... let's say we have 2 sheets and want to highlight the values present in 1st sheet but not in 2nd and. vice versa in different color irrespective to exact row for ex. sheet1-A10 value might be there in sheet2-A15. can we do this kind of comparison?

[@aanand110]

Very useful video...please upload video on different price comparison&analysis of report pictorial representation in charts

[@Nihalsaviation350]

Very informative.... thank you.

[@rupeshpatil6957]

Hi Brother, How can we compare text columns with ignore case? Means ABC and abc should be equals and should not get highligted. Thanks in advance

[@adamt3077]

This saved me so much time thank you

[@trumpexcel]

Glad you found it helpful

[@happymm2008]

Thank you so much for your tutorial! Your tips a very helpful!

[@thomasbae2008]

Can you compare more than 2 sheets and pull the differences? Is there a way to use VBA to be able to repeat this process regularly?

[@massivelearn6063]

Awesome, Thank you more.. Can you please make video on writing user defined formulas under conditional formatting option... Thank you!!

[@yurd563]

Thank you for videolesson!

[@paramasivamsadagopan2581]

Nicely explained

[@TVSCDN]

Very good 👌 thanks

[@gunamaypal7548]

Very nice. Many thanks sir

[@skyrock2266]

a scenario where store is in a different order or may not be in incremental order then how do we compare two spread sheets considering store as the primary key.

[@ololade9703]

You changed my mindset about indians

[@greenfruit3165]

Hi TrumpExcel, i enjoyed your presentation. However, can you handle SPSS like this Excel tutorial?

[@Jen-tq2bt]

This is so useful. Thanks!

[@MrWLJ71]

Superb as ever, thank you

[@trumpexcel]

Thanks for commenting Bill... Glad you found the video useful!

[@abeibrahim5846]

Great tutorial - Q - How to get the delta between two Tables - using PQ - example: Need to know using an SW assets inventory Table, a client that has not been installed using the client current comparison Table. I must show the delta only using the Asset Name as the link field.

[@pennyl4952]

very helpful! thank you

[@barryk7949]

Very helpful! Thank you.

[@Dsamoil2905]

Very useful topic. Thanks sir

[@Ryou234r]

This is really helpful. Thank you.

Thank you, you help me very much :)

[@vikaasb2016]

Firstly, A Loud Shout out to u-This is by far the best tutorial for excel.I came back with appreciation after I did my research and learnt some unique short cuts hugely handy while at work. Also, I feel you cab be the best go-to man(teacher) for me....Sorry found you late @Sumit Bansal Sirrrrrr, My biggest pain in excel has been understanding cell referencing(not literal meaning but when using) and understanding arguments while writing formulae and formula per se....in particular the role of special characters.... Plssssssssssssssssssssssssss help ! Would be indebted for life and would spread a word far and wide !!!! I'm a in BD for last 12 years.....but want to master it..will follow u on linkedIn ,,twitter ...everywhere.👍👏👏👏👏👏👏👏👏👏👌👌👌👌👌👌👌👌😊✔✌✌✌✌✌✌

[@trumpexcel]

Hello Vikus... Have you seen my free Excel course playlist. I cover cell referneces in the course (along with many other topics)

[@abdulrahmanfahad4059]

HI thank you this very helpful , what if I have three work sheet and I want to do match , I tried but it dose not work , any suggestion ? thanks

[@Flux_One]

Do you have to create separate rules if the workbook contains many columns of data, or just highlight the column headers in the first sheet and the same ones in the second sheet and use one rule?

[@bernardanim2381]

Great tips! Thanks!

[@innovativelearning8212]

For complicated and high volume reconciliations with multiple files, I have been using Foxpro programs since a long time....

[@narendersingh6346]

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.

[@MushtaqQureshi]

Hi Sumit -If we want column A to be displayed in the difference tab? secondly explain us a sample of multiple columns comparison/differences.

[@rahulsingh-yh8hk]

I am facing the issue of for highlighting the changes You cant use references to other worksheets or workbooks for Conditional formatting criteria . Please help

[@dataisfun4964]

This is Golden nice one guys.

[@prashant1513]

Superb Yaar. But sir it can only happend when rows are equal in both sheets. Will it work if there are differences in rows..

[@manideepak6820]

Same doubt bro

[@Drkalaamarab]

Very very much useful

[@gupttura2766]

Bansal Sir, do you have any tutorial about a SMART drop down list where when a user enters new data (which doesn't exist in drop down) it gets copy over to Master data file and becomes part of drop down list? Thank you for your tutorials. They are very useful for someone learning excel.

[@innovativelearning8212]

For complicated and high volume reconciliations with multiple files, I have been using Foxpro programs since a long time....

[@savefraud5843]

Hello Sumit jee !! Thanks for the uploading this knowledgeable video.i am facing problem with the conditional formatting thing.can u help me out wth it ?? I am not able to open the second sheet once that conditional formatting tab is open so m not able to give the required formula.can u help me with this.i am using office 2007 is that anything to do with the version of the office.

[@chris-zu6sf]

I would like to input and compare 2 data sheets, with the second sheet being an update to the initial sheet. The output would be 2 tables, one identifying when a value from the first sheet has been omitted in the second data sheet, and the other output table identifies any new values that are only in the second data sheet. I'm thinking of using conditional formatting on the inputted sheets and then creating 2 pivot tables as the output because I am only concerned with the sum of the value type. Do you know if this should work? If so, can you show me how?

[@honestlygio7830]

The best I've seen thx! I'm subscribing.

[@mikewilson6710]

Is there away to connect multiple workbooks in Excel?

[@Allmytravelz7072]

Great tips. Does this only work if the data in both sheets are organized the same row for row. Take for example, we are comparing po numbers in one previous month, jan, to current month, feb. If list 2 is missing a po number on say row 4 , because it is closed in month feb but was open in month jan, then all data from Row 5 would throw up an error as they would not be aligned.

[@trumpexcel]

For the conditional formatting and the formula method, it works better when the data is aligned. You can still do the comparison if some data is missing, but you will have to account for that in the formula. For example, instead of comparing the entire dataset, may be compare column wise.

[@Allmytravelz7072]

Trump Excel got it, i will put it to the test , thanks for the free knowledge share!

[@ankursharma6157]

Hi, Please try Consolidate (Data tab > Data Tools group > Consolidate). As the name suggests, it helps to Consolidate Data. Please note it does not compare. However, Consolidate makes Comparison easier. For comparison, after using Consolidate, You could use suggestions shared by Sumit. Hope, this helps.

[@manishatiwari7006]

Does it work for content matching of two webpages? I have long text from two sites I need to compare and find only the mismatched word/words and print it. Can we do that or it will return the entire text even if there is a single word mismatch and rest all is matching?