I have filtered my Excel data and now I want to number the rows. How do I do that?

Question

Basically all I want to do is to insert a new column after having filtered my data by a certain criterion, and then insert consecutive numbers into that column, one for each row. I.e., I have data like this in one column:

Armstrong, John

Beattie, Jane

Coombs, John

And I want a new column running next to it so it looks like:

1 Armstrong, John

2 Beattie, Jane

3 Coombs, John

I have tried inputting the first few numbers and then dragging down to fill the rest of the column but when I do that all of the numbers turn to 1 for some reason.

Answer 1

Okay I found the correct answer to this issue here

Here are the steps:

1. Filter your data.
2. Select the cells you want to add the numbering to.
3. Press F5.
4. Select Special.
5. Choose "Visible Cells Only" and press OK.

6. Now in the top row of your filtered data (just below the header) enter the following code:

   =MAX($"Your Column Letter"$1:"Your Column Letter"$"The current row for the filter - 1") + 1

   Ex:

   =MAX($A$1:A26)+1

   Which would be applied starting at cell A27.

7. Hold Ctrl and press enter.

Note this only works in a range, not in a table!