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-tuned 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:
Let us say that we want to add numbers to this random table. We will insert one column as column A, and then we will input number 1 in cell A2, and number 2 in cell A3.
We will then drag our pointer to the end of our table. Excel will automatically notice a pattern and will input the other rows:
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 at 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:
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:
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.
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:
|
1 |
=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:
Add Row Numbers with the COUNTA Function
Let us now delete a couple of entries in column E:
If we want to insert row numbers for column E, we will input the following formula in cell F2:
|
1 |
=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:
