How to combine multiple reports into one Excel file

First: stack or match?

"Combine" means one of two different jobs, and the method depends on which one you have:

• Stack (append): the files hold the same kind of rows. Open orders from three branches, or four weekly sales exports. You want one long list, then totals.
• Match (merge): the files hold different facts about the same things. An open orders report and a customer list, or stock on hand and items on order. You want them side by side on one row per customer or item.

Most of this guide is about stacking. Matching is covered in its own section below.

1. Copy the sheets into one workbook

Good for: a few files, once.

1. Open all the files. In each one, right-click the sheet tab and choose Move or Copy.
2. Pick your combined workbook in To book, tick Create a copy and click OK.
3. For one list, copy the rows from each sheet under the last one on an "All" sheet, keeping only the first header row. Add a column that says which file each row came from, or you'll lose track.

Where it breaks: it's all by hand, so it starts over every week, and it only works if every file has its columns in the same order.

2. Power Query: combine every file in a folder

Good for: Excel or CSV exports that arrive every week with the same layout.

1. Put all the files in one folder, with nothing else in it.
2. In Excel, go to Data > Get Data > From File > From Folder and pick the folder.
3. Click Combine > Combine & Transform Data. Choose the sheet or table to use from the sample file and click OK.
4. Power Query stacks every file and adds a Source.Name column with the file name of each row. Keep it: it's your "which store" or "which week" column. Rename it and, if you like, use Transform > Replace Values to strip .csv off the end.
5. Filter out the repeated header rows and blank rows, set the column types, then Close & Load.

Next week: drop the new files into the folder and use Data > Refresh All. Every file in the folder is read again, so move old files out if you only want the latest.

Where it breaks: Power Query lines columns up by name. If one branch's export calls it "Total" and another calls it "Amount", you get two half-empty columns. If one file is a PDF, it has to be cleaned up on its own first (see the Power Query PDF cleanup guide).

When the columns are named differently

This is the usual case when reports come from different systems. Fix it before the files are stacked:

1. In Power Query, open the query called Transform Sample File. Every step you add here runs on each file before it's combined.
2. Rename the columns there to one set of names. For a file whose names differ, add a step that renames only the ones that exist, using MissingField.Ignore so files without that column don't cause an error:

= Table.RenameColumns(#"Promoted Headers",
    {{"Total", "Amount"}, {"Order", "Order #"}}, MissingField.Ignore)

If the files differ in more than names (different column orders, extra header lines, one PDF among spreadsheets), it's often simpler to make one query per kind of file, clean each one, and then use Home > Append Queries to stack the finished results.

Rows that show up twice

Weekly exports often overlap: an order still open on Monday shows up in this week's file and last week's. To count it once, in Power Query select the column that identifies a row (order number, invoice number) and choose Home > Remove Rows > Remove Duplicates. Sort by file date first, newest on top, if you want to keep the latest version of each row.

Matching two reports side by side

For the "match" job, you need a column both reports share, like a customer number or item code.

• XLOOKUP (Microsoft 365 and Excel 2021): in the first report, add a column that looks up the value from the second. For example, to pull each item's on-order quantity onto the stock report:

=XLOOKUP([@Item], OnOrder[Item], OnOrder[Qty], 0)

• In older versions, =IFERROR(VLOOKUP(A2, OnOrder!A:C, 3, FALSE), 0) does the same, as long as the item code is the first column of the range.
• Power Query: load both reports as queries and use Home > Merge Queries, pick the shared column in each and a join kind (Left Outer keeps every row of the first report). This keeps working when you refresh.

Watch for codes that look the same but aren't: leading zeros dropped in one file, or trailing spaces. TRIM and setting both columns to text fixes most of them.

When it's the same set of files every week

A folder query is a good setup if every file comes from the same system in the same layout. When they don't, with a PDF from one branch, a CSV from another and an Excel export that changed its columns last month, the upkeep is the hard part.

Wheelbarrow is built for that. Drop in all the files together, in whatever format each one comes in, and describe what you want, for example "one list of all branches, on-hold orders on their own tab, totals by customer across every file". You see it built from your own files before you pay, and you keep a small tool that rebuilds the workbook from next week's files in seconds, even offline. See combining reports for more examples.