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 Setup
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
Result
| 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 Only)
If you’re on Microsoft 365, the VSTACK function stacks ranges vertically in a single formula:
=VSTACK(East!A2:C100, West!A2:C100, Central!A2:C100)
Stacks all three ranges into one continuous list. Uses dynamic arrays, so the output grows automatically. Available in Excel 365 only.
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.
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.
Start a Project