To combine data from multiple sheets into one master list in Excel, there are three options: Power Query for consolidation you will repeat every month, =FILTER(VSTACK(East!A2:C100, West!A2:C100, Central!A2:C100), VSTACK(East!A2:A100, West!A2:A100, Central!A2:A100)<>"") for a one-formula answer on Microsoft 365 or Excel 2024, and manual copy-paste with an added source column for a one-off. Adding a source column matters in all three, because once the rows are combined you cannot tell which sheet each came from.
Each department, branch, or month has its own sheet, but you need all the data in one place for reporting, analysis, or import into another system. Manually copying and pasting rows from 12 monthly tabs or 8 department sheets is tedious and error-prone. Here are three ways to consolidate multiple sheets into one master list.
The source sheets before consolidating
You have three sheets (“East”, “West”, and “Central”), each with the same column structure:
East
| A | B | C | |
|---|---|---|---|
| 1 | Date | Customer | Amount |
| 2 | 3/1 | Acme | $4,200 |
| 3 | 3/3 | Bright | $1,800 |
West
| A | B | C | |
|---|---|---|---|
| 1 | Date | Customer | Amount |
| 2 | 3/2 | Chen | $6,400 |
| 3 | 3/5 | Delta | $2,100 |
Goal: combine them into a single “Master” sheet with a “Region” column added.
Method 1: Power Query (Best for Recurring Consolidation)
Power Query is the most robust approach, especially for recurring tasks:
1Go to Data → Get Data → From Other Sources → Blank Query
2In the Power Query editor, enter this formula in the formula bar:
=Excel.CurrentWorkbook()
This returns a list of all tables and named ranges in the workbook. If your sheets use Excel Tables (ListObjects), they’ll appear here.
3Filter to include only the sheets you want, expand the data, and add the source sheet name as a column.
4Click Close & Load to output the consolidated data to a new sheet.
Why Power Query wins
When data on the source sheets changes, just click Data → Refresh All and the master list updates automatically. No re-copying, no formulas to maintain. Power Query is available in Excel 2016+ and Microsoft 365.
Method 2: Manual Copy-Paste with a Source Column
The simplest approach for a one-time consolidation:
1Create a “Master” sheet with the same columns plus a “Region” column
2Copy data from each sheet and paste below the previous region’s data
3Fill in the Region column for each block
What the combined master list looks like
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Date | Customer | Amount |
| 2 | East | 3/1 | Acme | $4,200 |
| 3 | East | 3/3 | Bright | $1,800 |
| 4 | West | 3/2 | Chen | $6,400 |
| 5 | West | 3/5 | Delta | $2,100 |
One-time only
This doesn’t update when source data changes. Every time the source sheets get new data, you repeat the entire copy-paste process. For anything recurring, use Power Query or VBA.
Method 3: VSTACK (Microsoft 365 and Excel 2024)
If you’re on Microsoft 365 or Excel 2024, the VSTACK function stacks ranges vertically in a single formula. On its own it also stacks the empty rows at the bottom of each range, which come through as rows of zeros between the sheets, so wrap it in FILTER to keep only the rows that have a date:
=FILTER(VSTACK(East!A2:C100, West!A2:C100, Central!A2:C100), VSTACK(East!A2:A100, West!A2:A100, Central!A2:A100)<>"")
Stacks all three ranges into one continuous list and drops the empty rows. Uses dynamic arrays, so the output grows automatically as rows are added to any sheet, down to row 100. Available in Microsoft 365 and Excel 2024.
The formula brings back the values but not their formatting, so on the master sheet the dates show as serial numbers such as 46082 and the amounts lose their dollar signs. Format the master’s columns once, Short Date for column A and Currency for column C (both in the Number Format box on the Home tab), and every row the formula adds later shows the same way.
Adding a source identifier
VSTACK alone doesn’t add a “which sheet did this come from” column. You’d need to combine it with HSTACK and a repeating label array, which gets complex. Power Query handles this more elegantly.
Converting Sheets to Tables First
Before consolidating, convert each sheet’s data to an Excel Table (Ctrl+T). This makes Power Query work better and ensures the consolidation picks up new rows automatically as data is added to each sheet.
Common questions
Do I have VSTACK?
Only on Microsoft 365 and Excel 2024 or later. On older versions use Power Query or manual copy-paste, since a workbook using VSTACK will show #NAME? for anyone without it.
How do I tell which sheet a row came from after combining?
Add a source column before you combine. Power Query can do this automatically; with copy-paste or VSTACK you have to add it yourself, and it cannot be reconstructed afterwards.
Does Power Query update when the source sheets change?
Not instantly. Click Refresh, or set the query to refresh when the file opens in Query Properties.
When Formulas Aren’t Enough
Consolidating a few same-structure sheets is manageable. But real business consolidation often involves:
- Different column layouts: each department’s sheet has columns in a different order or with different names
- Multiple workbooks: data lives in 12 monthly files, not 12 sheets in one file
- Data cleanup during merge: standardizing formats, removing headers, fixing dates as data is combined
- Deduplication: the same record might appear in multiple source sheets and needs to be merged, not duplicated
- Scheduled consolidation: combining data from a shared folder every week without manual intervention
A custom VBA consolidation tool reads multiple files or sheets, maps columns regardless of order, cleans and standardizes data, deduplicates, and outputs a formatted master list, all with one click.
“David worked brilliantly on this project and his communication was first class. He worked extremely quickly and efficiently, delivering a quality product on time and on budget.”
Inspirar B. · Leeds · PeoplePerHour
Consolidating Data from Multiple Sources?
Tell us about your data sources and we’ll build a tool that merges them automatically.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.