{"id":1506,"date":"2015-07-09T17:52:54","date_gmt":"2015-07-09T15:52:54","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=1506"},"modified":"2023-11-30T01:37:36","modified_gmt":"2023-11-30T00:37:36","slug":"consolidate-in-excel-with-the-consolidate-tool","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/consolidate-in-excel-with-the-consolidate-tool\/","title":{"rendered":"Consolidate in Excel with the CONSOLIDATE Tool"},"content":{"rendered":"<p>The Consolidate tool in Excel is located in the Data menu and combines values from multiple ranges into one new range.<\/p>\n<p>You would use this feature when you have a single text column on the left and the column has many duplicate values.<\/p>\n<p>First you need to select a blank cell outside your data set and then go to <em>Data &gt; Consolidate<\/em>.\u00a0 You will then need to select all your data and make sure that the <strong>Top Row<\/strong> and <strong>Left column<\/strong> boxes are checked.<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/MIlMqQh5rqA?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><\/p>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><br \/>\n<span style=\"text-decoration: underline; color: #0000ff;\"><a class=\"IRPP_minimalist icon-download\" style=\"color: #0000ff; text-decoration: underline;\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download workbook<\/span><span class=\"postTitle px-3\">Consolidate.xlsx<\/span><\/a><\/span><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1: <\/strong><\/span>Select a cell outside your data table<b>.<\/b><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate in Excel with the CONSOLIDATE Tool\"  class=\"alignnone size-full wp-image-19650\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-01.jpg\" alt=\"Consolidate in Excel with the CONSOLIDATE Tool\" width=\"637\" height=\"503\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-01.jpg 637w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-01-300x237.jpg 300w\" sizes=\"(max-width: 637px) 100vw, 637px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2: <\/strong><\/span>Go to <em><strong>Data &gt; Consolidate<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate in Excel with the CONSOLIDATE Tool\"  class=\"alignnone size-full wp-image-19651\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-02.jpg\" alt=\"Consolidate in Excel with the CONSOLIDATE Tool\" width=\"815\" height=\"152\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-02.jpg 815w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-02-300x56.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-02-768x143.jpg 768w\" sizes=\"(max-width: 815px) 100vw, 815px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3: <\/strong><\/span>For the <strong>Reference<\/strong>, make sure to select the entire table.<\/p>\n<p>Then ensure Use Labels has both <strong>Top Row <\/strong>and <strong>Left Column <\/strong>selected.<\/p>\n<p>Press\u00a0<strong>OK.<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate in Excel with the CONSOLIDATE Tool\"  class=\"alignnone size-full wp-image-19652\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-03.jpg\" alt=\"Consolidate in Excel with the CONSOLIDATE Tool\" width=\"836\" height=\"515\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-03.jpg 836w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-03-300x185.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-03-768x473.jpg 768w\" sizes=\"(max-width: 836px) 100vw, 836px\" \/><\/p>\n<p>Your consolidated data table is now ready!<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate in Excel with the CONSOLIDATE Tool\"  class=\"alignnone size-full wp-image-19653\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-04.jpg\" alt=\"Consolidate in Excel with the CONSOLIDATE Tool\" width=\"697\" height=\"509\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-04.jpg 697w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/07\/Consolidate-in-Excel-04-300x219.jpg 300w\" sizes=\"(max-width: 697px) 100vw, 697px\" \/><\/p>\n<p><strong><style>.special-box a { color: #fff; } <\/style><div class=\"drop-shadow lifted special-box\" style=\"text-align: center; color: #fff; padding: 15px; background: var(--V4-primary-orange); border-radius: 6px;\"> <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-51.html\" target=\"_blank\" rel=\"noopener noreferrer\">&#x1f449; Click Here To Join Our Excel Academy Online Course &amp; Access 1,000+ Excel Tutorials On Formulas, Macros, VBA, Pivot Tables, Dashboards, Power BI, Power Query, Power Pivot, Charts, Microsoft Office Suite + MORE!\u00a0<\/a> <\/div><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The Consolidate tool in Excel is located in the Data menu and combines values from multiple ranges into one new range. You would use this feature when you have a single text column on the left and the column has many duplicate values. First you need to select a blank cell outside your data set [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17513,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[6,41],"tags":[88,83],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1506"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=1506"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1506\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17513"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=1506"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=1506"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=1506"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}