How to Add Row Number to an Excel Table

We are dealing with Excel tables every day. They are one of the most useful things in Excel and can present our valuable data in a fine tunned way.

Users can often forget to add row numbers to a table at the beginning of its creation, and they can find themselves in a situation where they need to add it later. We will show how to do that in the example below.

Add Row Numbers with Fill

For our example, let us say that we have a random list of NBA players with their name, position, team, and draft position:

Graphical user interface, table

Description automatically generated

Let us say that we want to add numbers to this random table. We will insert one column as a column A, and then we will input number 1 in cell A2, and number 2 in cell A3.

Graphical user interface, table

Description automatically generated

We will then drag our pointer to the end of our table. Excel will automatically notice a pattern and will input the other rows:

Graphical user interface, table

Description automatically generated

It is worth mentioning that this works only if you input two numbers, meaning 1 and 2. If you put only number 1, this will not work. Also, if we have a large set of data, and we do not want to drag the numbers, we can hover over the right square that appears at the bottom-right of our selection and click on the plus icon.

Excel is usually great in recognizing all kinds of patterns. For example, if you input 1 and 3 first, Excel will automatically input all the odd numbers in our table:

Table

Description automatically generated

We can also use the Fill Series option, which gives us a little more control over our data. We will again input number 1 in the cell A2 and go to Home >> Editing >> Series:

Graphical user interface, application, Word

Description automatically generated

On a pop-up that appears, we will do the following: choose Columns in series, give a Step value of 1 and Stop value of 19.

Graphical user interface, application, Word

Description automatically generated

The downside of this approach is that we need to know the number of our rows.

Add Row Numbers with Row Formula

There is another way to add the numbers to a table, and that is with the formula. We will input the following formula now in cell A2:

=ROW(B2)-1

Formula ROW returns the number of our row. Since basically, Steven Adams is the first player in our table, we do not want him to be number 2, which is the number of the row he is at. So, we also subtract one number from our original number, to get the result that we want.

We will click on the plus icon at the bottom right when we hover over cell A1, and then the whole list will be populated:

Graphical user interface, table

Description automatically generated

Add Row Numbers with the COUNTA Function

Let us now delete a couple of entries in column E:

Table

Description automatically generated

If we want to insert row numbers for column E, we will input the following formula in cell F2:

=IF(ISBLANK(E2),"",COUNTA($E$2:E2))

The IF function checks if the cell next to the cell in column E is empty or not. If it’s empty, it returns a blank. If not, it gives us the count of all the filled cells till that cell.

When we drag this formula till the end, we will have the following results:

Graphical user interface, table

Description automatically generated