If you are working on Google Sheets to manage data then you may require the column that has serial numbers.
Although manually inserting serial numbers into a column is a simple method, it is not a very efficient one.
How to Number Rows in Google Sheets?
Here we will cover four methods through which we can number rows in Google Sheets.
- By using the Row function
- By using the Fill handle
- By using the COUNTA function
- By adding 1 to the previous serial number
The usage of each method depends on how your data is organized in Google Sheets.
-
Method 1: Using the ROW Function to Automatically Number Rows in Google Sheets
We’ll apply numerical values to a dataset using the Row function. The example that follows will guide you through the use of this useful feature.
Step 1: Select an empty cell in your spreadsheet
Select the cell in which you want a number sequence near the dataset as I have selected A2.
Step 2: Enter the formula = ROW() [Operator] [Value]
In the empty cell write the formula in the format of ROW() [Operator] [Value]
The formula contains three parts:
Row(): It gives the row number of the specific cell
[Operator]: The operator will have either – or + which will increase or decrease the number that came after these symbols.
[value]: This value will define from which value our row count will start.
In our example I am writing ROW()-1 as we want to have starting number 1.
Press enter to get the results.
Step 3: Click and hold the blue square in the corner of the cell and drag it down to fill the rest of the data range.
To apply this function to all the rows drag the blue box from the bottom right corner. As I have dragged from A2 to A7. In this way, row number will be added from A2 to A7.
When you will leave the blue box number will be added as
Step 4: Adding an additional text value to Row Numbers
If someone wants to add something additional to the row number such as a name or extra data we can use the following formula for that:
=”TEXT VALUE”ROW() [Operator] [Value]
Let’s explain the formula:
=TEXT VALUE: This is the text that we will add before our numerical value of the row number.
Row(): It gives the row number of the specific cell
[Operator]: The operator will have either – or + which will increase or decrease the number that came after these symbols.
[value]: This value will define from which value our row count will start.
In our example, I am adding a Number as additional text.
“number”&ROW()-1
Press enter to get the result.
To apply this function to all the rows drag the blue box from the bottom right corner. As I have dragged from A2 to A7.
When you will leave the blue box number will be added as
-
Method 2: Using Fill Handle
If your data has no blank rows then this is the easiest method to add row numbers.
Suppose you have an empty column before your data set where you can add a row number as in my data set it is column A. If you don’t have the empty column you can insert one column to the left.
Give this Column a heading.
In the cell, A2 write 1 and A3 write 2.
Select both cells and add a blue box from the bottom right corner. When you place the cursor at that position it will show you a plus sign this is called the fill handle.
Drag it down and serial numbers will be added to column A.
This method is best for the dataset that has no blank rows.
-
Method 3: Using the COUNTA Function
When the row is filled, the COUNTA function counts the number of adjacent non-empty cells and returns a serial number. It will not give the number to the blank rows.
Suppose your dataset has also many blank rows and you want to give a number to the rows that have data only.
Use this function
=IF(ISBLANK(B2),””,COUNTA($B$2:B2))
Use of the ISBLANK function, the IF function in the above formula determines if the neighboring cell is blank or not; if it is, it returns a blank; if it is not, it counts all the filled rows in the range and returns that number.
-
Method 4: Adding 1 to the Previous Serial number
This method of adding serial numbers to a column is quick and easy. When there are no blank rows in the dataset, it works well.
Assume you wish to add serial numbers to column A of the dataset that is displayed below.
The steps to use this method are listed below:
- Put 1 into cell A2.
- Enter the formula =A2+1 in cell A3.
- For each cell in the column where you want the numbers, copy and paste this formula.
Although this is an easy procedure, there are a few things you should be familiar with before applying it:
- This would give a number to the row whether or not there are blank rows.
- All values below a deleted row would return a reference error (#REF!) if the row were deleted.
- The row number will not be immediately updated when you insert a new row. The formula must be copied and pasted into each cell in this new row as well as every cell below it.
Number Cells Using SEQUENCE Formula
Sequence formula can immediately add number to multiple rows but the starting number will always be 1.
To use this method follow the steps given below:
Select the empty cell and enter the sequence formula as
=SEQUENCE(number)
As I am writing the formula for y spreadsheet =SEQUENCE(7)
It will give the number To ROW 1 to 7 in a sequenced manner.
FAQ’s
How to do numbering in Google Sheets?
Using row Function or other methods that are explained in this article you can number rows in Google Sheets.
Can you automatically number rows in Google Sheets?
Yes, using the Row function you can automatically number rows in Google Sheets.
Row Function:
=ROW() [Operator] [Value]
What is the formula for row number in Google Sheets?
The Formulas are:
1. Row Function:
=ROW() [Operator] [Value]
2. Sequence Function
=SEQUENCE(number)
With over two decades of experience in writing about Microsoft Excel, Google Sheets, and various other spreadsheet tools, Muhammad Nadeem Salam is your go-to expert for all things data. Since 2004, he has been passionately sharing his knowledge and insights through engaging and informative blog posts, helping countless readers unlock the full potential of their spreadsheet tools.
