How to Number Rows in Excel (In 8 Easy Ways)

Numbering rows in Excel. Doesn’t sound like a tough feat, right? Don’t worry, it really isn’t. Why would you need to number rows? When handling any database, it can always do with organizing and the organizing may come in the form of alphabetical order, chronological order, or serial numbering depending on the type of data.

You’ll find yourself behind many such common tasks when handling data in Excel and not only would it serve best to expedite such tasks, it would also help to pick the most suitable method.

Today’s tutorial can help you with that. We are learning how to number rows in Excel and have a handful of techniques to achieve that. Quickly listing them down, we will number rows using the Fill Handle, Fill Series, simple addition, the ROW, COUNTA, OFFSET, and SUBTOTAL functions, and an Excel table. The anomalies you can face while numbering rows is gaps in the data and filtering data and this will be dealt with the COUNTA and SUBTOTAL functions respectively.

We can get started after seeing an example of what we’ll be working with.

How to Number Rows in Excel

Example

You’ll most probably need to number rows in Excel for serial numbering in your dataset so here’s an example of where it can be applied:

How to Number Rows in Excel

We have an employee database of a school which can do with some sequential numbering. Our aim is to serially number this dataset in column B.

With some methods, you will get static numbering of rows and dynamic numbering with the rest. If that is important to you, we’ve mentioned which method delivers which results with the subheadings.

Let’s get numbering!

Method #1 – Using Fill Handle

Static numbering

Let’s use the simplest method first which requires no functions or fancy features. We use the Fill Handle. The Fill Handle is the tiny square that appears at the bottom-right corner of the selected cell(s). The Fill Handle recognizes the formula or pattern contained in the selected cell(s) and copies them down to the extended range. With this feature, we can number the rows in our dataset. See below how it’s done:

  • In the column where you want the serial numbers, enter 1 and 2 in the first and second rows:
  • Using Fill Handle
  • This will help the Fill Handle pick up on the pattern.
  • Select these two cells containing the numbers 1 and 2. The Fill Handle will appear in the bottom corner.
  • Using Fill Handle
  • Hover the cursor on the Fill Handle until the cursor changes to a cross.
  • Using Fill Handle
  • Now click and drag the Fill Handle to where you want the numbered rows. We want the rows numbered to 10 so we’ll drag the handle down to B12. The callout bubble will display the value for the target cell as you click and drag.

If you want to number the entire dataset’s rows, double-click the Fill Handle and it will number all the rows of the dataset that have the adjacent column filled.

Using Fill Handle

When you release the left mouse button, the cells will be auto-filled with the numbered rows using the Fill Handle:

Using Fill Handle

Method #2 – Using Fill Series Option

Static numbering

Here’s another feature that acts as a filler. To fill the rows with serial numbers, you can give Fill Series a go. Given its few options, you’ll find it quite handy for filling in numbers and dates in the specified steps. For now, we only need sequential numbering so we’ll head onto the steps right away:

  • Enter the number 1 in the first row of the column you want numbered.
  • Select the cell so that Fill Series can create the series to fill the column based on this number.
  • Select the Fill icon in the Editing section of the Home tab and then select the Series option from the menu.
  • Using Fill Series Option
  • The Series dialog box will be launched.
  • In the dialog box make the following selections:
    • Select the Columns radio button in the Series in section
    • Select the Linear radio button in the Type
    • Enter the Step value and Stop value for your number series.
  • For us, that would be 1 and 10.
  • The Step value is the number by which you want the numbers to increase. E.g. if we enter 2 here, the numbers in the series would go 1, 3, 5….. Since we want regular numbering, we have entered 1 as the Step value.
  • Stop value is the number where you want the series to end.
  • Using Fill Series Option
  • Click on the OK button when done.

With these steps, the column will be filled with the given number series:

Using Fill Series Option

Method #3 – Incrementing Previous Row Number by 1

Static numbering

Now we move on to the formulas for numbering rows in Excel and it only makes sense to start with the simplest one first. By adding 1 to the previous row number, we can create a sequence of numbers. Here’s what you need to do if you choose this method:

  • Start by entering "1" in the first cell where the serial numbers will be.
  • In the next cell of the column, add 1 to the previous cell using this formula:
  • =B3+1

    In this formula, B3 will be the cell containing the number 1. Adding 1 to B3 will result in the number 2. And this is how it will keep going. The next cell will be B4+1 which will result in 3, so on and so forth. In this way, the numbers will keep increasing serially.Incrementing Previous Row Number by 1

  • Now extend the formula to where you want the last number.
  • With this plain arithmetic formula, the rows will be numbered up to 10:
Incrementing Previous Row Number by 1

Method #4 – Using ROW Function

Dynamic numbering

And now we will number rows using a simple function, the ROW function. The ROW function returns the row number of a referred cell. So, no matter where we are on the active worksheet, if you refer a certain cell, the ROW function would return the specified cell’s row number. With the steps below, we’ll show you how we make the ROW function work for returning numbered rows:

=ROW()-2

The ROW function has been used on its own to return the number of the row the formula is entered in and then we deduct that figure by 2. Why? Our target cell is B3 so =ROW() would return 3. To make the ROW function return 1 as the first number, we have deducted 2 in the formula. You will need to adjust this value according to the row your target cell is in.

Using ROW Function

As the formula is copied down, it will return the row number and then 2 will be subtracted. E.g. in the next instance, the row number is 4. 4-2 = 2. The next row number will be 5. 5-2 = 3 and so on.

We have chosen this formula since our numbering was starting from B3. If you’re already starting from row 1, you can enter the formula as:

=ROW()

This formula will return the row number of the cell the formula is entered in.

Note: Both the ROW formulas suggested above will bring about dynamic results that recalculate with additions and deletions.

Method #5 – Using COUNTA Function

Dynamic numbering

Going good up until now. Rows are being numbered, the morning birds are taking their cue and the sun is rising somewhere. And then you realize that as opposed to a continuous dataset, your data has some blank rows that don’t need numbering. Challenge accepted; we have the COUNTA function at our disposal.

The COUNTA function counts the number of cells in a range that are not empty. COUNTA will count the non-empty cells in the adjacent column and, with the IF and ISBLANK functions, will return nothing if the adjacent cell is blank. Sounds like a plan. Let’s see all the works. we begin with this formula:

=IF(ISBLANK(C3),"",COUNTA($C$3:C3))

As per the IF function, C3 will be checked by the ISBLANK function as to whether it’s a blank cell or not. If this tests true, the IF function will return an empty text string "".

If the cell is not blank, then the COUNTA function will start counting the non-blank cells from $C$3 (mentioning the number as an absolute reference locks the cell in the formula so that as the formula is copied down, the reference remains the same) and return the count as the answer of the formula.

In the first instance, the count from C3 only makes 1 cell so 1 is the result.

Using COUNTA Function

In the next instance, COUNT counts C3 and C4 and therefore returns 2. In the third instance, the adjacent cell i.e. C5 is blank so this row has not been numbered.

Note: The results of using this formula are dynamic; with any change of addition or deletion of data, the numbering will recalculate and adjust accordingly.

Method #6 – Using OFFSET Function

Dynamic numbering

Rows in Excel can also be numbered using the OFFSET function. It’s a slightly “off” the norm method but a method nonetheless and it requires you to start the column without a header; you’ll see why in a while.

The OFFSET function returns the value of the cell which is the given numbers of rows and columns from the specified cell. We will use this concept to then add 1 to each result to create serial numbering. Below is the demonstration with the formula:

=OFFSET(B3,-1,0)+1

Now you’ll see why a blank column header was a prerequisite. Let’s understand the path in the formula. The cell specified is B3 itself. From there, OFFSET trails to -1 rows. This means B2. now in the third parameter of the formula, from B2 we need to shift to 0 columns which still leads to B2.

This means that using the OFFSET function, we will be referring to the previous cell in the same column. In the last bit of the formula, we have +1 which adds the number 1 to the value of B2. Since B2 is blank, 0+1 = 1. 1 is the outcome here. This is why we needed the header to be blank.

Using OFFSET Function

In the next instance, OFFSET will lead to B3 and add 1 to B3’s value. 1+1 = 2. That is how the rest of the rows will be numbered in succession.

Once you are done, you can copy and paste the values over themselves and add the column header if you so wish. The values are pasted back so that they are not formula-reliant and won’t cause errors.

Using OFFSET Function

Note: As long as the column header is absent, the results of this formula are dynamic. If you wish to add the column header, you will have to paste back the values or you can choose another method if want to retain the column header and the dynamic numbering.

Method #7 – Using SUBTOTAL Function

Dynamic numbering

And again, things were looking bright until you were hit with another requirement. You need to filter data but that would also filter the serial numbers. Now that’s a problem but not a problem we can’t work around. Enter SUBTOTAL function.

The SUBTOTAL function returns a subtotal in a dataset. While we will be using the COUNTA function via SUBTOTAL, SUBTOTAL will help to dynamically recalculate the row numbers as the data is filtered. Want to see how that works? We’ve got the formula to number rows with the SUBTOTAL function right ahead:

=SUBTOTAL(3,$C$3:C3)

The SUBTOTAL function presents a list of functions to choose from in the Formula AutoComplete. This parameter will determine what kind of subtotal you want to enter.

Using SUBTOTAL Function

After selecting the function, the next input is the cell reference. As shown in the COUNTA function section above, we will enter $C$3:C3 as the range so that the count always begins from $C$3. As the formula is filled downward, the count will continue up until the target cell.

Using SUBTOTAL Function

Now try filtering your data.

Using SUBTOTAL Function

The row numbers will be recalculated with the filtered data serially numbered:

Using SUBTOTAL Function

With the new serial number selected, we can note in the formula that the count for non-blank cells has started from $C$3 to C4 instead of C3. In this way, the count will begin from $C$3 and jump directly to the first filtered cell in the C column.

Method #8 – Creating Calculated Column in Excel Table

Dynamic numbering

Last but not least, let’s table it! An Excel table talks the talk and walks the walk and there are many convenient features it offers. The one helping us today is a calculated column; enter one formula and you’ll have numbered rows. Excel tables are known for being dynamic in their very nature and a calculated column also provides that ease. Nothing short of Excel magic. The complete tricks (read steps) to do this are as follows:

  • Select any cell in the dataset. This will help Excel identify the range to create the Excel table.
  • In the Home tab, go to the Styles section and click on the Format as a Table Select the table style from the menu or create your own.
  • Creating Calculated Column in Excel Table
  • Now a small dialog box will appear confirming the identified range of the intended Excel table. Correct the range if required.
  • If your table has headers, leave the My table has headers checkbox checked.
  • Then click on the OK button to close the dialog box and apply the table.
  • Creating Calculated Column in Excel Table
  • An Excel table will be created on the provided range:
  • Creating Calculated Column in Excel Table
  • Now, enter this formula in any cell in the column where you want the numbers:
=ROW()-ROW(Table1[#Headers])

As a calculated column, when the formula is entered, all the rows will automatically be serially numbered:

Creating Calculated Column in Excel Table

And that’s your job done!

And that’s our tutorial done! The solutions may be numbered but so are the problems and today you saw how to tackle numbering rows in Excel in many different ways.

Filtering data and dealing with gaps in the data are two hitches you might face when numbering rows but today you've learned how to breeze through them. We’re up for making other hitches easy, breezy and we’re on it too so we’ll see you around another Excel trick!

About Mehwish Javaid

Mehwish, an ACCA-qualified professional, transitioned from an audit trainee to an Excel specialist. With a foundation in financial auditing, her 4+ years of Excel expertise, showcased as a Content Specialist at ExcelTrick, bridges her auditing background with advanced spreadsheet skills. Read more...