Lookup & reference › ROW84 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreeROW

ROW: automatic row numbers

Sinta is building a participant list for a small business (UMKM) training at the village hall. Every time someone cancels and their row is deleted, the hand-typed numbers skip. She wants numbers that adjust themselves.

Task

Fill A2:A9 with the numbers 1, 2, 3 and so on using ROW. The formula must be exactly the same on every row and must not refer to the number cell above it.

Cells to fillA2:A9

Required functionsROW

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
ROW() with no argument returns the row number of the cell the formula is in. In A2 it returns 2.
Hint 2
The list starts on row 2, but the numbering must start at 1. Subtract the difference.
Hint 3
Pattern: =ROW() - …, then copy it down to A9.

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

A2:A9

=ROW()-1

Write it in A2, then fill down to A9.

Explanation

=ROW()-1 gives 1 on row 2, 2 on row 3 and so on. When a row is deleted, the rows below move up and their numbers adjust. The amount you subtract depends on how many rows sit above the data: if the data starts on row 5, use ROW()-4. An alternative that stays correct even if you insert rows above the table is =ROWS($B$2:B2), which counts the rows from the start of the data down to this row.

Starting worksheet: Sheet1
Row	A	B	C
1	No	Participant	Business
2		Bu Endang	Cassava chips
3		Pak Darto	Motorbike repair
4		Mbak Tari	Tailoring
5		Wahyu	Ground coffee
6		Bu Ani	Catering
7		Om Joko	Laundry
8		Kak Nadia	Hand-drawn batik
9		Reza	T-shirt printing
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result