Excel Data Transformation: Combine 4 Sheets in Excel using Power Query - Episode 2178

Microsoft Excel Tutorial: How to combine four Excel sheets where each has a different number of rows?

Welcome to another episode of the MrExcel Podcast, where we help you learn Excel in a fun and easy way. In today's episode, we will be discussing how to merge four worksheets in Excel using Power Query. This question comes from Doug, who is struggling with using formulas to combine his sales data from four different regions. As the number of records changes monthly, it becomes a nightmare to manage. But fear not, Doug, we have a solution for you using Power Query.

First, let's make sure you have the necessary tools. If you have Excel 2010 or 2013, you can download Power Query for free from Microsoft. If you have Excel 2016 or Office 365, Power Query is already built-in. Now, let's take a look at the four reports that Doug has - Central Region, East Region, South Region, and West Region. Each report has a different number of records, which will change every time the report is run. We have also made some assumptions, such as there not being a column called Central and purposely messing up the data in the South Region to show the power of Power Query.

To start, we will format each worksheet as a table using the shortcut Ctrl+T. This will make it easier for Power Query to work with the data. We will also rename each table to match its respective region. Now, instead of using the traditional Close & Load option, we will choose Close & Load To... and select Only Create Connection. This will create a connection to the data without actually loading it into the worksheet. We will repeat this process for all four tables.

Next, we will insert a new blank worksheet and use the Get Data option to Combine Queries. We will choose to append two queries from this workbook and select Three or more tables. We will then add the four tables and click OK. This will combine all the data from the four tables into one superset. We can then Close & Load this data to a new worksheet. And the best part? If we add more data to any of the four tables, we can simply click Refresh in the query and the new data will be added.

Power Query is an amazing feature from Microsoft that makes data manipulation and analysis so much easier. It may take a little longer to set up initially, but the time saved in the long run is well worth it. We want to thank Doug for sending in this question and we hope this solution helps you and others facing a similar issue. And don't forget to check out the book "M is for (DATA) MONKEY" by Ken Puls and Miguel Escobar for more in-depth knowledge on Power Query. Thanks for tuning in and we'll see you next time for another episode of the MrExcel Podcast.

#excel
#microsoft
#microsoftexcel
#exceltutorial
#walkthrough
#evergreen
#excelconsolidate
#powerquery

This video answers these common search terms:
how to combine tables power query excel
how to combine sheets in excel by power query
how to merge two tables in excel using power query
can you merge a table with a query excel powerquery
how to merge sheets into one sheet in excel
how to merge sheets in excel into one sheet
how to merge sheets excel
how to merge to sheets in excel
how to merge 2 excel sheets to one
how to merge 3 excel sheets into one
how to merge several sheets into one in excel
how to quickly merge sheets in excel
how to merge all excel sheets into one sheet
how to merge two excel sheets into one sheet
how to merge different excel sheets into one
how do i merge 2 excel spreadsheets
how do i merge two excel spreadsheets into one sheet
how combine sheets in excel
how to combine sheets in excel by power query
how to combine sheets into one sheet in excel
how to combine sheets from multiple excel
how to combine multiple sheets in one excel into one sheet
how to combine several excel sheets into 1 sheet
how to combine data in different excel sheets

Table of Contents:
(00:00) Combine Four Excel Sheets
(00:10) The Problem with Formulas
(00:21) Using Power Query
(01:31) Setting Up the Tables
(03:53) Combining the Tables
(05:46) Refreshing the Data
(06:44) Clicking Like really helps the algorithm

Use Power Query
Format each worksheet as a Table with Ctrl+T
Rename the tables
For each table, new query From Table. Add a custom column for Region
Instead of Close & Load, choose Close & Load to… Only Create a Connection
Use New Query, Combine Query, Append. 3 or More Tables. Choose the Tables and Add
Close and Load and the data appears on a new worksheet
For the one table with extra column: the data shows up for only that sheet's records
For the one table where the columns were in the wrong order: Power Query worked correctly!
Easy to Refresh later

[Breewilly]

You just helped I had to use this at work today!!!

Bless you sir! Bless you!!!!

[GeertDelmulle]

So, if you append queries from tables PQ understands the concept of fields/columns and matches the right ones in the append.
That is a super trick right there. Thanks Bill!

[krishnavani]

your video is very very useful for me.

[douglaszulu]

Thanks alot Bill. I have just used it on my reports and updating for this month was awesome....

[desuraiie]

Thank you, Mr. Excel! Love this vid! <3

[sasavienne]

Powerful and so useful. Thanks. 👍 🌟 🌟 🌟 🌟

[garylillich]

Thank you very much.
What procedure for the situation where some rows have been added or deleted to Version 2?

[lakshmiprasanna]

Can we use the same method for 100 worksheets in the same excel?

[keery]

I'm wanting to do this with an action tracker I use for different projects, if I combine the sheets will I lose conditional formatting I have set up on the individual sheets. Once the sheets have merged into a master sheet can I filter by status, date, priority etc?

[mustafazakaria]

Thanks can we do this without converting the range into the table. My header has some formulas due to which I dont want to convert it to the table?

[adnangori]

Dear Mr.Excel,
Hope thanks for all
In my excel 2013 I don't have PQ it's not easy to update in my company...
Could share formula for this problem solving with indirect

[reyanngonzales]

How should I proceed, If I need to add more table (worksheet) to this?

[wayneseymour]

Why is excel power query adding a unwanted column to the appended table when I refresh the data ?

[surajmarathe]

How can resize of Filter box in excel without mouse use

[joem]

Bill you are amazing! How did you rearrange those two columns. You said in the video to the cut the first one and then you simply said paste it. But there is more to it than that. If you just paste you will overwrite the second column.

[rrrprogram]

Nice one.... Is this not easy one... That ypu can first append all the tables and then add custom colum?? It will reduce the working step

[itsreallysimple]

The "From Table/Range" option is not available in neither the Mac Excel version nor the online Excel version. So frustrating because this would save me a hella lot of time

[andytanjuakio]

Is there a way to make this a macro that you can run on every sheet? So on every sheet you run the macro and it will format the data as a table and load it as a connection.

[garystahl]

Doug, I have never used you tube to talk with someone but here it goes. I have two spreadsheet of numbers. Each spreadsheet has a 11 digit number and quantity. How do I combine the quantities by referencing the 11 digit number?

[ismailismaili]

Wow so powerful thank u Mr. Bill if i have a question can i comment it?,