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

Cleaning up an inherited Excel table — unmerge cells, delete blank rows, convert text dates

One line summary

A table handed over from someone else usually has the department column merged, blank rows sitting between teams, and hire dates entered as text like 2024.03.05. In that state the sort button stops with 「이 작업을 수행하려면 병합하려는 모든 셀의 크기가 동일해야 합니다」 — on an English build, "To do this, all the merged cells need to be the same size" — and any date-based count comes back 0. Running unmerge → delete blank rows → convert dates over a 12-row dummy table left 9 rows, and the amount total of 10,100 was identical before and after.

Who this is for

• Anyone who has to sort, filter or pivot a table that came from another team
• Anyone who unmerges and re-merges every single time because the sort button throws a merged-cell error
• Anyone pulling a date range who keeps getting 0 rows

Test environment

The menu names in the steps below follow the Korean build of Excel 16.0, but we did not open a window and click through them in this round. We called the equivalent commands over COM and read the resulting numbers. The on-screen names may differ on your build.

The table before cleanup

The department column was merged vertically (A2:A4, A6:A7, A9:A11) and a blank row was inserted wherever the team changed (rows 5, 8, 12). Hire dates mixed dotted, short and hyphenated notations, and ISTEXT confirmed all nine cells were text.

We counted the before numbers first.

The department looks like it is written on nine rows, but the value actually sits in only four cells. Merging keeps the value in the top-left cell and leaves the rest empty.

Sorting first got us this far.

Microsoft Excel: 이 작업을 수행하려면 병합하려는 모든 셀의 크기가 동일해야 합니다.

(In English builds, "To do this, all the merged cells need to be the same size.")

Date aggregation does not work in this state either.

MIN and MAX ignore text entirely, which is where the 0 comes from. It does not mean "nobody joined in the second half of the year"; it means not one value is being read as a date.

Step 1 — unmerge and fill the values down

Through the menus, the order is this.

1. Select the department column (A2:A13) and click 홈 > 병합하고 가운데 맞춤 (Home > Merge & Center) once more to unmerge.
2. With the same range selected, press Ctrl+G > 옵션 > 빈 셀 (Special > Blanks) and click OK. Only the empty cells stay selected.
3. Type =, press the up arrow to point at the cell directly above, then press Ctrl+Enter. Every selected blank cell gets the value from above.
4. Copy the department column and paste it back over itself as values, turning the formulas into values.

Right after the unmerge, Go To Special picked up $A$3:$A$5,$A$7:$A$8,$A$10:$A$12 as blanks. After the Ctrl+Enter fill, A3 held =A2 and A5 held =A4, and =COUNTA(A2:A13) went from 4 to 12.

It reached 12 because the department cells on the blank rows (5, 8, 12) got filled from above too. Rows that should not be in the table now carry text like 「영업1팀」 (Sales Team 1), but the next step removes them along the way when you key off the name column. That is exactly why the blank-row deletion has to key off the name column rather than the department column.

After converting to values, reading A3 again returned the value 「영업1팀」 instead of a formula. Deleting the three blank rows in that state left the departments as 영업1팀 3 rows, 영업2팀 2 rows, 지원팀 3 rows, 기획팀 1 row, and the amount total stayed at 10,100. We did not run what happens if you skip step 4 and delete rows while the formulas are still live.

Step 2 — delete the blank rows

1. Select the name column (B2:B13). Not the whole table — pick exactly one column that must never be empty.
2. Ctrl+G > 옵션 > 빈 셀 (Special > Blanks) > OK.
3. Right-click > 삭제 > 행 전체 (Delete > Entire row).

The cells caught as blanks were the three at $B$5,$B$8,$B$12, and after the delete the table was 10 rows including the header (9 data rows). The amount total stayed at 10,100. If what you deleted really was only blank rows, the total does not move. If the total changed, start by retracing what you deleted.

Running Go To Special with the whole table selected catches every blank cell inside the table, and deleting entire rows from there can take out rows that hold values in other columns. That is the reason for picking one column.

Step 3 — turn text dates into real dates

DATEVALUE is the first thing that comes to mind here, and it did not accept dotted dates. We put five notations in one cell each.

Both dotted forms failed. So we use Text to Columns instead.

1. Select the hire date column (C2:C10) and nothing else.
2. 데이터 탭 > 텍스트 나누기 (Data tab > Text to Columns).
3. In step 1, pick 「구분 기호로 분리됨」 (Delimited) and click Next.
4. In step 2, clear every delimiter checkbox and click Next.
5. In step 3, set the column data format to 「날짜」 (Date), choose 「년월일」 (YMD) from the list on the right, and click Finish.

All nine cells, dots and hyphens mixed, converted in one pass.

변환 전: 2024.03.05, 2024.3.7, 2024-03-11, 2024.04.02, 2024.04.15, 2024.05.01, 2024.05.20, 2024.12.01, 2025.01.09
변환 후: 2024-03-05, 2024-03-07, 2024-03-11, 2024-04-02, 2024-04-15, 2024-05-01, 2024-05-20, 2024-12-01, 2025-01-09
내부 값: 45356, 45358, 45362, 45384, 45397, 45413, 45432, 45627, 45666

(The three lines are the values before conversion, after conversion, and the underlying serial numbers.)

Excel holds dates as serial numbers, so a proper conversion leaves those five-digit numbers underneath. Checking the displayed date and the serial number together is the reliable version (45356 is 2024-03-05). If the column is too narrow and shows #######, widening it is all that is needed.

Counting again after the conversion:

What happens if you change the order

Sorting before converting the dates looks like this.

Sorted as text, March 7 (2024.3.7) landed in the seventh row, below December 1, and the hyphenated March 11 (2024-03-11) landed in the eighth. Comparison runs character by character from the front, so 3 and 03 cannot stand in the same place. The exact order this table produced is not a rule in itself, but mixed notations clearly break the date order. Sort the same column after converting and it stands correctly as 홍길동 · 김철수 · 이영희 · 박민수 · 정수진 · 최동해 · 한여름 · 오세라 · 서지훈.

We then ran steps 1 and 2 the other way around. Deleting the three blank rows with the merges still in place worked, the merged ranges shrank to A2:A4 · A5:A6 · A7:A9 accordingly, and the amount total stayed at 10,100. Unmerging afterwards, the rows with an empty column A were 3, 4, 6, 8 and 9. For this table, swapping the order gave the same result.

The reason for keeping the order is elsewhere. As long as merges remain, the sort button is blocked by the error above, and sorting before converting the dates scrambles the order as shown. Going unmerge → delete blank rows → convert dates lets you count rows and totals at the end of each step.

When there are many files — Python

When identical files pile up every month, doing it by hand takes longer. openpyxl tells you where the merged ranges are and pandas does the rest. The code below has the filenames shortened. The files we actually ran were E2_26_정리표.xlsx → E2_26_파이썬정리후.xlsx, and the full script is at 원자료\excel-clean-data-table\E2_26_python.py.

import openpyxl, pandas as pd

wb = openpyxl.load_workbook("정리표.xlsx")
ws = wb["원본"]
print([str(r) for r in ws.merged_cells.ranges])   # ['A2:A4', 'A9:A11', 'A6:A7']

# 1단계 병합 해제 + 값 채우기
for rng in list(ws.merged_cells.ranges):
    v = ws.cell(rng.min_row, rng.min_col).value
    ws.unmerge_cells(str(rng))
    for r in range(rng.min_row, rng.max_row + 1):
        for c in range(rng.min_col, rng.max_col + 1):
            ws.cell(r, c).value = v
wb.save("정리후.xlsx")

df = pd.read_excel("정리후.xlsx", sheet_name="원본", usecols="A:D", nrows=12)

# 2단계 빈 행 삭제
df = df.dropna(subset=["이름"]).copy()

# 3단계 문자 날짜 -> 날짜 (점을 붙임표로 바꿔 넘긴다)
s = df["입사일"].astype(str).str.strip().str.replace(".", "-", regex=False)
df["입사일"] = pd.to_datetime(s, format="mixed", errors="coerce")
print(df["입사일"].isna().sum())      # 0 = 변환 실패 없음

df = df.sort_values("입사일").reset_index(drop=True)
df.to_excel("정리후.xlsx", sheet_name="정리후", index=False)

(The sheet is 원본 "source", the columns are 이름 "name" and 입사일 "hire date", and the three comments mark the same three steps.)

The result matched the COM side: 12 rows → 9 rows, total 10,100 → 10,100, 0 conversion failures, 2 rows joined on or after 2024-07-01. Department totals were 영업1팀 3,500 / 영업2팀 2,900 / 지원팀 3,100 / 기획팀 600, which add up to 10,100.

With errors="coerce", values that cannot be parsed do not raise — they are left as an empty date (NaT). The row is not dropped; only the date cell goes blank. That is why you should always print isna().sum() after the conversion. If that number is not 0, it is either an unparsed value or one that was empty to begin with, and you need to find out which.

What failed

• Our first sort went through COM's Range.Sort with named arguments (Key1=, Order1=, Header=). It sorted left to right instead of top to bottom, so the headers flipped from 부서·이름·입사일·실적금액 to 실적금액·입사일·부서·이름. We switched to the Sort object form that the macro recorder produces (SortFields.Add → SetRange → Orientation = 1 → Apply) and re-ran it. We reproduced the same call again later and recorded it in 07_순서·정렬_보탬시험.txt.
• The first time we measured the text-date sort, we were measuring values that had already been converted to dates, so the sort came out looking correct. We kept the pre-conversion text separately, put it back, and measured again. The line in the log about restoring hire dates to the text sheet is that re-measurement.
• We did not widen the column right after the date conversion, so the screen and the log were all #######. We added a column autofit and read it again.
• A fact-check pass with GPT flagged "deleting blank rows first makes it hard to predict which cells the department value ends up in" as guesswork we had not run. Running the swapped order for real gave the same result for this table, and the sentence was rewritten as above.

What we did not test

• We never opened an Excel window and clicked the menus. The menu names and wizard steps in this note follow the Korean build of Excel 16.0, and the numbers come from COM commands that do the same work.
• Horizontally merged cells (a header merge like A1:C1) were not in the test table. We only handled vertical merges.
• Whether the 1904 date system in the workbook options produces the same serial numbers — we did not switch it. Only the default setting was checked.
• Whether Power Query gives the same result for these three steps — not compared.
• How Text to Columns handles a value that cannot be a date, such as 2024.13.05 — not tried.
• Which century a two-digit year (24.03.05) is read as — not checked.
• How long Go To Special takes to delete blank rows when there are tens of thousands of rows. We only used 12.
• Whether pandas' format="mixed" reads the dotted notation without swapping dots for hyphens. We only confirmed the swapped form.

Related notes

• When an Excel formula will not calculate
• Comparing two lists in Excel — what is in both, what is in only one
• Finding and removing duplicates in Excel