Each branch sends its own sheet (Delhi, Mumbai, Pune) with the same columns, and you need one combined list for a pivot. Copy-paste works until next month. VSTACK keeps it live.

In this article

1. Stack sheets
2. Remove the blank rows
3. Add the headers once
4. Tag each row with its branch
5. Sort and summarise the combined list
6. Formula or Power Query?
7. Where people go wrong
8. Practice

Stack sheets

=VSTACK(Delhi!A2:F500, Mumbai!A2:F500, Pune!A2:F500)

Sheets that sit next to each other can use a 3D reference:

=VSTACK(Delhi:Pune!A2:F500)

Add a sheet between Delhi and Pune and it’s included automatically.

Remove the blank rows

Fixed ranges like A2:F500 bring empty rows along. Filter on a column that is always filled:

=LET(d, VSTACK(Delhi:Pune!A2:F500), FILTER(d, CHOOSECOLS(d, 1) <> ""))

Add the headers once

=VSTACK(Delhi!A1:F1, LET(d, VSTACK(Delhi:Pune!A2:F500), FILTER(d, CHOOSECOLS(d, 1) <> "")))

Tag each row with its branch

=LET(t, LAMBDA(r, n, HSTACK(r, IF(SEQUENCE(ROWS(r)), n))),
  VSTACK(t(Delhi!A2:F100, "Delhi"), t(Mumbai!A2:F100, "Mumbai"), t(Pune!A2:F100, "Pune")))

The IF(SEQUENCE(…), n) part repeats the branch name once for every row.

Sort and summarise the combined list

Name the combined spill (say, put it in Combined!A1 and refer to Combined!A1#), then:

=SORT(Combined!A1#, 1)                                                     ' by date
=GROUPBY(CHOOSECOLS(Combined!A1#, 7), CHOOSECOLS(Combined!A1#, 6), SUM)    ' total by branch (newest Excel 365)

Formula or Power Query?

VSTACK suits a handful of sheets in one workbook with a few thousand rows. For a folder of files, different column orders, or 100k+ rows, use Data › Get Data › From Folder in Power Query: it appends and cleans, and Refresh pulls in new files.

Where people go wrong

Issue	Cause
Columns misaligned	Sheets don’t share the same column order; VSTACK stacks by position, not by header
Zeros instead of blanks	Empty cells come back as 0; FILTER them out or use IF(d="","",d)
3D reference misses a sheet	The sheet sits outside the first:last range in tab order
Pivot won’t use the spill	Point the pivot at a Table loaded by Power Query, or paste values

Practice

Download the combo practice workbook below. Its Sales sheet of 200 orders (dates, regions, products, customers, amounts) is ready data to try every formula on this page, and the Practice sheet has 50 checked tasks on related combos.

More: all formula combos · Excel function course.

Keep reading

Formula CombosPercentage of Total and Percentage Change in Excel (Without #DIV/0! Errors)Formula CombosRank in Excel: Handle Ties and Rank Within Each GroupFormula CombosPartial Match and Wildcard Lookups in Excel (Contains, Starts With, Ends With)