How to Number Rows in Google Sheets (Serial Numbering)

Google Sheets offers lots of tools to increase productivity. There are multiple ways to number rows in Google Sheets automatically. The quickest and most efficient of them is to use the ROW function.

Manually numbering rows or columns is ideal when the table size is small.

But what if the table contains thousands of rows?

It may take several hours to assign serial numbers to them manually.

In this article, let us discuss various methods to number rows or columns in Google Sheets. We will discuss multiple functions and built-in tools, so make sure to read the article till the end.

This how to number rows in Google Sheets guide is divided into the following sections. Click on the links to jump to that particular section:

Automatically number rows using the ROW function
Automatically number rows using the Fill Handle
Automatically number rows using the COUNTA function

Download the Example Google Sheets

Consider downloading the following Google Sheet, which contains spreadsheets that demonstrate adding serial numbers to rows. It will strengthen your knowledge of the functions and tools discussed in this article.

Click Here to Make A Copy!

It is okay if you have your own Google Sheet ready with a table where you need to number rows.

How to use the ROW function to number rows in Google Sheets automatically

This is the quickest method to add serial numbers to rows. It uses the built-in function called the ROW.

Before discussing the steps, let us quickly understand the ROW function.

Explained: The ROW Function in Google Sheets

The ROW is a lookup function that returns the row number of the given cell reference. It has the following general syntax,

=ROW([cell_reference])

The argument “cell reference” is self-explanatory. You can replace it with a cell reference or an array of row numbers.

This function is combined with other functions in Google Sheets to create powerful formulas.

Now, let us use this function to add serial numbers to rows.

We will consider the following table, which contains 20 rows, excluding the header row.

Let’s begin,

Select the entire first column
Right-click to view more options
Select “Insert 1 column left” from the popup
A new column will be added, as shown in the following image
Add the header as “Sr. No.” to this new column
Press “Enter” key
Select the cell “A2”
Type “=row”
Select the first option from the popup or press “Tab” on your keyboard
Provide the cell reference as “B2“
Complete the bracket using “)“
Type the minus operator, the “–” symbol using your keyboard
Type “1“
Press the “Enter” key

Our final formula will be as follows,

=ROW(B2)-1

As cell B2 represents the second row of the spreadsheet, we have made a special arrangement to our formula by subtracting 1 from the integer value returned by the ROW function.

It makes sure that the first number calculated by the entire formula mentioned above will be “1”.

Now, to apply this formula to the rest of the cell, simply hover over the Fill Handle that appears at the lower-rightmost corner of cell A2 after selecting it. You will see a “+” icon. Click on it and drag it to the end of the table. Refer to the following GIF.

Note that the serial numbers we created using the ROW function are dynamic. It means that if we sort our table from A to Z or vice versa, the numbering will stay intact.

Refer to the following GIF, where we have sorted the data alphabetically in descending order.

The numbers assigned to the rows don’t change.

This is the magic of using the ROW function, which is uncommon with the other two methods discussed in the following sections.

How to use the COUNTA function to number rows in Google Sheets automatically

This is the second method on this list, which uses various built-in functions such as the IF, ISBLANK, and COUNTA to assign serial numbers to rows.

As it doesn’t consider the blank cells while generating the serial numbers, the formula is helpful in cases where your rows contain empty cells.

Our task is to assign numbers to each row that contains values. Refer to the following image.

Here’s the formula,

=IF(ISBLANK(B2),"",COUNTA($B$2:B2))

Before we proceed further, let us quickly understand each function used in the above formula.

Explained: The COUNTA Function in Google Sheets

The COUNTA is a simple function in Google Sheets that counts the cells with numbers or letters. The general syntax of the function is as follows,

=COUNTA(value)

The “value” argument needs to be replaced with the cell reference or range.

Note that you can provide multiple cell references or ranges to this function.

Explained: The IF Function in Google Sheets

The IF is a conditional function in Google Sheets. It returns values based on a logical expression defined by the users.

The general syntax for the IF function is as follows,

=IF(logical_expression, value_if_true, value_if_false)

Each argument of the function needs to be replaced with the proper information,

“logical_expression” – It represents a criterion or condition determining the output. It returns TRUE or FALSE.
“value_if_true” – This argument displays the output if the logical expression argument returns the TRUE as an output.
“value_if_false” – Similar to the second argument, it displays the output if the logical expression argument returns the FALSE as an output.

It is quite a simple formula with self-explanatory arguments. Often, comparison operators are used to create logical expressions for the function.

Explained: The ISBLANK Function in Google Sheets

It is a simple function in Google Sheets that returns TRUE or FALSE based on the cell’s content.

Here’s the general syntax for the ISBLANK function,

=ISBLANK(value)

The “value” argument needs to be replaced with the cell reference value, which you wish to check is empty. The function then returns TRUE if the cell contains numbers or letters and FALSE if it doesn’t.

That’s all about the functions.

Now, let us use them to assign serial numbers to the rows which contain values.

Select the cell “A2“
Type “=if”
Select the first option from the popup or press “Tab” key
Type “isblank” in the place of the logical_expression argument of the IF function

(Note that as the ISBLANK is a function in Google Sheets, the program will automatically detect the same and display the popup)
Select the first option from the popup
Provide the cell reference as “B2” for the value argument of the ISBLANK function
Complete the bracket for the ISBLANK function using “)” on your keyboard
Press “,” to move to the next argument of the IF function
Now, replace the value_if_true argument with “”
(Note that it is double quotation marks with no space in between)
Press “,” to move to the next argument of the IF function
Type “counta“

(As the COUNTA is a function in Google Sheets, you will see a popup as shown in the above image)
Select the first option from the popup
Replace the value argument of the COUNTA function with cell range “$B$2:B2“
(Make sure to use double quotation marks while defining the cell range; otherwise, the function will not work correctly)
Complete the bracket for the COUNTA function using “)“
Complete the bracket for the IF function using “)“
Press “Enter” key

After following the above steps, the formula should look like below.

=IF(ISBLANK(B2),"",COUNTA($B$2:B2))

You can directly copy and paste the formula for your table, but make sure to replace the cell references wherever necessary.

To apply this formula for the rest of the cells, you need to hover over the Fill Handle that appears in the lower-rightmost corner of cell A2.

You will see the “+” icon. Click on it and drag it to the end of the table as shown at the end of the above GIF.

How to use the Fill Handle to number rows in Google Sheets automatically

This is the last method on this list.

In contrast to the other methods, it doesn’t use any formula. All you need to do is use the Fill Handle.

Here are the steps,

Click on the cell “A2”
Type “1” as the first-row number
Next, select the cell “A3” and type “2” as the second-row number
Now, select both cells A2 and A3
Hover over the Fill Handle of the cell A3 which appears on the lower-rightmost corner
You will see a “+” icon
Double-click on that “+” icon

Google Sheets will instantly assign serial numbers to each table row, as shown in the above GIF.

However, note that this method generates static numbers affected by operations such as sorting or filtering. Also, this method assigns a serial number to a blank row within the table.