How do I keep the source name in Google Sheets?

With curly-brace stacking, add a column of labels beside each range, for example using ARRAYFORMULA to repeat the sheet name for each row, before stacking. Apps Script can also write the sheet name as it copies rows.

What if two sheets have the same name in different files?

Combine file and sheet names, such as north.xlsx / Orders, so each source label stays unique.

Can I add the source after combining?

Only if something in the rows already identifies the source, such as an ID prefix. Otherwise you have to recombine, so add it at the stacking step.

Does the source column update when I add files?

Yes, with Power Query From Folder: new files appear with their own names on refresh. Formulas like VSTACK need the new sheet added by hand.

Where is Source.Name in Power Query?

It's added by From Folder > Combine. If you removed it, go back to the step before Removed Other Columns or edit that step to keep it.

Can I get the sheet name with a formula?

=TEXTAFTER(CELL("filename",A1),"]") returns the current sheet's name in Microsoft 365, once the workbook has been saved. Older versions use MID and FIND on the same CELL result.

Does Consolidate keep source names?

Consolidate with Create links to source data adds outline rows per source but doesn't give you a tidy source column. Stacking with Power Query is better for that.

Should the source column be part of the key?

Only if the same ID can legitimately appear in different sources, such as order numbers that restart in each store.

How to add a source column when combining files or sheets

By the Analistable team · Updated 2026-10-10 · 5 min read

When you stack several files or sheets, add a column naming where each row came from so you can filter, trace and dedupe later. Power Query's From Folder keeps a Source.Name column by default; with VSTACK, stack a column of the sheet name alongside each range; in pandas, set df["source"] = path.name before concat.

Part of our guide: How to combine Excel sheets into one

Why a source column matters

Trace an odd value back to the file that contained it.
Filter or subtotal by store, region or month when that information is only in the file or sheet name.
Spot overlaps: the same ID appearing under two sources.
Remove one bad file's rows without rebuilding everything.

Stacked store exports with a source column
source	order_id	amount
leeds.xlsx	L-1001	42.5
leeds.xlsx	L-1002	18
york.xlsx	Y-2001	63
york.xlsx	Y-2002	27.5

Here Leeds totals 60.50 and York 90.50, a combined 151.00, a split you couldn't get without the source column.

Power Query: files from a folder

Data > Get Data > From File > From Folder > Combine & Transform builds a query that already includes a Source.Name column with each file's name (for example leeds.xlsx). Keep it rather than deleting it. To get a cleaner label, add a step:

= Table.TransformColumns(#"Previous Step",
    {{"Source.Name", each Text.BeforeDelimiter(_, "."), type text}})

That turns leeds.xlsx into leeds. You can also split a name like sales_2026-09.xlsx into a date column with Split Column by Delimiter. The full folder workflow is in combining files from a folder with Power Query.

Power Query: sheets in one workbook

Data > Get Data > From File > From Excel Workbook, select the file, and in the Navigator select the workbook node itself (not one sheet), then Transform Data.
Filter the Kind column to Sheet (or Table).
Keep the Name and Data columns and remove the rest.
Expand Data. The Name column stays beside every row and is your source column.
Promote headers and filter out repeated header rows if each sheet had its own.

Excel 365: VSTACK with a sheet label

VSTACK on its own loses the sheet name. Pair each range with a column of its label using HSTACK and IF, then stack the results:

=LET(
  a, Leeds!A2:B3,
  b, York!A2:B3,
  VSTACK(
    HSTACK(IF(SEQUENCE(ROWS(a)), "Leeds"), a),
    HSTACK(IF(SEQUENCE(ROWS(b)), "York"), b)
  )
)

IF(SEQUENCE(ROWS(a)), "Leeds") makes a column of "Leeds" with one entry per row of a. For many sheets this gets long; Power Query scales better. See VSTACK and dynamic arrays for trimming blank rows with FILTER.

Python and VBA

from pathlib import Path
import pandas as pd

frames = []
for path in sorted(Path("stores").glob("*.xlsx")):
    for sheet, df in pd.read_excel(path, sheet_name=None).items():
        df.insert(0, "source_file", path.name)
        df.insert(1, "source_sheet", sheet)
        frames.append(df)
combined = pd.concat(frames, ignore_index=True)

sheet_name=None returns every sheet as a dict, so the loop covers multi-sheet workbooks too. In VBA, when you copy a block of rows from each workbook into a master sheet, write the workbook name next to it in the same step:

' inside a loop over files, after pasting n rows starting at row r
master.Range(master.Cells(r, 1), master.Cells(r + n - 1, 1)).Value = wb.Name

Tips

Name files consistently (store_month.xlsx) so the source column can be split into useful fields.
Put the source column first, so it's visible when you scroll a wide table.
If two files share a name in different folders, use the full path or folder name too.

Analistable keeps each loaded file or sheet as its own table in an in-browser database, so the AI can label rows by source when it stacks them in a query. Your files aren't changed. For more ways to stack, see combining Excel files into one spreadsheet and the combine Excel sheets guide.

Turn file names into real fields

A source column is most useful when it holds structured information. If files are named like north_2026-09.xlsx, split the name into region and month columns, then change the month's type to a date. You can filter and group by those fields like any other data, and new files that follow the same pattern slot in automatically on refresh.

If the naming is inconsistent, a small mapping table (file name to region, month) joined to the stacked data is more reliable than clever text parsing. It also documents which files you expect, so a missing month is obvious.

Check the stack by source

After combining, count rows per source and compare with each file. A PivotTable with the source column in rows and a count of IDs gives the check in seconds. A source with zero rows usually means a sheet was named differently or the header row didn't match; a source with twice the expected rows usually means the same file was included twice, perhaps under a copy name.

Frequently asked questions

How do I keep the source name in Google Sheets?: With curly-brace stacking, add a column of labels beside each range, for example using ARRAYFORMULA to repeat the sheet name for each row, before stacking. Apps Script can also write the sheet name as it copies rows.
What if two sheets have the same name in different files?: Combine file and sheet names, such as north.xlsx / Orders, so each source label stays unique.
Can I add the source after combining?: Only if something in the rows already identifies the source, such as an ID prefix. Otherwise you have to recombine, so add it at the stacking step.
Does the source column update when I add files?: Yes, with Power Query From Folder: new files appear with their own names on refresh. Formulas like VSTACK need the new sheet added by hand.
Where is Source.Name in Power Query?: It's added by From Folder > Combine. If you removed it, go back to the step before Removed Other Columns or edit that step to keep it.
Can I get the sheet name with a formula?: =TEXTAFTER(CELL("filename",A1),"]") returns the current sheet's name in Microsoft 365, once the workbook has been saved. Older versions use MID and FIND on the same CELL result.
Does Consolidate keep source names?: Consolidate with Create links to source data adds outline rows per source but doesn't give you a tidy source column. Stacking with Power Query is better for that.
Should the source column be part of the key?: Only if the same ID can legitimately appear in different sources, such as order numbers that restart in each store.