How to Merge Excel Files When the Header Row Is in a Different Place

The columns match. The data is fine. But one file starts with a company name and a reporting period, another has a blank row and a logo, and a third begins straight with the headings. Anything that assumes “row 1 is the header” gets at least two of them wrong.

What goes wrong when the header row moves

Most combine methods assume the first row of every file holds the column names. When it doesn’t:

• The title becomes the heading. “South region sales” is promoted to a column name, and the real headings arrive as a row of data.
• Columns go missing. Tools that combine by column name find no “Customer” column in south.xlsx, so its rows are left blank or dropped.
• Junk rows appear in the data. “Period: October”, blank rows and export timestamps end up as records, and filters and pivot tables stop making sense.
• A fixed “remove top rows” step breaks. Deleting three rows fixes south.xlsx, deletes two real records from north.xlsx and leaves the timestamp in west.csv.

A moved header row is one of the most common forms of spreadsheet schema drift: the data hasn’t changed, but its position on the sheet has.

What usually sits above the table

Method 1: by hand in Excel

Fine for three files. Tedious for thirty, and it has to be repeated every time a new batch arrives.

Method 2: Power Query, finding the header row by content

Instead of removing a fixed number of rows, load each sheet without headers, find the row that contains a heading you know will be there, skip everything above it, then promote headers. As a function you can apply to every file in a folder:

(raw as table) as table =>
let
    Position = List.PositionOf(Table.Column(raw, "Column1"), "Customer"),
    Result   = if Position < 0
               then error "Header row not found"
               else Table.PromoteHeaders(Table.Skip(raw, Position),
                                         [PromoteAllScalars = true])
in
    Result

• Load each workbook with Excel.Workbook([Content], false) so nothing is promoted too early, then pass the sheet’s [Data] to the function.
• This version looks for “Customer” in the first column. If the anchor heading can be in any column, search each row instead.
• Raising an error when the heading isn’t found is deliberate: a file that silently loads nothing is worse than one that stops the refresh.
• Apply it in the Transform Sample File query, or call it per file, so every file is handled on its own rather than all files sharing one fixed offset.

Method 3: Python

import pandas as pd

raw = pd.read_excel("south.xlsx", header=None)
header_row = raw.index[raw.eq("Customer").any(axis=1)][0]
df = pd.read_excel("south.xlsx", header=header_row)

Read once without headers, find the first row containing the anchor heading, then read again from that row. Loop over the files, add a Source File column, and combine with pd.concat.

Method 4: ConsoliSheet

ConsoliSheet looks for the coherent table in each file separately, even when a worksheet contains title rows, spacers or separate calculations. Each file can start on a different row; the headings are found for each one. Where a sheet holds more than one plausible table, or the header row is genuinely unclear, you are asked to choose rather than having one picked for you. Columns are then lined up by heading, and every output row keeps its Source File.

Checks after combining

• No title text in the data. Filter the first column for words like “Report”, “Period” or “Total”.
• No repeated header rows. A row reading “Customer | Amount | Month” in the middle of the data means a file was combined from the wrong row.
• Row counts reconcile. Output rows should equal the data rows in each file, not including titles, blanks and totals.
• Totals reconcile. Each file’s total should match its rows in the output.

Common questions

Related: combining Excel files with different column names covers what to do once the tables are found but the headings differ, and Power Query Combine Files problems covers the other ways a folder combine breaks.