If you’re using Excel 2021, 2019, or even 2016, you’ve probably run into this problem: You’ve got multiple sheets with similar data, maybe monthly sales reports or departmental lists, and you want to combine them into a single table.
But you’re not on Microsoft 365, so the new VSTACK function isn’t available to you. Now what? Don’t worry. There’s a powerful alternative…Power Query
In this post & video I’ll walk you through how to combine data from different sheets into a single refreshable table using Power Query.
Note for Mac users: Power Query on Mac doesn’t currently support this particular setup, so this walkthrough is for Excel on Windows only.
Let’s say you have a workbook with data stored across three sheets. Each sheet contains a table of sales data.
Convert Each Sheet into a Table
Power Query works best with structured data. Go to each sheet, select your range, and press Ctrl + T to convert it into a Table. Then rename each table appropriately.
Create a Connection Query for Each Table
For each table:
- Select Data > From Table/Range
- This opens the Power Query Editor
- Click Close and Load on the Home Tab of the Query Editor Ribbon
- From the dropdown menu select Close & Load To…
- Select Only Create Connection
- Click OK
Think of a Connection Only Query as a pointer to a table of data stored in a worksheet. I selected this because the other option, Table would load a copy of the data into a new Table in the worksheet and there is already a copy of the data in the worksheet.
Combine the Tables into a Single Table
- Select Data > Get Data > Combine Queries > Append
- For each Table, select it and click Add
- Optionally rename the resulting Query
- Make any other required tweaks in the Power Query Editor
- Click Close & Load To and select new worksheet.
Refresh as Needed
Whenever you update data on the original sheets (e.g., adding new rows or fixing mistakes), simply right-click anywhere in the combined table and select Refresh
Download a copy of the file(s) used in this video: https://share.zight.com/yAuRdWGE