Consolidating excel worksheets
For this technique to work best, it’s better to have names for your Excel Tables (work without it too, but it’s easier to use when the tables are named).I have given the tables the following names: The above steps would combine the data from all the worksheets into one single table.But what if your tables are not named consistently.What if all the table names are random and have nothing in common.So I decided to write this tutorial and show the exact steps to combine multiple sheets into one single table using Power Query.Below a video where I show how to combine data from multiple sheets/tables using Power Query: Below are written instructions on how to combine multiple sheets (in case you prefer written text over video).wiki How's Content Management Team carefully monitors the work from our editorial staff to ensure that each article meets our high standards.The wiki How Tech Team also followed the article's instructions and validated that they work.
Note: if you don't check Top row and Left column, Excel sums all cells that have the same position.
Apart from the fact that Power Query makes this entire process of combining data from different sheets (or even the same sheet) quite easy, another benefit of using it that it makes it dynamic.
If you add more records to any of the tables and refresh the Power Query, it will automatically give you the combined data.
Suppose you have four different sheets – East, West, North, and South.
Each of these worksheets has the data in an Excel Table, and the structure of the table is consistent (i.e., the headers are same). This kind of data is extremely easy to combine using Power Query (which works really well with data in Excel Table).