Google Docs Won t Continue Numbering When Adding Row

If you keep and manage records in Google Sheets, having a column with serial numbers is something you may need.

While you can easily create a column with serial numbers by manually entering these, it's not an efficient way.

In this tutorial, I will show you a couple of ways to number rows in Google in such a way that it automatically add serial numbers.

This can easily be done using formulas and other simple techniques.

So let's get started!

• How to Number Rows in Google Sheets
• Number Rows Using Fill Handle
• Using the ROW Function to Number Rows
• Using the COUNTA Function
• Adding 1 to Previous Serial number

How to Number Rows in Google Sheets

In this tutorial,  I will show you four ways to add serial numbers in Google Sheets:

1. Using Fill Handle
2. Using the ROW function
3. Using the COUNTA Function
4. Adding 1 to Previous Serial number

The method you choose to numbers rows will depend on how your data is structured or how you want this to work.

For example, if there are blank rows in between and you only want to add a serial number to records that are not blank, you can not use all the methods (example covered later in this tutorial).

Number Rows Using Fill Handle

If you have a dataset without any blank rows, this is the easiest way to add serial numbers.

Suppose you have a dataset as shown below and you want to add serial numbers in column A.

Below are the steps to use the 'Fill handle' to fill down the serial numbers:

1. Insert a column to the left the Name column. To do this, right-click on any cell in column A and select 'Insert Column'
2. [Optional] Give the new column a heading and format it like other columns
3. In cell A2, enter 1
4. In cell A3, enter 2
5. Select both the cells
6. Place the cursor at the bottom-right corner of the selection. The cursor would change into a plus icon (this is called the Fill Handle)
7. Double-click using the mouse (or left-click and drag till you want the numbers)

The above steps would give you serial numbers in the additional column you inserted. You can then format this column to make it look better.

The above steps work when you have a continuous set of the dataset (i.e., there are no blank rows). In case there is a blank record the dataset, double-clicking on the fill handle will only fill down the numbers till the filled record before the blank row.

Note: The result you get from this method is static. If you add more records to the dataset, you will have to the fill-down again do this to cover the new rows.

Using the ROW Function to Number Rows

ROW function is Google Sheets gives you the row number of the cell reference you give to it. And if you don't give it any input argument, it will return the row number of the cell in which it is used.

You can use the ROW function (by adjusting it a little) to get the serial numbers in a column.

Suppose you have a dataset as shown below and you want to add serial numbers in column A.

Below are the steps to do this:

1. Insert a column to the left the Name column. To do this, right-click on any cell in column A and select 'Insert Column'
2. [Optional] Give the new column a heading
3. In cell A2, enter the formula: = ROW ( ) – 1
4. Copy and paste for all the cells where you want the serial number.

The above formula uses the ROW function to get the current row number and then subtracts 1 from it as we are starting from the second row onwards. In case you're starting from the first row, you can simply use the ROW function.

In case you only want to show the serial number for the records that are filled, you can use the below formula:

          =          IF          (          B2          <>          ""          ,          ROW          (          )          -          1          ,          ""          )        

You can use this formula on an expanded number of cells in the column (even if there are no records in it for now). As soon as you add a new record, the number would automatically appear.

The above uses an IF function to check whether there is any text in the adjacent cell or not. In case there is no text, it returns a blank, else it returns the row number.

The good thing about using the ROW function is that in case you delete a row from this dataset, the function would adjust to give you the correct row number.

Using the COUNTA Function

Another way you can number rows in Google Sheets is by using the COUNTA function.

The COUNTA function will count the number of non-empty cells adjacent cells and return a serial number only when the row is filled (unlike the ROW function method, which would still give a serial number if the row is empty).

Suppose you have a dataset as shown below and you only want to assign a row number (serial number) to a row if that row is filled.

The below formula can do this:

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

In the above formula, the IF function checks whether the adjacent cell is blank or not (using the ISBLANK function). If it's blank, the IF formula would return a blank and if it isn't, it will count all the filled rows in the specified range and give that number.

Note that I am using $B$2:B2 as the range where I have locked the first B2 (by having dollar sign before the column alphabet and row number) but not the second one. This allows me to expand the range when I copy it to other cells. For example, if I copy this to cell B5, it will expand and the range would become $B$2:B5

Adding 1 to Previous Serial number

This is a quick and simple way to add serial numbers to a column. It's best suited when you have no blank rows in the dataset.

Suppose you have a dataset as shown below where you want to insert serial numbers in column A.

Below are the steps to do this:

1. Enter 1 in cell A2
2. In cell A3, enter the formula = A2 + 1
3. Copy and paste this formula for all the cells in the column where you want the numbers

The above formula simply adds 1 to the number in the cell above it.

While this is a simple and straightforward method, there are a few drawbacks you must know before using this method:

• This would assign a number irrespective of whether there are blank rows or not.
• In case you delete a row, all the values below it would return a reference error (#REF!)
• When you insert a new row, it will not update the row number automatically. You will have to copy and paste the formula to all the cells in this new row and all the cells below it.

So these are four methods you can use to numbers rows in Google Sheets. You can choose any of these methods based on how your data is structured. In case you have blank rows in your data set, it's best to use the COUNTA method.

Hope you found this tutorial useful!

You may also like the following Google Sheets tutorials:

• Count the Number of Characters in a Cell in Google Sheets
• Count Cells IF NOT Blank (Non-Empty cells) in Google Sheets
• How to Delete Empty Rows in Google Sheets
• How to Apply a Formula to an Entire Column in Google Sheets
• How to Multiply in Google Sheets (Numbers, Cells or Columns)
• How to Divide in Google Sheets

hollawaydautly1938.blogspot.com

Source: https://spreadsheetpoint.com/number-rows-in-google-sheets/

Share this post