How do I handle a month that is reissued with corrections?

Replace the file and refresh, rather than appending it, and keep the period column so you can confirm the month appears only once.

How do I combine 12 monthly Excel files into one?

Put them in one folder and use Data → Get Data → From Folder in Excel, then add a Period column from the file name. Refresh when a new month arrives.

How do I keep track of which store each row came from?

Add a Store column during stacking, from the file or tab name. Power Query's Source.Name column and DuckDB's filename option give you the name automatically.

Can I add sales in different currencies?

Only after converting them to one currency with a dated rate table. Keep the original amount and currency columns so the conversion can be checked.

Should I stack reports or put months side by side?

Stack them in one long table with a Period column. Pivot tables and charts can lay months out side by side later.

How to combine monthly, store and country sales reports into one spreadsheet

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

Stack the reports into one long table rather than placing them side by side, and add the columns the file name or tab name used to carry: Period, Store and Country. Keep amounts in their original currency with a Currency column, then convert to one reporting currency with a rate table. Use Power Query's From Folder, VSTACK, or SQL UNION ALL BY NAME to do the stacking.

Part of our guide: How to consolidate data in Excel

One long table, not twelve tabs

Monthly exports usually arrive as one file per month, per store or per country: sales_2026-07.xlsx, Leeds.csv, DE_sales.xlsx. Each has the same columns but no column saying which month or store it belongs to, because that was in the file name. Once you stack them, that information is lost unless you add it as a column. The goal is one table where every row says what, where, when and in which currency.

Target layout after stacking
Period	Store	Country	SKU	Units	Net sales	Currency
2026-07	Leeds	UK	A-100	40	960	GBP
2026-07	Berlin	DE	A-100	25	700	EUR
2026-08	Leeds	UK	A-100	52	1248	GBP
2026-08	Berlin	DE	A-100	31	868	EUR

Method 1: Power Query From Folder

Put all the files in one folder, with the same column headers.
Data → Get Data → From File → From Folder, choose the folder, then Combine & Transform.
Power Query adds a Source.Name column with each file name. Split or extract it into Period, Store or Country (for example Text.BetweenDelimiters on “_” and “.”).
Set data types, especially dates and numbers, then Close & Load.

Next month, drop the new file in the folder and click Refresh. Details and pitfalls are in Power Query combine files from a folder.

Method 2: VSTACK for tabs in one workbook

If each month is a tab in the same workbook, Microsoft 365 can stack them and add the period from a list of names:

=VSTACK(
  HSTACK(IF(SEQUENCE(ROWS(Jul!A2:D200)), "2026-07"), Jul!A2:D200),
  HSTACK(IF(SEQUENCE(ROWS(Aug!A2:D200)), "2026-08"), Aug!A2:D200))

The IF(SEQUENCE(...), "2026-07") part just repeats the label for every row. Fixed ranges include blank rows, so wrap the whole result in FILTER(result, CHOOSECOLS(result, 2)<>"") to drop them. This gets long for twelve months; Power Query scales better. In Google Sheets, {Jul!A2:D200; Aug!A2:D200} stacks ranges with a semicolon.

Method 3: SQL union

SELECT *, filename AS source_file
FROM read_csv('sales_*.csv', union_by_name = true, filename = true);

DuckDB reads every matching file, lines columns up by name even if their order differs, and records the file name so you can parse Period or Store from it. For Excel files or files with differing headers, UNION ALL BY NAME between individually loaded tables does the same. Analistable loads each file you add into DuckDB in the browser and can stack them when you ask “combine these monthly sales files and show units by store and month”.

Store and country files: mismatched columns

Store files tend to be consistent; country files often aren't. Expect different column names (“Net” vs “Net sales” vs “Umsatz netto”), different date formats (07/08/2026 means July in the US and August in the UK), and decimal commas. Map every file to one set of column names before stacking, and convert dates using the source's format, not your machine's. If the columns genuinely differ, see combining Excel files with different columns.

Currency: keep the original, convert explicitly

Never sum a column that mixes pounds and euros. Keep Net sales and Currency as they are, add a monthly rate table, and calculate a reporting-currency column alongside:

Monthly average rates to GBP
Period	Currency	Rate to GBP
2026-07	EUR	0.85
2026-08	EUR	0.86
2026-07	GBP	1
2026-08	GBP	1

=F2 * XLOOKUP(A2&"|"&G2, Rates!A:A&"|"&Rates!B:B, Rates!C:C)

Converted totals
Period	Leeds (GBP)	Berlin (EUR)	Berlin in GBP	Total GBP
2026-07	960	700	595	1555
2026-08	1248	868	746.48	1994.48
Total	2208	1568	1341.48	3549.48

Decide whether to use monthly average or month-end rates and write it down; finance teams usually have a policy. The lookup above concatenates two columns on the fly, a variant of joining on two columns at once.

Checks after combining

Row count per Period and Store matches each source file.
Total per file matches the file's own total line, and those total lines were removed before stacking so they aren't counted twice.
No month is missing or loaded twice: COUNTIFS per period, or GROUP BY period in SQL.
Every currency has a rate for every period.

Once the table is stacked, month-over-month growth and top products follow easily; see calculating month-over-month growth from monthly files and finding top products and customers across stores. The consolidate data in Excel guide covers other consolidation methods.

Naming files so the columns write themselves

Most of the effort in stacking reports is recovering the period and store from somewhere. Agree a file name pattern with whoever sends the reports, such as sales_STORE_YYYY-MM.csv, and the extraction is one step. Use ISO dates (2026-07) so names sort in order and nobody confuses July with the seventh day.

If a sender can't change their file names, keep a small lookup sheet that maps each file name to Store, Country and Period and join it on after stacking. That's more robust than parsing names that change shape, and the lookup itself documents which files have arrived for which month.

Frequently asked questions

How do I handle a month that is reissued with corrections?: Replace the file and refresh, rather than appending it, and keep the period column so you can confirm the month appears only once.
How do I combine 12 monthly Excel files into one?: Put them in one folder and use Data → Get Data → From Folder in Excel, then add a Period column from the file name. Refresh when a new month arrives.
How do I keep track of which store each row came from?: Add a Store column during stacking, from the file or tab name. Power Query's Source.Name column and DuckDB's filename option give you the name automatically.
Can I add sales in different currencies?: Only after converting them to one currency with a dated rate table. Keep the original amount and currency columns so the conversion can be checked.
Should I stack reports or put months side by side?: Stack them in one long table with a Period column. Pivot tables and charts can lay months out side by side later.