Good day,
This week, let's combine datasets from different sheets into one (the easy way) - check the Tiny Tip below.
(New Youtube tutorials are currently every other week.)
Last Weeks Video: Numbered Text in Excel
[Tiny Tip] Combine Multiple Worksheets into One Automatically in Excel
Have you ever needed to combine data from several worksheets into one master list? Instead of copying and pasting everything together, Excel's VSTACK function can do it with a single formula!
(Even better, when combined with Excel Tables, your master list can automatically update as you add new data.)
Step 1: Prepare Your Data
Let's say you track sales on two worksheets, January and February.
January Worksheet
Product | Sales |
|---|---|
Apples | 150 |
Bananas | 200 |
February Worksheet
Product | Sales |
|---|---|
Oranges | 175 |
Pears | 250 |
On each worksheet:
Select your data and press Ctrl + T to turn it into an Excel Table. Make sure My table has headers is checked.
Go to Table Design > Table Name.
Name the January table JanSales and the February table FebSales.
Step 2: Combine the Worksheets
Create a new worksheet called Master.
In cell A1, enter this formula:
=VSTACK(JanSales[#Headers],JanSales,FebSales)
That's it! Excel automatically combines both tables and headers into one list:
Product | Sales |
|---|---|
Apples | 150 |
Bananas | 200 |
Oranges | 175 |
Pears | 250 |
Step 3: Watch It Update Automatically!
Now, go back to either worksheet and add another row of sales data to its Table.
Return to the Master worksheet, and you'll see that the new data has been added automatically!
Why this works: VSTACK combines the rows from multiple ranges or Tables into one continuous list. Since we're using Excel Tables, the formula automatically includes new rows added to those Tables.
Bonus Tip: Combine Even More Worksheets
You can combine more worksheets by adding additional Tables to the formula. For example, if you create a March table named MarSales, use:
=VSTACK(JanSales[#Headers],JanSales,FebSales,MarSales)
Just make sure the Tables have the same columns in the same order.
Note: VSTACK is available in Excel for Microsoft 365 and Excel 2024. Enter the formula in a normal worksheet cell with enough empty space below it for the combined results.
Automate Excel
Fix or add features to your macros with the click of a button using our new tool.
Learn
Cheers and see you next week!
Don