{"id":432030,"date":"2022-04-14T18:00:56","date_gmt":"2022-04-14T15:00:56","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-compare-two-lists-in-microsoft-excel\/"},"modified":"2022-04-14T18:00:56","modified_gmt":"2022-04-14T15:00:56","slug":"how-to-compare-two-lists-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-compare-two-lists-in-microsoft-excel\/","title":{"rendered":"#How to Compare Two Lists in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a2e0b842acc0\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a2e0b842acc0\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-compare-two-lists-in-microsoft-excel\/#%E2%80%9CHow_to_Compare_Two_Lists_in_Microsoft_Excel%E2%80%9D\" >&#8220;How to Compare Two Lists in Microsoft Excel&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-compare-two-lists-in-microsoft-excel\/#The_Quick_Way_Highlight_Unique_Cells_to_Compare_Lists\" >The Quick Way: Highlight Unique Cells to Compare Lists<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-compare-two-lists-in-microsoft-excel\/#The_Formula_Way_Use_Conditional_Formatting_to_Compare_Lists\" >The Formula Way: Use Conditional Formatting to Compare Lists<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Compare_Two_Lists_in_Microsoft_Excel%E2%80%9D\"><\/span>&#8220;How to Compare Two Lists in Microsoft Excel&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-757715\" data-pagespeed-lazy-srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel logo on a green background.\" width=\"1200\" height=\"675\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Microsoft Excel offers two different methods to help you compare two lists and highlight the missing items in each one. You can choose to highlight the missing items in both lists or in a single one. We\u2019ll show you how.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Find the Percentage of Difference Between Values in Excel<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"The_Quick_Way_Highlight_Unique_Cells_to_Compare_Lists\"><\/span>The Quick Way: Highlight Unique Cells to Compare Lists<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>A quick way to compare two lists in your spreadsheet is to <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/howto\/45670\/how-to-highlight-a-row-in-excel-using-conditional-formatting\/\">use Excel\u2019s unique highlight feature. This feature highlights the items in a list that are not found in the other list. This way you know exactly what items are missing from your lists.<\/p>\n<p>To use the method, first, select the lists you want to compare in your spreadsheet.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-790912\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/1-select-both-lists.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select both lists.\" width=\"517\" height=\"323\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>While your lists are highlighted, in Excel\u2019s ribbon at the top, click the \u201cHome\u201d tab.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-790913\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/2-excel-home-tab.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select &quot;Home&quot; at the top.\" width=\"622\" height=\"207\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>On the \u201cHome\u201d tab, in the \u201cStyles\u201d section, click Conditional Formatting &gt; Highlight Cells Rules &gt; Duplicate Values.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-790914\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/3-excel-highlight-items.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Choose Conditional Formatting &gt; Highlight Cells Rules &gt; Duplicate Values.\" width=\"650\" height=\"475\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In the \u201cDuplicate Values\u201d box, click \u201cDuplicate\u201d and choose \u201cUnique.\u201d Click the second drop-down menu and choose how you would like to highlight the missing items. To specify your own formatting, choose \u201cCustom Format\u201d from the menu.<\/p>\n<p>Then <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>ly your changes to your lists by clicking \u201cOK.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-790915\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/4-excel-highlight-unique-items.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Highlight unique cells in Excel.\" width=\"513\" height=\"200\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Excel will highlight the missing items in your lists. For example, in your first list, you will have those items highlighted that are missing from the second list, and so on.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-790916\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/5-list-comparison-excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Two lists compared in Excel.\" width=\"518\" height=\"318\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>And that\u2019s it.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"The_Formula_Way_Use_Conditional_Formatting_to_Compare_Lists\"><\/span>The Formula Way: Use Conditional Formatting to Compare Lists<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The above method highlights items in both your lists. If you\u2019d only like to highlight missing items in a specific list, then <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/740183\/how-to-use-conditional-formatting-to-find-duplicate-data-in-excel\/\">use a formula with conditional formatting as explained below.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Use Conditional Formatting to Find Duplicate Data in Excel<\/em><\/strong><\/p>\n<p>First, in your spreadsheet, select all rows of your first list. Then, in the top-left corner, click the text box, type <code>FirstList<\/code>, and press Enter. This assigns a unique name to your range of cells, which lets you refer to all these cells using a single name.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-790917\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/6-first-list-name.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Assign &quot;FirstList&quot; to the first list.\" width=\"521\" height=\"359\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Assign a unique name to your second list by first selecting all rows of your list. Then, in the top-left corner, click the text box, type <code>SecondList<\/code>, and press Enter.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-790918\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/7-second-list-name.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Assign &quot;SecondList&quot; to the second list.\" width=\"516\" height=\"357\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Select all rows of your first list by clicking the text box in the top-left corner and choosing \u201cFirstList.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-790919\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/8-select-first-list.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Choose &quot;FirstList.&quot;\" width=\"520\" height=\"326\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In Excel\u2019s ribbon at the top, click the \u201cHome\u201d tab and choose Conditional Formatting &gt; New Rule.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-790920\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/9-new-conditional-formatting-rule.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select Conditional Formatting &gt; New Rule.\" width=\"556\" height=\"500\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>On the \u201cNew Formatting Rule\u201d window, you\u2019ll specify how your missing items will be highlighted. On this window, from the \u201cSelect a Rule Type\u201d section, choose \u201cUse a Formula to Determine Which Cells to Format.\u201d<\/p>\n<p>In the \u201cFormat Values Where This Formula is True\u201d box, type the following:<\/p>\n<pre>=COUNTIF(SecondList,A1)=0<\/pre>\n<p>Select the \u201cFormat\u201d button and specify how you\u2019d like to format the missing items in your list. Then, save your changes by clicking \u201cOK.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-790921\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/10-compare-list-rule.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Format cells to compare lists in Excel.\" width=\"650\" height=\"466\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Back on the spreadsheet, Excel has highlighted the items in your first list that are missing from the second list. Your job is done.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-790922\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/11-compare-lists-excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Two lists compared in Excel.\" width=\"424\" height=\"292\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>And that\u2019s how you quickly perform a comparison of two different lists in your Excel spreadsheets. Very useful!<\/p>\n<hr\/>\n<p>You can also alphabetize your data to manually find the differences in two lists in your spreadsheets. Check out our guide to learn how to do that.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Alphabetize Data in Microsoft Excel<\/em><\/strong><\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/790901\/compare-two-lists-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Compare Two Lists in Microsoft Excel&#8221; Microsoft Excel offers two different methods to help you compare two lists and highlight the missing items in each one. You can choose to highlight the missing items in both lists or in a single one. We\u2019ll show you how. RELATED: How to Find the Percentage of&#8230;<\/p>\n","protected":false},"author":1,"featured_media":432031,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-432030","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/432030","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=432030"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/432030\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/432031"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=432030"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=432030"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=432030"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}