How to / Counting

How to number rows automatically in Excel

In Excel 2021 and Microsoft 365, use =SEQUENCE(COUNTA(B2:B4)) in the first cell. It numbers as many rows as the list has.

Numbers for a list of names

AB
1#Name
21Ana
32Ben
43Chloe

In A2: =SEQUENCE(COUNTA(B2:B4)) → 1; 2; 3

How it works

  1. COUNTA counts how many names are in the list.
  2. SEQUENCE returns that many numbers, starting at 1, and they spill down the column.
  3. Give COUNTA a longer range, such as B2:B500, and the numbering grows as names are added.

Variations

  1. In any version of Excel

    AB
    1#Name
    21Ana
    3Ben
    4Chloe

    In A2: =ROW()-1 → 1

    ROW() is the row the formula is in. Fill it down: row 3 gives 2, row 4 gives 3. Subtract whatever puts 1 on your first row.

  2. Only number the rows that have a name

    AB
    1#Name
    21Ana
    3Ben
    4Chloe

    In A2: =IF(B2="", "", COUNTA($B$2:B2)) → 1

    Filled down, it skips empty rows and carries the count on after them.

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related