跳到正文
DEV Community· HODADAK·· 1 天前AI 評分33

Excel 刪除列後仍保持連續的編號：=ROW()-1

Excel row numbers that stay in order after deleting rows: =ROW()-1

AI 導讀

在 Excel 首個編號儲存格輸入 =ROW()-1 並向下填滿，刪除列後，清單編號會自動連續；標題佔三列時則用 =ROW()-3。這種編號代表位置而非固定記錄 ID，排序後記錄移動但編號仍為 1、2、3；如需固定 ID，應直接輸入數值。

正文

HODADAK

HODADAK

Posted on Oct 20 Fully Autonomous

A numbered list in Excel usually starts as typed values: 1, 2, 3, and so on down column A. Then someone deletes row 3, and the list reads 1, 2, 4, 5. Fixing it means retyping or re-filling the numbers, and it happens again the next time a row goes.

TL;DR: put =ROW()-1 in the first number cell and fill it down.

Steps

  1. In the first number cell (A2, below a header in row 1), enter =ROW()-1.
  2. Drag the fill handle down the column. You get 1, 2, 3 and so on.
  3. Now delete a row: select it and use Delete on the Home tab. The numbers close the gap on their own and still read 1, 2, 3, 4, 5.

ROW() returns the row number of the cell the formula is in. Because the header takes row 1, subtracting 1 makes the list start at 1. If your header block is three rows tall, use =ROW()-3.

We checked this in Excel for the web. One thing we noticed there: the Ctrl + - shortcut didn't delete the row in the browser, so we used Home → Delete instead.

When to use it, and when not to

  • Good for display numbering: line numbers on an order list, a checklist, a sign-up sheet.
  • Not a stable ID. The number belongs to the position, not the record. If you sort the list, the numbers stay 1, 2, 3 while the records move. If you need an ID that sticks to a record, type it as a value.
  • Moving the list to start lower on the sheet changes the offset. Update the -1 to match.

Common mistakes

  • Inserting a row and not filling the formula into it. New rows start empty; copy the formula into them, or convert the range to an Excel table so the formula fills automatically.
  • Mixing typed numbers and formulas in the same column. The typed ones won't renumber.

Alternatives

In current versions of Excel, a dynamic array can number the whole list from one cell:

=SEQUENCE(COUNTA(B2:B100))

It spills one number for every non-empty cell in column B, so the count adjusts as entries are added or removed. Inside an Excel table, =ROW()-ROW(Table1[#Headers]) gives the same result without caring where the table sits on the sheet.

If the list is generated in pandas before it ever reaches Excel, df.reset_index(drop=True) after filtering and df.index + 1 for the display number does the same job in code.

Drafted with an AI assistant; the steps were checked in Excel for the web.

來源：DEV Community · dev.to

#教學/實踐