ExcelInsider Team
Working with data spread across multiple sheets in Excel is a common scenario, especially when managing reports, tracking records, or compiling information from various sources. Combining this data into a single consolidated sheet helps simplify analysis and reporting. Excel offers several effective ways to merge data from multiple sheets ranging from straightforward manual methods to advanced automation techniques.
In this article, you’ll learn the best methods to combine data from multiple sheets in Excel. We’ll explore built-in tools, advanced formulas, and Power Query to help you pick the right solution based on your needs.
Steps to combine data from multiple sheets in Excel:
➤ Format the data in Sheet1 and Sheet2 as tables (Ctrl + T) and they will be automatically named as Table1 and Table2.
➤ In a summary sheet, enter:
=VSTACK(Table1, Table2)
➤ Press Enter. Excel automatically aligns columns and stacks both tables.
Combine Identical Sheets Vertically with VSTACK and FILTER Functions
When your data across sheets follows the same column structure, the VSTACK function can quickly stack them vertically. To avoid messy blank rows in the output, you can enhance the formula with FILTER function.
Steps:
➤ Create two sheets in your workbook called Sheet1 and Sheet2.
➤ In your output sheet such as VSTACK, select a cell where you want the combined list to begin (e.g., A2).
➤ Use this formula:
=LET(data, VSTACK(Sheet1!A2:C4, Sheet2!A2:C4), FILTER(data, CHOOSECOLS(data,1)<>””))
Here, this formula stacks data from Sheet1 and Sheet2 vertically and removes blank rows based on the first column. You can customize it by changing sheet names, cell ranges, or column filters as needed.
➤ Press Enter to generate the consolidated dataset and the formula will spill automatically.
You now have a merged list of all entries from both regions in one dynamic table.
Stack Excel Tables from Multiple Sheets Using VSTACK Function Only
If your datasets are formatted as Excel Tables (e.g., Table1 and Table2), you can stack them quickly without referencing ranges or worrying about blank rows. This approach is highly dynamic and ideal for ongoing reports.
Steps:
➤ Format the data in Sheet1 and Sheet2 as tables (Ctrl + T) >> Check your headers and click OK. Now, they will be automatically named as Table1 and Table2.
➤ In a summary sheet, enter:
=VSTACK(Table1, Table2)
➤ Press Enter. Excel automatically aligns columns and stacks both tables.
This method creates a fully dynamic master table combining both regions. It updates automatically as new rows are created.
Consolidate Values Using Excel’s Built-in Consolidate Feature
Excel’s Consolidate tool combines only numeric values across sheets based on matching row and column labels like IDs. It won’t show text fields which is ideal for totals like sales or counts.
Steps:
➤ Go to a new sheet, select the top-left cell where results should appear.
➤ Go to the Data tab >> Click Consolidate under Data Tools.
➤ Choose a function like Sum or Average.
➤ Click Add and select ranges from each sheet using the small arrow (e.g., Sheet1!A2:C4, Sheet2!A2:C4).
➤ Check Top row and Left column labels if headers exist.
➤ Click OK.
You’ll get a new table that aggregates values from all selected sheets.
Note:
Your data will appear unformatted. You need to format it manually.
Automatically Combine Data with Power Query
When dealing with large workbooks or frequently updated data, Power Query is the most efficient and scalable method to combine sheets dynamically.
Steps:
➤ Go to Data tab >> New Query under Get & Transform group >> Select From File and From Excel Workbook.
➤ Select your current workbook from your saved location >> Click Import.
➤ Check the box for Select multiple items >> Select Sheet1 and Sheet2 >> Click on Transform Data to load it into Power Query Editor.
➤ Use Append Queries as New under Combine drop-down from the Home tab to combine them into one.
➤ Select Two tables or the other option if you have more tables and click OK.
➤ Use Close & Load under the Home tab to push the combined table into Excel.
Now all sheets are combined dynamically, and new updates reflect instantly with just a refresh.
Frequently Asked Questions
Can I combine data from sheets with different column structures?
Not directly with the VSTACK function or Consolidate tool. Use Power Query, which lets you align and rename columns as needed before merging, even if sheet structures aren’t identical.
Will formulas like VSTACK update when new data is added?
Yes, if you’re using Excel Tables. VSTACK automatically expands when tables grow, making it ideal for dynamic and auto-updating reports across multiple sheets.
Why is my Consolidate output missing text columns like Product?
The Consolidate tool only works with numeric values. It ignores text columns. If you want to keep both text and numbers, switch to Power Query.
What’s the best method for large or frequently updated files?
Power Query is the most scalable. It lets you combine, transform, and refresh data from multiple sheets or even files with just one click, perfect for ongoing reports.
Wrapping Up
In this tutorial, we learned multiple ways to combine data from multiple sheets in Excel, ranging from simple stacking with formulas like VSTACK, to built-in tools like Consolidate, and finally, Power Query for automation. Each method is suited based on your needs and Excel version. Feel free to download the practice file and share your feedback.
