{"id":2128,"date":"2022-04-11T13:46:51","date_gmt":"2022-04-11T08:46:51","guid":{"rendered":"https:\/\/www.yourrightdecision.com\/blog\/?p=2128"},"modified":"2022-04-11T13:46:53","modified_gmt":"2022-04-11T08:46:53","slug":"excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks","status":"publish","type":"post","link":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/","title":{"rendered":"Excel: How to find differences between two sheets. Same and Different Workbooks"},"content":{"rendered":"\n

In Microsoft Word, you will quickly find the compare documents features under REVIEW -> Compare.<\/strong> However, comparing two sheets in Microsoft Excel is not pretty straightforward. As mentioned at this link<\/a>, Microsoft has created a separate add-on Spreadsheet Compare<\/strong> that needs to be invoked from the Start menu to compare two sheets in Excel. The comparison results are mentioned in a separate pane with the columns of Cell, Value 1, Value 2, and Change Description. It can be quite confusing for a novice user of Excel.<\/p>\n\n\n\n

In this blog post, I am presenting a better approach to finding differences between two sheets. I will present the solution in both cases, i.e. if 1) both sheets are in the same workbook and 2) if both sheets are in different workbooks. Although you will have to write some VBA (Visual Basic for Applications) code to accomplish this task, the code is very simple. You just need to copy-paste this code and change the names of the workbook and sheets to make it work for you.<\/p>\n\n\n\n

Suppose you created two workbooks Employees Jan.xlsx<\/strong> and Employees Feb.xlsx<\/strong>. The file Employees Jan.xlsx <\/strong>has a sheet Jan<\/strong>. The file Employees Feb.xlsx<\/strong> has a sheet Feb. <\/strong>Sheet Jan <\/strong>contains employees’ names and salaries of January Payroll. Sheet Feb<\/strong> contains employees’ names and salaries for February Payroll. Now you have to find out what changed in Feb? i.e. How many new employees were inducted and if there were changes in the salaries of the existing employees. Sheet Jan<\/strong> and Sheet Feb<\/strong> look as follows:<\/p>\n\n\n\n

Sheet: Jan<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Sheet: Feb<\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

As it is a small dataset with sample values, you can even see manually that Sheet Feb<\/strong> differs from Sheet Jan<\/strong> in some ways. The salary is changed for Employee Numbers 101 and 108. Moreover, a new employee with Employee Number 110 is inducted. The code below will yellow-highlight these entries in Feb<\/strong> sheet.<\/p>\n\n\n\n

Place the cursor\/pointer on any cell in the Feb<\/strong> sheet such as A1<\/strong>. Now press Alt+F11<\/strong>. You will see the following screen:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now double-click ThisWorkbook <\/strong>as shown below:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now enter the following code in the Code area:<\/p>\n\n\n\n

Sub CompareSheets()\r\nDim rngCell As Range\r\n \r\nFor Each rngCell In Worksheets(\"Feb\").UsedRange\r\n\r\n    If Not rngCell = Workbooks(\"Employees Jan.xlsx\").Worksheets(\"Jan\").Cells(rngCell.Row, rngCell.Column) Then\r\n        rngCell.Interior.Color = vbYellow\r\n    End If\r\n\r\nNext rngCell\r\n\r\nEnd Sub<\/code><\/pre>\n\n\n\n
The code will look like this in the Code window:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Now place the cursor anywhere between Sub CompareSheets() <\/strong>and End Sub<\/strong> and press F5<\/strong> to execute the code.<\/p>\n\n\n\n
Now when you see the Feb<\/strong> sheet, you will observe that salaries for Employee Numbers 101 and 108 are yellow-highlighted because salaries have been increased in Feb. You will also notice that all the information of Employee Number 110 is yellow-highlighted because he is a new employee. The final output will be as follows:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Now delete the code and copy-paste it whenever you need it. You cannot retain the code for future use because Excel does not allow to store macros in xlsx<\/strong> files. For this purpose, you will have to save the file as an xlsm<\/strong> or Macro-Enabled file.<\/p>\n\n\n\n
Suppose you have both sheets Jan<\/strong> and Feb<\/strong> in the same workbook. In that case, you are not required to mention the file name, and the code can further be simplified as follows:<\/p>\n\n\n\n
Sub CompareSheets()\r\nDim rngCell As Range\r\n \r\nFor Each rngCell In Worksheets(\"Feb\").UsedRange\r\n\r\n    If Not rngCell = Worksheets(\"Jan\").Cells(rngCell.Row, rngCell.Column) Then\r\n        rngCell.Interior.Color = vbYellow\r\n    End If\r\n\r\nNext rngCell\r\n\r\nEnd Sub<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
In Microsoft Word, you will quickly find the compare documents features under REVIEW -> Compare. However, comparing two sheets in…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27,9],"tags":[],"class_list":["post-2128","post","type-post","status-publish","format-standard","hentry","category-excel","category-tech"],"yoast_head":"\nExcel: How to find differences between two sheets. Same and Different Workbooks - Your Right Decision Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel: How to find differences between two sheets. Same and Different Workbooks - Your Right Decision Blog\" \/>\n<meta property=\"og:description\" content=\"In Microsoft Word, you will quickly find the compare documents features under REVIEW -> Compare. However, comparing two sheets in…\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/\" \/>\n<meta property=\"og:site_name\" content=\"Your Right Decision Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/yourrightdecision\" \/>\n<meta property=\"article:published_time\" content=\"2022-04-11T08:46:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-04-11T08:46:53+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/04\/sheetjan.jpg\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/person\/1619bc20b251247bf3089fa41c507641\"},\"headline\":\"Excel: How to find differences between two sheets. Same and Different Workbooks\",\"datePublished\":\"2022-04-11T08:46:51+00:00\",\"dateModified\":\"2022-04-11T08:46:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/\"},\"wordCount\":522,\"commentCount\":41,\"publisher\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/04\/sheetjan.jpg\",\"articleSection\":[\"Excel\",\"Technology\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/\",\"url\":\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/\",\"name\":\"Excel: How to find differences between two sheets. Same and Different Workbooks - Your Right Decision Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/04\/sheetjan.jpg\",\"datePublished\":\"2022-04-11T08:46:51+00:00\",\"dateModified\":\"2022-04-11T08:46:53+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#primaryimage\",\"url\":\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/04\/sheetjan.jpg\",\"contentUrl\":\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/04\/sheetjan.jpg\",\"width\":430,\"height\":261},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.yourrightdecision.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel: How to find differences between two sheets. Same and Different Workbooks\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#website\",\"url\":\"https:\/\/www.yourrightdecision.com\/blog\/\",\"name\":\"Your Right Decision Blog\",\"description\":\"Your Right Decision on the Web\",\"publisher\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.yourrightdecision.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#organization\",\"name\":\"Your Right Decision\",\"url\":\"https:\/\/www.yourrightdecision.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2020\/11\/156.jpg\",\"contentUrl\":\"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2020\/11\/156.jpg\",\"width\":364,\"height\":364,\"caption\":\"Your Right Decision\"},\"image\":{\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/yourrightdecision\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/person\/1619bc20b251247bf3089fa41c507641\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/4ae389a5bfe7418706d8fd3157e4bd2b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/4ae389a5bfe7418706d8fd3157e4bd2b?s=96&d=mm&r=g\",\"caption\":\"admin\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel: How to find differences between two sheets. Same and Different Workbooks - Your Right Decision Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/","og_locale":"en_US","og_type":"article","og_title":"Excel: How to find differences between two sheets. Same and Different Workbooks - Your Right Decision Blog","og_description":"In Microsoft Word, you will quickly find the compare documents features under REVIEW -> Compare. However, comparing two sheets in…","og_url":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/","og_site_name":"Your Right Decision Blog","article_publisher":"https:\/\/www.facebook.com\/yourrightdecision","article_published_time":"2022-04-11T08:46:51+00:00","article_modified_time":"2022-04-11T08:46:53+00:00","og_image":[{"url":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/04\/sheetjan.jpg","type":"","width":"","height":""}],"author":"admin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"admin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#article","isPartOf":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/"},"author":{"name":"admin","@id":"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/person\/1619bc20b251247bf3089fa41c507641"},"headline":"Excel: How to find differences between two sheets. Same and Different Workbooks","datePublished":"2022-04-11T08:46:51+00:00","dateModified":"2022-04-11T08:46:53+00:00","mainEntityOfPage":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/"},"wordCount":522,"commentCount":41,"publisher":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#primaryimage"},"thumbnailUrl":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/04\/sheetjan.jpg","articleSection":["Excel","Technology"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/","url":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/","name":"Excel: How to find differences between two sheets. Same and Different Workbooks - Your Right Decision Blog","isPartOf":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#primaryimage"},"image":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#primaryimage"},"thumbnailUrl":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/04\/sheetjan.jpg","datePublished":"2022-04-11T08:46:51+00:00","dateModified":"2022-04-11T08:46:53+00:00","breadcrumb":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#primaryimage","url":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/04\/sheetjan.jpg","contentUrl":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2022\/04\/sheetjan.jpg","width":430,"height":261},{"@type":"BreadcrumbList","@id":"https:\/\/www.yourrightdecision.com\/blog\/excel-how-to-find-differences-between-two-sheets-same-and-different-workbooks\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.yourrightdecision.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Excel: How to find differences between two sheets. Same and Different Workbooks"}]},{"@type":"WebSite","@id":"https:\/\/www.yourrightdecision.com\/blog\/#website","url":"https:\/\/www.yourrightdecision.com\/blog\/","name":"Your Right Decision Blog","description":"Your Right Decision on the Web","publisher":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.yourrightdecision.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.yourrightdecision.com\/blog\/#organization","name":"Your Right Decision","url":"https:\/\/www.yourrightdecision.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2020\/11\/156.jpg","contentUrl":"https:\/\/www.yourrightdecision.com\/blog\/wp-content\/uploads\/2020\/11\/156.jpg","width":364,"height":364,"caption":"Your Right Decision"},"image":{"@id":"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/yourrightdecision"]},{"@type":"Person","@id":"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/person\/1619bc20b251247bf3089fa41c507641","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.yourrightdecision.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/4ae389a5bfe7418706d8fd3157e4bd2b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ae389a5bfe7418706d8fd3157e4bd2b?s=96&d=mm&r=g","caption":"admin"}}]}},"_links":{"self":[{"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/posts\/2128","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/comments?post=2128"}],"version-history":[{"count":4,"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/posts\/2128\/revisions"}],"predecessor-version":[{"id":2155,"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/posts\/2128\/revisions\/2155"}],"wp:attachment":[{"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/media?parent=2128"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/categories?post=2128"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yourrightdecision.com\/blog\/wp-json\/wp\/v2\/tags?post=2128"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}