{"id":2874,"date":"2020-07-10T19:32:30","date_gmt":"2020-07-10T17:32:30","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=2874"},"modified":"2024-03-22T19:02:40","modified_gmt":"2024-03-22T18:02:40","slug":"consolidate-multiple-excel-sheets-using-power-query","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/consolidate-multiple-excel-sheets-using-power-query\/","title":{"rendered":"Consolidate Multiple Excel Sheets Using Power Query Append"},"content":{"rendered":"

Power Query<\/a> is awesome!<\/p>\n

You will see why after viewing this tutorial.<\/p>\n

I get lots of queries from my blog readers asking me if there is a way to easily combine tables from multiple sheets in the same workbook with Power Query<\/h3>\n

With Power Query, the answer is YES!<\/p>\n

If you have multiple Excel worksheets that are in the same format and their underlying differences are their values and dates (e.g. January Sales List, February Sales List, March Sales-List, etc), then we can easily use Power Query combine Multiple Sheets feature.<\/p>\n

\n

Follow the step by step tutorial guide on power query multiple sheets with free downloadable Excel workbook to practice.<\/p>\n

Download excel workbook<\/span>Append-Multiple-Sheetsv2.xlsx<\/span><\/a><\/span><\/strong><\/p>\n

STEP 1:\u00a0<\/strong>Make sure that each worksheet\u00b4s data is in an Excel Table<\/strong> by clicking in the data and pressing CTRL+T<\/strong><\/span><\/span><\/p>\n

\"Consolidate<\/a><\/p>\n

 <\/p>\n

STEP 2:\u00a0<\/strong>Click in each of the worksheets data that you want to consolidate and select:<\/span><\/span><\/p>\n

Power Query > From Table<\/strong><\/p>\n

\"Consolidate<\/a><\/p>\n

 <\/p>\n

STEP 3:\u00a0<\/strong>This will open up the Query Editor<\/strong> and all you have to do here is press Close & Load<\/strong>. \u00a0<\/span><\/span><\/p>\n

NB: Make sure to do Step 2 & 3 for each worksheet you want to consolidate<\/strong><\/em><\/p>\n

\"Consolidate<\/a><\/p>\n

 <\/p>\n

STEP 4:\u00a0<\/strong>Select Power Query > Append\u00a0<\/strong><\/span><\/span><\/p>\n

\"Consolidate<\/a><\/p>\n

 <\/p>\n

STEP 5:\u00a0<\/strong>Choose the Three or more tables<\/strong> option<\/span><\/span><\/p>\n

\"Consolidate<\/a><\/p>\n

 <\/p>\n

STEP 6:\u00a0<\/strong>Add the tables to append from the Available Tables<\/strong> (from the left) to the Tables to Append<\/strong> (to the right) by selecting and pressing the Add<\/strong> button.<\/span><\/span><\/p>\n

You can also organize the order that you want your consolidated table to appear by moving the Tables up or down<\/p>\n

Press the OK button!<\/strong><\/p>\n

\"Consolidate<\/a><\/p>\n

 <\/p>\n

STEP 7:<\/strong><\/span>This will open up the Query Editor<\/strong> once again. \u00a0Choose Close & Load<\/strong>.<\/p>\n

\"Consolidate<\/a><\/p>\n

 <\/p>\n

STEP 8:<\/strong><\/span> This will open up a brand new worksheet\u00a0which will consolidate\u00a0all the worksheets into one big Table:<\/p>\n

\"Consolidate<\/a><\/p>\n

STEP 9:<\/strong><\/span> From this consolidate worksheet you can Insert a Pivot Table<\/a><\/strong> and do your analysis:<\/p>\n

\"Consolidate<\/a><\/p>\n

This is how you can combine tables and use Power Query<\/a> consolidate multiple worksheets feature.<\/p>\n

Power Query is an extremely useful tool in Excel<\/a>. You can learn more about it here.<\/a><\/strong><\/em><\/p>\n<\/div>\n

Further Learning:<\/strong><\/p>\n