Consolidate Multiple Excel Sheets Using Power Query Append

Author: John Michaloudis - Posted on July 10, 2020

Home » Power BI in Excel » Power Query » Consolidate Multiple Excel Sheets Using Power Query Append

DOWNLOAD OUR FREE
101 BEST EXCEL TIPS & TRICKS E-BOOK!

Power Query is awesome!

You will see why after viewing this tutorial.

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

With Power Query, the answer is YES!

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.

Watch our free training video on How to Merge & Consolidate Multiple Excel Sheets with Power Query

Follow the step by step tutorial guide on power query multiple sheets with free downloadable Excel workbook to practice.

DOWNLOAD EXCEL WORKBOOK

STEP 1: Make sure that each worksheet´s data is in an Excel Table by clicking in the data and pressing CTRL+T

STEP 2: Click in each of the worksheets data that you want to consolidate and select:

Power Query > From Table

STEP 3: This will open up the Query Editor and all you have to do here is press Close & Load.

NB: Make sure to do Step 2 & 3 for each worksheet you want to consolidate

STEP 4: Select Power Query > Append

STEP 5: Choose the Three or more tables option

STEP 6: Add the tables to append from the Available Tables (from the left) to the Tables to Append (to the right) by selecting and pressing the Add button.

You can also organize the order that you want your consolidated table to appear by moving the Tables up or down

Press the OK button!

STEP 7:This will open up the Query Editor once again. Choose Close & Load.

STEP 8: This will open up a brand new worksheet which will consolidate all the worksheets into one big Table:

STEP 9: From this consolidate worksheet you can Insert a Pivot Table and do your analysis:

This is how you can combine tables and use Power Query consolidate multiple worksheets feature.

Power Query is an extremely useful tool in Excel. You can learn more about it here.

Further Learning:

👉 Click Here To Join Our FREE Excel Pivot Tables & Excel Dashboards Webinar That Will SAVE YOU HOURS At Work & INCREASE Your Excel SKILLS!

Facebook

Twitter

DOWNLOAD OUR FREE
101 BEST EXCEL TIPS & TRICKS E-BOOK!

About The Author

John Michaloudis

John Michaloudis is the Founder & Chief Inspirational Officer of MyExcelOnline!

You May Also Like

Leave a Comment Cancel reply

Hii

I Have connected 63 Worksheet in power query with Append Option but data does not load I am Awaited 3 Hourse.

please guide for a solution.

Satyam

Bryan says:

September 12, 2022 at 7:23 AM

Hi Satyam,

What Excel version are you using? Does your computer have enough resources?

Hi can i consolidate more than 50 sheets at a time?

Bryan says:

May 11, 2020 at 8:52 AM

Hi Pallavi,

Theoretically yes you should be able to. And you have better odds if you use the 64-bit version of Excel.

What happens when the 2 queries exceed the row limit? is there a workaround for this in Power query?

Bryan says:

December 17, 2019 at 6:08 AM

Hi Charlie,

Are you using Excel 64-bit or 32-bit? What is the error message you are getting?

Kind Regards,
Bryan

hello John Michaloudis,

could you tell me how to append more than 5 or 7 worksheets? due to i can not do it when i try to append 7 worksheets it shown only 2 or 4 worksheet.

I appreciate your answer.

Regards,

Sambo Tith

Bryan says:

September 30, 2019 at 3:22 PM

Hi Sambo,

Yes it should be possible to do that using the steps in this post. What is the error you are getting?

Kind Regards,
Bryan

Since opening the wksts in Power Query (for Close & Load) creates all these extra sheets (Sheet 5, etc), can we just delete those extra sheets? Or hide them? I’m not sure what the purpose of having those is, except that they’re connected to the Workbook Queries panel.

Bryan says:

July 12, 2019 at 12:23 PM

Hi Sandy,

If you do not need a sheet to be created, you can go to Close & Load To.. -> Only Create Connection. So that it is only your query gets stored. Keep on Excelling!

Kind Regards,
Bryan

Hello…thanks for the easy to follow steps! I am having one issue with an append query consisting of 4 tables. Each time the append query shows 1 error however when attempting to view the error I am unable to determine what the issue is. Under the applied steps the “kept errors” section states that the table is empty. There is a “reordered columns” section as well that has a row number column added in the first column – this table is empty as well. Any advice is appreciated!

Bryan says:

November 4, 2018 at 2:26 PM

Hi Jayne,

Could you send me a screenshot when the error is happening? So that we can better understand the problem.

Regards,
Bryan

Hi there,
Have a similar issue you may be able to assist on.
I have created a query pulled from 3 sheets in a folder (Master). In the same sheet as the query, i have created an additional tab of data with the same headers (New) which I want to combine with the query (Master).

The issue I am having appending these 2 together is an error which states ” query. Expression.Error: The key didnt match any rows in the table. Details: Key = Record Table = Table”

Any idea why this is happening?

John Michaloudis says:

July 13, 2017 at 1:06 AM

Hey Jason,

Make sure that the NEW data is in an Excel Table.

Also here is a solution from the Power BI community: http://community.powerbi.com/t5/Integrations-with-Files-and/Expression-Error-The-key-did-not-match-any-rows-in-the-table/m-p/11536#M1965

1. Select Edit Queries from the menu to open the Query Editor
2. Use the left hand menu to select one of the queries having the issue (will have the triangular warning sign)
3. On the applied steps menu at the right select the source step.
4. In the top menu bar, select Refresh Preview.

I hope this helps.

Thanks,
John

Hi
I have just followed your post to append a number of tables into one. I loaded my 10 tables, but when I came to append, I didn’t have the option to append more than one table – is this a version issue?
Thank you, Sarah

John Michaloudis says:

March 24, 2017 at 7:24 PM

Hey Sarah,

Yes this may be a version issue. What version are you on?

Thanks,
John

Hi John,

I have my own files and want to append all my data. Unfortunately Power Query doesn’t append all of my Workbook Queries, just a few. Everything is the same Data Typ (I triede also with different Types e.g. Date, Text etc.,.

In the new Workbook query it should have 15987 rows but is only has 6575. Do you know what the problem could be?

Thank you.
Peter

John Michaloudis says:

March 7, 2017 at 1:17 PM

Hey Peter,

Please send us your workbook so we can have a look at your Query Editor at [email protected]

Thanks,
John

Hi John, Is there a way to consolidate multiple excel sheets using power query append if each worksheet has 100,000 lines on it? And then prepare a pivot table? Thank you. Kelly

John Michaloudis says:

December 7, 2016 at 4:17 PM

Hey Kelly,

Of course you can! Dont let the number of rows scare you.

You can always email me a sample worksheet if you like us to have a look at this and implement it for you.

Cheers,
John

There is another way to do this job: use add “query from folder” option. Main Functions used are:Folder.Files,Table.AddColumn,Excel.Workbook,Table.ExpandTableColumn

John Michaloudis says:

April 27, 2016 at 10:05 PM

Hello Wanght,

Can you please explain where we can find this option to help our blog readers?

Thanks for your help,

John
wanght says:

April 30, 2016 at 6:31 AM

hi,we can try this: data-add new query-from files-from folders. that’s to say, we can build a query from a folder, although there is only one file with multiple worksheets in the folder. this method has a disadvantage that we have to delete the title for each worksheets manually.sorry for my poor English.
John Michaloudis says:

May 6, 2016 at 1:19 AM

Hello Wang,

Yes this is another great way of doing this consolidation and you have given me an idea to record a blog post to describe just this process!

Thanks 🙂

John
Wanght says:

May 13, 2016 at 4:21 PM

hi,John Michaloudis, i’m glad my suggestion proved to be valid. and i’m looking forward to reading your new post:)