{"id":3036,"date":"2016-05-12T21:29:58","date_gmt":"2016-05-12T19:29:58","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=3036"},"modified":"2024-02-06T16:03:07","modified_gmt":"2024-02-06T15:03:07","slug":"consolidate-multiple-excel-workbooks-using-power-query","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/consolidate-multiple-excel-workbooks-using-power-query\/","title":{"rendered":"Consolidate Multiple Excel Workbooks Using Power Query"},"content":{"rendered":"<p>One of the most sought after a query from the millions of Excel users around the world is:<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/consolidate-multiple-excel-workbooks-using-power-query\/#How_do_I_consolidate_multiple_Excel_workbooks_in_Power_Query_into_one\" title=\" How do I consolidate multiple Excel workbooks in Power Query into one?\"> How do I consolidate multiple Excel workbooks in Power Query into one?<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/consolidate-multiple-excel-workbooks-using-power-query\/#STEP_16_NOW_FOR_THE_COOL_PART\" title=\"STEP 16: NOW FOR THE COOL PART!!!!\">STEP 16: NOW FOR THE COOL PART!!!!<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/consolidate-multiple-excel-workbooks-using-power-query\/#THAT_IS_POWER\" title=\"THAT IS POWER!!!!\">THAT IS POWER!!!!<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"How_do_I_consolidate_multiple_Excel_workbooks_in_Power_Query_into_one\"><\/span><em> How do I consolidate multiple Excel workbooks in Power Query into one?<\/em><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>There are a couple of ways you can do this, using VBA or complex <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formulas\" data-wpil-keyword-link=\"linked\">formulas<\/a> but the learning curve is steep and out of reach for most Excel users.<\/p>\n<p>Luckily with Power Query Excel Workbook, this consolidation task can be done in a couple of minutes! \u00a0That&#8217;s right, only a couple of minutes.<\/p>\n\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/7D2KxCSvYEQ?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\">?<\/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><\/p>\n<p><strong><a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/Consolidate-Multiple-Excel-Workbooks.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<\/span><span class=\"postTitle px-3\">Consolidate-Multiple-Excel-Workbooks.xlsx<\/span><\/a><\/strong><\/p>\n<p>I show you how below&#8230;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span> <strong>Create a New Folder<\/strong>\u00a0on your Desktop or any directory and name it to whatever you like e.g. <em>2016 Sales<\/em><\/p>\n<p><strong>Move an\u00a0Excel Workbook in this Folder<\/strong> that contains your Sales data e.g. <em>January 2016.xlsx<\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/move-jan.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3060\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/move-jan.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"315\" height=\"131\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/move-jan.jpg 315w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/move-jan-300x125.jpg 300w\" sizes=\"(max-width: 315px) 100vw, 315px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span> Open a <strong>NEW Excel Workbook<\/strong> and go to <em><strong>Power Query &gt; From File &gt; From Folder<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/from-folder.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3038\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/from-folder.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"927\" height=\"404\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/from-folder.jpg 927w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/from-folder-300x131.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/from-folder-768x335.jpg 768w\" sizes=\"(max-width: 927px) 100vw, 927px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong><\/span> From the Folder dialogue box, <strong>click the Browse button<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/folder.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3039\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/folder.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"695\" height=\"245\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/folder.jpg 695w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/folder-300x106.jpg 300w\" sizes=\"(max-width: 695px) 100vw, 695px\" \/><\/a><\/p>\n<p>This will bring up the <em>Browse for Folder<\/em> dialogue box and you need to<strong> select the\u00a0folder you created in Step 1<\/strong> and press <strong>OK.<\/strong><\/p>\n<p><em>This is how you can use Power Query load multiple files from folder feature.<\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/browse-dor-folder.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone wp-image-3040 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/browse-dor-folder.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"320\" height=\"378\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/browse-dor-folder.jpg 320w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/browse-dor-folder-254x300.jpg 254w\" sizes=\"(max-width: 320px) 100vw, 320px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4:<\/strong><\/span> This will open up the <strong>Query Editor<\/strong>.<\/p>\n<p>From in here you need to<strong> select the first 2 columns<\/strong> (hold down the CTRL key to select) and<strong> Right Click on the column heading<\/strong> and choose <strong>Remove Other Columns<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/remove-other-columns.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3041\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/remove-other-columns.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"413\" height=\"135\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/remove-other-columns.jpg 413w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/remove-other-columns-300x98.jpg 300w\" sizes=\"(max-width: 413px) 100vw, 413px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5:<\/strong><\/span> You need to go to <em><strong>Add Column &gt; Add Custom Column<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/add-custom-column.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3042\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/add-custom-column.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"379\" height=\"122\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/add-custom-column.jpg 379w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/add-custom-column-300x97.jpg 300w\" sizes=\"(max-width: 379px) 100vw, 379px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 6:<\/strong> <\/span>This will bring up the <em>Add Custom Column<\/em> dialogue box.<\/p>\n<p>In here you need to <strong>name the new column<\/strong>\u00a0E.G. <em>Import<\/em>, and within the Custom Column <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"Formula\" data-wpil-keyword-link=\"linked\">Formula<\/a> you need to<strong> enter the following formula<\/strong>:<\/p>\n<h2>= Excel.Workbook([Content])<\/h2>\n<p>This will import the workbooks from within the Folder that you selected in Step 3<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/add-custom-column2.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3043\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/add-custom-column2.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"695\" height=\"399\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/add-custom-column2.jpg 695w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/add-custom-column2-300x172.jpg 300w\" sizes=\"(max-width: 695px) 100vw, 695px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 7:<\/strong><\/span> You now have a new column called <em>Import<\/em>.<\/p>\n<p><strong>Click on the Expand Filter<\/strong> and select the<strong> Data<\/strong> box only and press <strong>OK<\/strong>. \u00a0This will import the\u00a0workbook from the folder<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/import-expand.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3044\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/import-expand.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"367\" height=\"296\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/import-expand.jpg 367w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/import-expand-300x242.jpg 300w\" sizes=\"(max-width: 367px) 100vw, 367px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 8:<\/strong> <\/span><strong>Click on the Expand Filter<\/strong> from the<em> Import Data<\/em> column and select <strong>OK<\/strong>. \u00a0This imports all the columns&#8217; data from\u00a0within the workbook<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/expand-import-2.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3045\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/expand-import-2.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"397\" height=\"300\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/expand-import-2.jpg 397w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/expand-import-2-300x227.jpg 300w\" sizes=\"(max-width: 397px) 100vw, 397px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/data-import.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3046\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/data-import.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"959\" height=\"369\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/data-import.jpg 959w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/data-import-300x115.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/data-import-768x296.jpg 768w\" sizes=\"(max-width: 959px) 100vw, 959px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 9:<\/strong><\/span> Now it is time to transform the data by making some cosmetic changes!<\/p>\n<p><strong>Remove the Content column<\/strong> by <strong>Right-Clicking<\/strong> and choosing <strong>Remove<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/remove-binary.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3049\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/remove-binary.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"289\" height=\"126\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 10:<\/strong> <\/span>Select the<em> Import.Data.Column1<\/em> and <strong>filter out the CUSTOMER<\/strong> heading and press <strong>OK<\/strong>. \u00a0This will also remove the other column&#8217;s headers<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/remove-column-headings.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3050\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/remove-column-headings.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"341\" height=\"445\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/remove-column-headings.jpg 341w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/remove-column-headings-230x300.jpg 230w\" sizes=\"(max-width: 341px) 100vw, 341px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 11:<\/strong><\/span> <strong>Select the <em>Date<\/em><\/strong> <strong>column<\/strong> and go to <em><strong>Transform &gt; Data Type &gt; Date<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/data-type-date.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3051\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/data-type-date.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"378\" height=\"329\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/data-type-date.jpg 378w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/data-type-date-300x261.jpg 300w\" sizes=\"(max-width: 378px) 100vw, 378px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 12:<\/strong><\/span> <strong>Select the <em>Sales<\/em> column<\/strong> and go to\u00a0<em><strong>Transform &gt; Data Type &gt;\u00a0Currency<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/data-type-currency.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3052\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/data-type-currency.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"517\" height=\"328\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/data-type-currency.jpg 517w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/data-type-currency-300x190.jpg 300w\" sizes=\"(max-width: 517px) 100vw, 517px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 13:<\/strong><\/span> <strong>Rename the column headings<\/strong> to whatever you like by <strong>double clicking on the column header<\/strong>, renaming and pressing <strong>OK<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/column-rename.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3053\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/column-rename.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"449\" height=\"67\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/column-rename.jpg 449w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/column-rename-300x45.jpg 300w\" sizes=\"(max-width: 449px) 100vw, 449px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 14:<\/strong> <\/span>Go to <em><strong>File &gt; Close &amp; Load.<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/close-load-1.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3054\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/close-load-1.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"189\" height=\"155\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>This will put the data into a new worksheet within your workbook<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/new-sheet-1.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3056\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/new-sheet-1.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"561\" height=\"370\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/new-sheet-1.jpg 561w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/new-sheet-1-300x198.jpg 300w\" sizes=\"(max-width: 561px) 100vw, 561px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 15:<\/strong><\/span> You can now<strong> Insert a <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener\" title=\"Pivot Table\" data-wpil-keyword-link=\"linked\">Pivot Table<\/a><\/strong> to do your analysis by going to<em><strong> Insert &gt; Pivot Table\u00a0&gt; New\/Existing Worksheet<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/insert-pivot-table.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3057\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/insert-pivot-table.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"203\" height=\"126\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Put the Months in the ROWS and the Sales $ in the VALUES area:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/pivot-table1.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3058\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/pivot-table1.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"513\" height=\"428\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/pivot-table1.jpg 513w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/pivot-table1-300x250.jpg 300w\" sizes=\"(max-width: 513px) 100vw, 513px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"STEP_16_NOW_FOR_THE_COOL_PART\"><\/span><span style=\"color: #ff0000;\"><strong>STEP 16:<\/strong><\/span> NOW FOR THE COOL PART!!!!<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>You can <strong>move similar workbooks into the Folder we<\/strong> <strong>created in Step 1,<\/strong> say for subsequent months eg. <em>February 2016.xlsx, March 2016.xlsx etc<\/em><\/p>\n<p><em>NB:\u00a0The\u00a0Excel Workbooks have to have the same format and number of columns as in the workbook we imported in Step 1<\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/move-subsequent-months.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3059\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/move-subsequent-months.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"505\" height=\"140\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/move-subsequent-months.jpg 505w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/move-subsequent-months-300x83.jpg 300w\" sizes=\"(max-width: 505px) 100vw, 505px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 17:<\/strong> <\/span>In\u00a0your Excel workbook, <strong>click on the imported data<\/strong> and this will open up the <strong>Workbook Queries pane<\/strong> (If this does not open, go to <em>Power Query &gt; Show Pane<\/em>)<\/p>\n<p><strong>Click the Refresh button<\/strong> (or you can go to <em>Table Tools &gt; Query &gt; Refresh<\/em>)<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/refresh-pane.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3061\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/refresh-pane.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"705\" height=\"286\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/refresh-pane.jpg 705w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/refresh-pane-300x122.jpg 300w\" sizes=\"(max-width: 705px) 100vw, 705px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 18:<\/strong><\/span> This will <strong>import\u00a0the<em>\u00a0February 2016.xlsx<\/em> and <em>March 2016.xlsx<\/em> data<\/strong> into the Excel workbook and<strong> append it to January&#8217;s data<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/feb-data.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3062\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/feb-data.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"557\" height=\"306\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/feb-data.jpg 557w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/feb-data-300x165.jpg 300w\" sizes=\"(max-width: 557px) 100vw, 557px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 19:<\/strong> <\/span>Now you can<strong> Refresh the Pivot Table<\/strong> and the new imported data will be reflected<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/updated-pivot.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Consolidate Multiple Excel Workbooks Using Power Query\"  class=\"alignnone size-full wp-image-3063\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/updated-pivot.jpg\" alt=\"Consolidate Multiple Excel Workbooks Using Power Query\" width=\"206\" height=\"120\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>Next month all you have to do is drop in the new month&#8217;s workbook into the 2016 Sales Folder and Refresh the Query &amp; the Pivot Table to see the results!<\/p>\n<h2><span class=\"ez-toc-section\" id=\"THAT_IS_POWER\"><\/span>THAT IS POWER!!!!<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>In the article, you have learned how to use <strong>Power Query combine multiple Excel files<\/strong> feature and its advantages. To learn more about Power Query, <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/50-things-you-can-do-with-excel-power-query\/\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>Click here<\/strong><\/a>.<\/p>\n<p><strong>Further Learning:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/remove-duplicates-using-power-query\/\" target=\"_blank\" rel=\"noopener\">Remove Duplicates Using Power Query<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/replicating-excels-right-function-with-m-in-power-query\/\" target=\"_blank\" rel=\"noopener\">Replicating Excel&#8217;s RIGHT Function with M in Power Query<\/a><\/li>\n<li><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/add-a-row-index-to-a-loaded-query-using-power-query\/\" target=\"_blank\" rel=\"noopener\">Add a Row Index to a Loaded Query Using Power Query<\/a><\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100.html\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-2964 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/728x90.gif\" alt=\"PIVOT BANNER\" width=\"728\" height=\"90\" title=\"\"><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of the most sought after a query from the millions of Excel users around the world is: How do I consolidate multiple Excel workbooks in Power Query into one? There are a couple of ways you can do this, using VBA or complex formulas but the learning curve is steep and out of reach [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17120,"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":[120,121],"tags":[117],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3036"}],"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=3036"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3036\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17120"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=3036"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=3036"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=3036"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}