Microsoft released an extension for Excel called Power Query. It makes some tasks easier, like combining Excel worksheets in a single one. This is the subject I am going to talk about, but in the future I will cover more about Power Query.
Let’s assume we have a worksheet containing foods.
And we also have another worksheet containing electronics.
Both worksheets have the same columns and we want to combine them into a single worksheet to display all data. With Power Query this becomes an easy task. You just need to follow these steps.
Creating connection from tables
Click inside a worksheet used range and from Power Query ribbon choose to get data from table.
Get data from table
You are asked to choose the range you want to use for data. By default used range is selected.
Query editor window is opened. Just give a name to your query (I named it “Table_Food”) and choose “Close & Load To” option from “Close & Load” menu. From the new window choose “Only Create Connection” as we already have data into table.
Repeat this operation for the other worksheet, of course giving a different name for the connection (like “Table_Electronics”).
Create the connection to combine the data
To create a connection to show combined data, choose “Show Pane” from Power Query ribbon.
On the “Workbook Queries” pane, right click on any data connection and choose “Append”.
Choose Append Option
On the next screen choose source and destination for append query.
Choose source and destination
Give a name for your new query (like “Combined Data”) and again choose “Close & Load To” option from “Close & Load” menu, of course only creating connection.
Now, as you have created all connections, go to “Data” ribbon and choose to get external data from existing connection.
Get data from existing connections
Choose your connection which displays data from both worksheets and click “Open” button.
On the next screen is really up to you what you choose. You can choose to display data into an existing worksheet or you can tell Excel to create a new for you to place the data. You can consider now the task is done. 🙂
There is an interesting part with Power Query. If, for example, you add new data into one of the worksheets you used as a source and refresh data connections, Power Query knows and put new data into combined connection.