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.
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).
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!
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. :)
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.
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?
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
Have a large list of vendors, titles, salaries and such and I receive a "new updated version could have new people added or name changes and need a Formula to "Compare" the new one with the old and see where those changes are made.If I need to Copy and Paste the new one on a Worksheet in one Workbook or from a new Workbook?
Thanks for your efforts here. 'Use a formula to determine which cells to format' option is missing, does anyone have any advice? The window that pops up is tiny and basic compared to yours. Excel Version 16.72 for Mac.
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.
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
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?
Late to this video but would it still work if one sheet has the file sorted by last name alpha and the second sheet is sorted by IDs. Also what if one sheet has more rows than the other sheet.
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,
What is the best way to compare columns of data across 2 sheets in the same workbook. If any data from sheet 2 column C shows in sheet 1, column AV - highlight green. If no values match, highlight red.
I have a question! I have two worksheets. one work sheet consists of CustomerName in one column and Income in another column. The second sheet consists of CustomerName in one column and email in another column. The sheet with customername and income has been filtered to provide the customers I am interested in and now I want the sheet with the customername and email to be filtered to only show the customers that I have on the customer and income sheet. How do I do this?! Thank you so much in advance for any help
Great video. Really helpful. Thanks!! How would you compare them if lines in the sheet were deleted on a newer version? Only asking because, once a line has been deleted it highlights them all from below that point.
I have the same problem. Or what if you have extra rows or columns in the other workbook? Adding to the difficulty when you don’t have a clear ID or customer number or something similar to have as a reference cell. 😢
i m not able to solve one problem last few years My issue is i compare two msword file after that wrong and right error show in msword that right and wrong and right and wrong word i want to copy paste in excel sheet i can copy but there are more thousand of words but i want to copy only right and wrong in excel sheet please any one help
I have a excel database that I collect multiple COVID data on separate sheets from various departments. Is it possible to create a form to update multiple sheets in one location?
I am very frustrated. This worked but only once. Its not a refresh issue. Let me explain. I have one WB with two sheets. One has a list of names that have done business with us. The other is a list for that month and there are NEW partners that have not done business with us. I jsut want to automatically highlight the new partners. I followed this but it only partially worked. Upon initial copy and paste of names but If I go and add a name after that it stays highlighted even if it is on the YTD list. Can you help me? I can send you the WB if need be.
Thank you very much for the video! However, I notice that the date format has changed into numbers after the quotation, could you tell us how to convert it back to the format of date? Thank you for your attention.
Thanks for this teaching, I have watched many videos on YT but your is very precise. I have a problem with some excel and I don’t know how to fix it. 2 workbook with hrs one is military time and the other in decimal e.g 70.87. How do I change the decimal time to regular hours and apply vlookup and compare hrs in both workbook.
Chester - excellent stuff. thanks for sharing! Question, how would you recommend handling comparison of a master sheet and a data sheet which has a different number of rows and only comparing certain columns?
Looks like it does, I'm trying to compare 2 sheets with very different values to see where the duplicates are and this only compares A1 - TABLE1 vs A1 - TABLE2
Thank you Chester for your great tutorial. Is there any way to 1- conditional the duplicates in 2 different sheets with different headings (Only Item # is the similar in the headings of these 2 sheets?) 2-copy the duplicates records to the seperate sheet? Ex: Sheet 1 has heading Item # in cell A1, B1 = Description1, C1 = Price , etc...Sheet 2 has heading including Item #, Shipping Method, Payment Type, Destination, etc.......thank you so much Chester. Liked and subscribed !
That is because Excel doesn't allow you to use conditional formatting across workbooks. You have to use the 3rd method he shows in the video, which is not as fun.
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.
This is the by far the best tutorial
I found on RUclips that explains how to compare data in two spreadsheets
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).
Same question
Did you ever find the answer?
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!
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?
That's what I wanted to know too!
Looking for this answer as well :)
Vlookup
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. :)
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.
Thanks!
Many thanks!
Hi I got an error message saying 'you may not use refrences to other workbooks for conditioning formatting ' may you kindly explain why? Pls
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?
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?
Thank you - that was a great timesaver and made an impossible task a breeze.
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
Have a large list of vendors, titles, salaries and such and I receive a "new updated version could have new people added or name changes and need a Formula to "Compare" the new one with the old and see where those changes are made.If I need to Copy and Paste the new one on a Worksheet in one Workbook or from a new Workbook?
Well that is so so so useful and so esay! Brilliant. Thanks for sharing. I'm putting this to use right now!
Thanks for your efforts here. 'Use a formula to determine which cells to format' option is missing, does anyone have any advice? The window that pops up is tiny and basic compared to yours. Excel Version 16.72 for Mac.
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.
In a single file we have to keep both the tables. And prepare the table in a way the formula should be A1 to table2 A1 not like A1 to table2 G3..
Is there any video on how to reconcile an statement against the invoices that I have entered on the system?
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
Does it compare different sheets
One longer than the other
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?
This video helped me a lot. Thank you.⭐⭐⭐⭐⭐
Late to this video but would it still work if one sheet has the file sorted by last name alpha and the second sheet is sorted by IDs. Also what if one sheet has more rows than the other sheet.
Thank you, the video is so helpful and easy to follow. Subscribed to your channel right away.
Thank you so much for teaching us an extremely cool method!!!
It is just what I need. Thank you very much!
Does anyone know how to do this in the Online version of Excel? The online version does not let me do the same thing as this.
Thank you. This was very helpful.
you saved me 8 hours worth of work
Great video i find it very helpful
Excellent video! Do you have any tip to display the result quickly if we have 10 000 rows ? :) Thanks
Fantastic compares and so well explained.
Thank you!
Really helpful. Thank you.
I hav one question, how to check difference between 2 columns where one is giving name with legal form and another column without legal form
Excellent, Good stuff 🎉🎉🎉
How does it react if something is on the first one but not on the second, or on the second and not the first?
Thank you for that great video!
Is it the same in mac ?
Very well explained. TY
Great, fast, simple...
Hi! This is great! Unfortunately it concatenates even when Ver 1 A1 = Ver 2 A1. Any suggestion? Thanks!
Excellent! Really useful! Thanks
how to highlight the different of the two workbook with color but automatically?
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,
Very useful information
Can you do this for bank recs
exceptional video. Thanks. Hugs.
Thanks! Very useful 👌
In my case, Both excel does not contain all the ids. What should be the formula for that?
What if I want to compare to lines but the order is messed up? Or if I want to check for extra contents and lines?
What is the best way to compare columns of data across 2 sheets in the same workbook. If any data from sheet 2 column C shows in sheet 1, column AV - highlight green. If no values match, highlight red.
Thank you sir, you’re a lifesaver!!!!
Thanks. There are 2 spreadsheets. Bigger spreadsheet containing small spreadsheet data. How to find the different rows in bigger spreadsheet?
What if V2 has new values, that V1 doesnt. How can you be able to show select them?
Thank you very helpful
I have a question! I have two worksheets. one work sheet consists of CustomerName in one column and Income in another column. The second sheet consists of CustomerName in one column and email in another column. The sheet with customername and income has been filtered to provide the customers I am interested in and now I want the sheet with the customername and email to be filtered to only show the customers that I have on the customer and income sheet. How do I do this?! Thank you so much in advance for any help
Hi Chester i got this error "You may not use references to other workbooks for conditional formattung critieria"
Great video. Really helpful. Thanks!! How would you compare them if lines in the sheet were deleted on a newer version? Only asking because, once a line has been deleted it highlights them all from below that point.
I have the same problem. Or what if you have extra rows or columns in the other workbook? Adding to the difficulty when you don’t have a clear ID or customer number or something similar to have as a reference cell. 😢
I am getting you may not use references to other workbooks for Conditional formatting criteria
what should we do if the datasets are diferent in size
Useful thanks
i m not able to solve one problem last few years
My issue is i compare two msword file after that wrong and right error show in msword that right and wrong and right and wrong word i want to copy paste in excel sheet i can copy but there are more thousand of words but i want to copy only right and wrong in excel sheet please any one help
I have a excel database that I collect multiple COVID data on separate sheets from various departments. Is it possible to create a form to update multiple sheets in one location?
I am very frustrated. This worked but only once. Its not a refresh issue. Let me explain. I have one WB with two sheets. One has a list of names that have done business with us. The other is a list for that month and there are NEW partners that have not done business with us. I jsut want to automatically highlight the new partners. I followed this but it only partially worked. Upon initial copy and paste of names but If I go and add a name after that it stays highlighted even if it is on the YTD list. Can you help me? I can send you the WB if need be.
So helpful! Thank you!!!
bro you save me! thanks !
Glad to hear that
Thank you very much for the video! However, I notice that the date format has changed into numbers after the quotation, could you tell us how to convert it back to the format of date? Thank you for your attention.
Hey nice work! Also, what was that music you played in the background, that was relaxing
How to highlight the data with Visual Basic
Thanks for this teaching, I have watched many videos on YT but your is very precise.
I have a problem with some excel and I don’t know how to fix it. 2 workbook with hrs one is military time and the other in decimal e.g 70.87. How do I change the decimal time to regular hours and apply vlookup and compare hrs in both workbook.
Chester - excellent stuff. thanks for sharing! Question, how would you recommend handling comparison of a master sheet and a data sheet which has a different number of rows and only comparing certain columns?
Nice👍 Thank you🙂
Mine is showing there are differences in cells but it's identical...why is that?
You may need to trim the cells first =TRIM(A1) will remove spaces before and after the value
Thank you for clear explanation. I have a question :Does the column order and column title matter when comparing the sheets?
Looks like it does, I'm trying to compare 2 sheets with very different values to see where the duplicates are and this only compares A1 - TABLE1 vs A1 - TABLE2
Thank you!
helpful. Thank you.
You are the lifesaver!!! Thank you.
Thank you Chester for your great tutorial. Is there any way to 1- conditional the duplicates in 2 different sheets with different headings (Only Item # is the similar in the headings of these 2 sheets?) 2-copy the duplicates records to the seperate sheet? Ex: Sheet 1 has heading Item # in cell A1, B1 = Description1, C1 = Price , etc...Sheet 2 has heading including Item #, Shipping Method, Payment Type, Destination, etc.......thank you so much Chester. Liked and subscribed !
THANK YOU!!!!!
What is this | in the formula. doesn't accept it
Thank you
please i have an issue this please report the prblm dear admin .I want to compare two different worksheets containing a list
I keep getting the following message " "You may not use references to other worksheets or workbooks for Conditional Formatting criteria"
That is because Excel doesn't allow you to use conditional formatting across workbooks. You have to use the 3rd method he shows in the video, which is not as fun.
I need somthing simple, check 2 workgorup data and find what is missing, this is toooooooo complicated
Thanks but all these methods will not work for big tables
Tku sir
👍
⭐️⭐️⭐️⭐️⭐️
Thank you - that was a great timesaver and made an impossible task a breeze.
Thank you!!!