Assigning numbers to selected rows

Occasional Contributor

I have a file with about 400 rows which I have set up to allow me to select using filters on any of the columns.  I need to assign sequential numbers, starting with "1",  to the selected rows.  I can't figure out how to number ONLY the selected rows without human intervention. 


In other words, if my filter selects the 40th through the 60th rows of my file, how can I number only those rows, starting with "1"?

4 Replies


Let's say that you want the sequence numbers in column A.

Select the visible (filtered) cells in column A.

In the following, I will assume that the first cell in the selection is A40.

Enter the formula




where 39 is the row number of the cell above the first cell in the selection.

Confirm the formula with Ctrl+Enter so that it is entered in all selected cells.

Unless I misunderstand, this appears to require human intervention to enter the address of the cell before the selected cell. I would like to programmatically identify the first selected row and start numbering there -- in advance I would not know which row would be the first selected.


Try this:


Row 1 contains the headers. The formula starts in A2.


I didn't describe the problem adequately, however the responses I received got me thinking of a solution which worked. Thanks for jogging my thought process out of the rut it was in>