AI how-to notes · 2026-09-18 · last run 2026-09-18 · 한국어 원문

Combining several Excel sheets and files into one table — paste, Power Query, Python

One line summary

Two files of three monthly sheets each, 27 rows in all, with an amount total of 1,526,850, combined into one table. Copy and paste dragged the header in six times and ended up at 33 rows (even taking the top row off, five header rows remain inside the table), and when the paste row was off by one the header vanished, the data shrank from 27 rows to 22, and 294,850 disappeared. Power Query and Python both came out at 27 rows and 1,526,850, matching the source. Checking the row count and the total against the source afterwards is half the job.

Who this is for

• Anyone who keeps one sheet per month and has to see a quarter or a year at once
• Anyone who has to feed several branch files into a single pivot table
• Anyone who combined by pasting and was confused to find an item called 「거래처」 (customer) in the pivot

Test environment

We did not open an Excel window and click the menus. We called the equivalent commands over COM and read the resulting numbers.

What we are combining — count the source first

The columns are the same five across all six sheets — date, customer, item, quantity, amount. Row counts were deliberately varied from 3 to 6. Note that January and May both have 5 rows and February and June both have 4, so the row count alone cannot tell you which sheet went missing. To trace where a row came from, write the source into a column and reconcile row counts and totals per source.

Write these two numbers down before you combine. 27 and 1,526,850 are the baseline for the rest of this note.

Method 1 — copy and paste

The first thing anyone reaches for, and fine for three or four sheets. It slips in two places.

When the headers come along too

Grab the whole table on each sheet and paste them one after another, and the header comes with it six times.

「금액」이 글자로 들어앉은 행: 1, 7, 12, 19, 23, 29  (모두 6줄)
=COUNTIF(B:B,"거래처")  ->  6

(The first line lists the rows where 「금액」, the amount header, is sitting as text — six rows in all.)

The confusing part is that the total is fine. =SUM(E:E) returns 1,526,850, correctly, because the text 「금액」 is not a number and SUM simply skips it. AVERAGE also returns a normal-looking 56550.

What breaks is anything that counts rows.

If you are in the habit of dividing SUM by COUNTA for an average, this is where you report an average that is off by about 8,836. And with the text 「거래처」 sitting six times in the customer column, pulling a customer list cannot go well either — though we did not actually build a pivot table to confirm that.

When the paste row is off by one

Aim at "the last row" rather than "the row after the last" and you overwrite it. This time we took the paste row as End(xlUp).Row instead of End(xlUp).Row + 1 to create that situation.

The 21 in the first cell treats the top row as a header and subtracts it. Here that header was buried under data and is gone, so the data actually left should be read as 22 rows.

After six pastes, the amount cells holding a number went from 27 to 22 and the total dropped by 294,850. The first paste landed on the header row and took the header with it. Rows are not left standing empty — values are simply overwritten and gone — so without checking the total against the source it goes straight past you.

If you are going to do this by pasting, print =COUNTA(A:A) after each sheet. The increase should equal the row count of the sheet you just pasted, and for this table it should end at 28 including the header.

Method 2 — Power Query

It tidies the header down to one row for you, and the query you build can be reused next time. That said, the query below has both files pinned by name, so more files means editing the query. Whether the result grows when you add new data and hit refresh was not run this round.

This time we pushed the M code below in as a query and loaded it as a table. It walks every sheet in a file and concatenates them, then concatenates that result across the two files.

let
    한파일 = (경로 as text) as table =>
        let
            책 = Excel.Workbook(File.Contents(경로), true),
            시트만 = Table.SelectRows(책, each [Kind] = "Sheet"),
            이어붙임 = Table.Combine(시트만[Data])
        in
            이어붙임,
    결과 = Table.Combine({ 한파일("C:\\...\\X2_44_매출_1분기.xlsx"),
                           한파일("C:\\...\\X2_44_매출_2분기.xlsx") })
in
    결과

(The identifiers are Korean — 한파일 "one file", 경로 "path", 책 "workbook", 시트만 "sheets only", 이어붙임 "concatenated", 결과 "result".)

The two file paths in the middle were shortened to fit. What we actually passed were full paths in a temp folder, and they are in 03_파워쿼리_로그.txt as-is.

Entering this query in the Excel window

2026-09-28 supplement — checked against official help; these on-screen steps were not executed directly. This is the Windows desktop procedure for entering the M code above. No new code or measured result has been added.

1. Save the two source files and open the workbook that will hold the combined result. The code assumes the layout used in our test: a header in the first row of each sheet, with matching column names.
2. Select Data → Get Data → From Other Sources → Blank Query.
3. In Power Query Editor, select Home → Advanced Editor. Replace all the starter code with the code above, from let to the final 결과.
4. In each of the two 한파일(...) calls, replace the entire path inside the double quotes with the full path of the corresponding source file. Include the filename and .xlsx; leave no ... placeholder. Keep the double quotes and enter path separators exactly as in the Windows path.
5. Select Done and inspect the headers and data in the preview. Resolve any path or query error before continuing.
6. Select Home → Close & Load → Close & Load to load into a worksheet. Reconcile the data-row count and amount total with the source. The baseline for the original dummy data is 27 rows and 1,526,850.

The paths were checked against Microsoft's create, load, or edit a query and enter code in Advanced Editor instructions. Adding a new month's data and refreshing was not tested in this supplement either.

Results from the original run

The second argument of Excel.Workbook(..., true) tells it to use each sheet's first row as the header. What this query returned was five headers and 27 data rows. The six header rows that got mixed in during pasting did not happen here. The [Kind] = "Sheet" filter is there to stop named ranges and tables from being picked up as well.

The results of walking through the steps over COM in the original 2026-09-18 run:

성공  wb.Queries.Add("합치기", M)                    -> '합치기'
성공  ws.ListObjects.Add(OLEDB Mashup) + CommandText -> '표_ExternalData_1'
성공  lo.QueryTable.Refresh(BackgroundQuery=False)   -> True
표 범위 = $A$1:$E$28
머리글  = ['날짜', '거래처', '품목', '수량', '금액']
마지막 행 = 28  (머리글 뺀 줄 = 27)
=SUM(E:E) -> 1,526,850  / =COUNT(E:E) -> 27
원본과 차이: 줄 0 / 금액 0

(성공 is "succeeded"; the lines below it are the table range, the headers, the last row with the header-excluded row count in brackets, and the difference from the source in rows and amount.)

Row count and total matched on the first pass. The five headers are intact too.

The date column arrives as numbers

Look at the loaded table and the date column is five-digit numbers like 46025 and 46029. The values are fine.

파워 쿼리로 합친 표   A2  Text='46025'  Value2=46025  표시 형식='G/표준'
원본 시트            A2  Text='#######' Value2=46025  표시 형식='yyyy-mm-dd'
=TEXT(A2,"yyyy-mm-dd")  ->  2026-01-03

(The first two lines are the Power Query result and the source sheet; 표시 형식 is the display format.)

It holds the same 46025 as the source and arrived with the General display format, which is why it looks like a number. Select the date column, set the display format to yyyy-mm-dd, and 2026-01-03 comes back. The value itself is still 46025. Why cells like this appear and what to watch out for is written up in Excel says two identical dates are not equal.

Method 3 — Python (when files pile up monthly)

If you want the same consolidation in code, this is the route. Reading with sheet_name=None returns a dictionary keyed by sheet name. Here the six sheets in the two files read straight through and gathered into 27 rows. The code below has the filenames shortened; what we actually ran went from X2_44_매출_1분기.xlsx · X2_44_매출_2분기.xlsx to X2_44_합치기_파이썬.xlsx. The full script is at X2_44_python.py in the 원자료 folder.

import os, pandas as pd

frames = []
for p in ("매출_1분기.xlsx", "매출_2분기.xlsx"):
    시트들 = pd.read_excel(p, sheet_name=None)      # None = 모든 시트
    for 이름, df in 시트들.items():
        df = df.copy()
        df["출처"] = "%s / %s" % (os.path.basename(p), 이름)
        frames.append(df)

합 = pd.concat(frames, ignore_index=True)
print(len(합), int(합["금액"].sum()))       # 27 1526850
print(합.isna().sum().to_dict())           # 빈 칸 0
합.to_excel("합친표.xlsx", sheet_name="합친표", index=False)

(시트들 is the sheet dictionary, 이름 the sheet name, 출처 the source column, 금액 the amount column, and the comments read "None = all sheets" and "0 empty cells".)

Attaching a 출처 (source) column lets you trace later which sheet a row came from, and count per sheet to reconcile against the source.

X2_44_매출_1분기.xlsx / 1월   5행    233,500
X2_44_매출_1분기.xlsx / 2월   4행    175,700
X2_44_매출_1분기.xlsx / 3월   6행    350,200
X2_44_매출_2분기.xlsx / 4월   3행    147,050
X2_44_매출_2분기.xlsx / 5월   5행    307,500
X2_44_매출_2분기.xlsx / 6월   4행    312,900

(1월–6월 are the month sheets and 행 means rows.)

Grouping by customer instead adds back up to 1,526,850.

When one character in a column name differs

concat lines rows up by column name. One character out and it is not the same column. We wrote the July file's header as 금액 (with one trailing space) and combined.

7월 파일의 열 이름 : ['날짜', '거래처', '품목', '수량', '금액 ', '출처']
합친 뒤 열 이름    : ['날짜', '거래처', '품목', '수량', '금액', '출처', '금액 ']
줄 수              : 29      <- 7월 2줄이 더해져 맞다
sum('금액')        : 1,526,850   <- 7월 74,000원이 빠졌다
sum('금액 ')       : 74,000
'금액' 열 빈 칸    : 2줄

(The lines are the July file's column names, the column names after combining, the row count — correct, with July's 2 rows added — then the amount sum, which is missing July's 74,000, the sum of the stray column, and the 2 empty cells in the real amount column.)

By row count alone, 29 is correct. The total has lost July entirely, and the missing amount is sitting off to the side in a new 금액 column. Printing the column names after combining and checking isna().sum() exposes it immediately.

The fix is to strip leading and trailing spaces as you read.

df.columns = df.columns.str.strip()

After stripping and recombining, the columns tidied down to six and it came to 29 rows with a total of 1,600,850.

Cross-checking the three methods

1a passes if you only look at the total. You have to look at the row count as well for the gap between 32 and 27 to show. 1c is the reverse — counting rows makes the 21 stand out. Looking at both numbers catches both.

The "rows excluding header" column is the last row minus 1, so it equals the data row count only where the header is still alive. In 1c the header was overwritten and gone, so the real number of data rows left is not 21 but the 22 in the next column.

Three things are worth confirming on a combined table. Whether the header-excluded row count equals the sum of the source sheets' row counts, whether the amount total equals the sum of the per-sheet totals, and whether the count of numeric cells in the amount column (=COUNT(E:E)) equals the row count. If that third number is lower than the row count, the amount column holds that many non-numeric entries. They could be text, blanks or error values, so you have to go find the cells to know which.

What failed

• We were not confident Workbook.Queries could be driven over COM, so we made it print success or failure at each step and ran it. Adding the query, loading it as a table and refreshing all passed, with no stall.
• We tried reading the Korean names of the ribbon commands with CommandBars.GetLabelMso. Only seven of twenty-three came back. 「데이터」 (Data) tab, 「모두 새로 고침」 (Refresh All), 「새로 고침 상태」 (Refresh Status), 「붙여넣기」 (Paste), 「선택하여 붙여넣기...」 (Paste Special), 「필터」 (Filter) and 「피벗 테이블」 (PivotTable) came back; the other sixteen all returned 0x80070057, and all the Power Query names are in that group. We could not tell whether the idMso names we guessed were wrong or whether this build does not have them.
• With the three result files open at once, passing Application.Evaluate a sheet name without the workbook name returned different values than with only one file open. We reordered the run to measure before opening the other files, and recorded both cases side by side in 08_환경·실패재현_로그.txt [2].

(가) 붙여넣기 파일 하나만 열어 둔 상태
  =COUNTA('1a_머리글째로'!B:B)            -> 33.0
  =COUNTIF('1a_머리글째로'!B:B,"거래처")   -> 6.0
(나) 결과 파일 세 개를 모두 열어 둔 상태  (ActiveWorkbook = X2_44_합치기_파이썬.xlsx)
  =COUNTA('1a_머리글째로'!B:B)            -> 1.0
  =COUNTIF('1a_머리글째로'!B:B,"거래처")   -> -2146826265   <- #REF!

((가) is with only the paste file open, (나) with all three result files open.)

What we did not test

• In the original 2026-09-18 run, we did not click through the Power Query menus. The query was created by pushing M code in over COM. The on-screen procedure above was added after checking official help on 2026-09-28; it was not executed directly.
• Renaming columns or setting types in the Power Query editor was not included. We went only as far as concatenating sheets.
• Reading a whole folder (Folder.Files) was not tested. We only confirmed the case of naming two files explicitly.
• Whether the result grows when you add a new month's sheet and hit refresh was not run. We only measured the one load.
• Sheets with a different column order or a different number of columns were not tried. All six sheets had the same five columns.
• How [Kind] = "Sheet" filters when empty or hidden sheets are mixed in was not checked.
• How long each of the three methods takes over tens of thousands of rows was not measured. We only used 27.
• Files in xls (97-2003) format, or CSVs mixed in, were not tested.

Related notes

• Excel says two identical dates are not equal — hidden time and display format
• Cleaning up an inherited Excel table — merged cells, blank rows, text dates
• Finding and removing duplicates in Excel
• Comparing two lists in Excel — what is in both, what is in only one