Combine Data from Multiple Sheets into One Sheet In Excel | Consolidate Tables into a Single Sheet

In this Microsoft Excel tutorial I explain how to combine data stored across multiple worksheets in one sheet or table. I use Power Query to achieve this.

Table of Contents:

00:00 - Introduction
00:14 - Combine sheets (tables) in the same workbook
00:21 - House data in an Excel Table
00:47 - Create a query that returns all tables
01:37 - Modify formats for some columns
02:11 - Load the query to Excel
02:32 - Modify the query so it does not include the consolidated table
03:30 - Add a new sheet and refresh the query
04:30 - Consolidate data in a separate workbook
------------------------

[brinh]

You just helped me combine 67 sheets into one lovely table. THANK YOU!!!

[zpepe]

Oh god thank you for this...Exactly what I wanted to do but had no chance of figuring out on my own!

[iainmacdonald]

This is brilliant! Thank you so much. You enabled me to do something (combines multiple sheets in multiple files) that I just hadn't ever done before. Not sure I 100% understand it now, but it works! Fantastic. Thank you, again.

[ybs]

I have tried to do this using VBA for 2 hours, and you solved my issues... Amazing!! Thanks a lot

[sivasubramaniambaskaran]

Thanks for the clear and helpful video! Your explanation of the formula really helped me solve my problem

[cerdito]

Great tutorial. I had struggled with others, and this was the one that got it done! Thanks

[dineshshehant]

when I go to the blank query and when I enter the format =ex or =excel don't get the drop-down as u got to select the current workbook..any fix

[thinktank]

This works pretty well with one question: I end up with duplicated columns.

Ex.
Sheet 1 with a table columns
First
Last
ID

Sheet 2
First
Last
ID

When I do the query, my output creates TWO Last columns, with null data in some of them, where the source tables each only have one Last column containing data.

[AnkitKumar-vtmi]

PLease If you can make a video on how to insert table in all worksheet in one go it will be very useful. thanks or if someone from the comments section can can tell me i will be very thankful

[demdex]

Why can't I like this video twice ★★★★★

[mattkiggans]

You win! Saved me a couple hours today 😊

[IvanCortinas_ES]

Thank you for this video Chester. A great tutorial!!!!

[shyjjj]

This has been super helpful! Thank you so much!!!!

[morbirfil]

It's been of great help! Thank you!

[VanessaVasquez]

must save this video. thank u very much. i feel so smart :)

[Coriolanus]

Fantastic video, had compliments from the boss.

One thing though, supposing I add new columns to individual tables and then refresh the combined table and those new columns don't appear? That is the problem I am now having.

[jinglu]

How about adding a new column in the combined table to show the regions? Do I have to add it manually before I combine them? Is there any automate way to adding the region data into column?

[AmrouneBrahim-pv]

thank you SO MUCH this was extremly helpfull thank you

[bernardharper]

Thanks very much. Exactly what I was looking for !

[deepster]

How about if each excel sheet represent daily data, say stock prices? So each tab has same rows, say 100 stocks. And same 4 columns - open, high, low, close. Cells values are different.
What is the best way to handle this?