Auto Serial Numbering in Google Sheets with Row Function

0
165
Auto Serial Numbering in Google Sheets

Multiple Ways of Serial Numbering in Google Sheets

As you know, a standard format starts with serial number. To more precise it will be like serial number, description, client name etc. Under the serial number, you can number rows in different ways. Here we can learn about auto serial numbering in Google Sheets.

fill serial number automatically

There are multiple ways one can put serial number in Google Sheets. Before going to tell you how to auto number rows in Google Sheets in dynamic ways, I should tell you other available options that may be useful for beginners. So let us begin with different auto serial numbering in Google Spreadsheet.

Auto Serial Numbering Using Simple Formula

put serial number using simple formula

In the above example, our serial numbering starts from row A3. Here in this case, in Cell A4 use the formula “=A3+1” and then copy and paste the formula to the cells down. See the above screenshot.

This is the commonly using numbering method in Google or Excel spreadsheets. It can auto adjust rows numbering, when insert or delete rows in between.

Auto Row Numbering Using the Fill Handle

Sorry! Unlike Excel, Google Sheets fill handling behaves differently. You can’t use fill handle to auto fill number in Google Sheets this way. It works perfectly in Excel. The below screenshot applicable only in Excel.

serial numbering using fill handle in Google Sheets

Here what you want to do is manually enter first two serial numbers and then drag the fill handle to down. As already told, it’s applicable to Excel only.

Automatically Put Serial Number in Google Sheets Using the Row Function

row function use in Google Sheets

Here I’ve used the ROW function. As per the example our row numbering to be started from row number 3. The row number function applied in Cell A3 is “=ROW(A3). It will return the row number three in the active cell. But we need our serial number to start from 1. So we just put a “-2” in the formula. Then you can either copy and paste the formula to down or drag the fill handle down. When you delete any row in between, in this case also, the serial number will automatically get adjusted.

Dynamic Auto Serial Numbering in Google Sheets

The ultimate way of auto serial numbering in Google Sheets!

This is the best option to automatically fill serial number in Google sheets. Here we can use a kind of formula which is a combination of IF, ArrayFormula and ROW function. First see the below formula in Cell A3. It’s as per our sample screenshots above.

=ArrayFormula(if(B3:B<>””,row(A3:A)-2,””))

I will explain you how this formula works and how you can use it in your spreadsheets.

As per the above example, you just need to apply the formula in Cell A3. No need to copy and paste It down. It will automatically do the numbering to the cells down. How?

Details:

  1. The formula “row(A3)” returns the cell number in A3, i.e. 3. We can use a “-2” to make it 1. When you want to use infinitive rows you should use this formula as “row(A3:A)-2”.
  2. When you want to auto fill serial numbers, you should know how to limit the numbering to a specific number of cells. We did it by using the IF logical function in Google sheets here. We have customer name under column B. If column B has no value, then the above formula won’t put number to the corresponding cell under Column A.
  3. Now the array function. Without the array formula we can’t automatically expand the formula to the below cells.

Now how to use this formula for your needs and that is important. See few more formulas below.

When you want your serial number to start from Column H2 and your rest of the data on the right side. Use this formula in cell H2.

=ArrayFormula(if(I2:I<>“”,row(H2:H)1,“”))

Now another formula in Cell J3.

=ArrayFormula(if(K3:K<>””,row(J3:J)-2,””))

Here the serial numbering will start from cell J3. You can use the above formula to automatically fill serial numbers in any column. Just change the cell reference in the formula as per your data.

LEAVE A REPLY

Please enter your comment!
Please enter your name here