HomeGoogle DocsSpreadsheetAuto Serial Numbering in Google Sheets with Row Function

Auto Serial Numbering in Google Sheets with Row Function

Published on

Multiple Ways of Serial Numbering in Google Sheets:

As you may know, a standard format typically begins with a serial number column. To be more precise, this format may include columns such as serial number, description, client name, etc. The serial number column allows you to number rows in various ways. In this tutorial, we will explore auto-serial numbering in Google Sheets.

There are multiple ways one can adopt to insert serial numbers in Google Sheets. Before delving into dynamic methods for auto-numbering rows, it’s important to understand other available options that may be useful for beginners.

At the end of this post, I have also shared information on how to auto-increment alphabets as well as Roman Numerals in Google Sheets.

So, let us begin with different methods for auto serial numbering in Google Spreadsheet.

Standard format table with a serial number column in Google Sheets

Auto Serial Numbering Using Simple Formula

In the above example screenshot, our serial numbering starts from row #3, i.e., from cell A3.

First, enter 1 in cell A3. In cell A4, use the formula =A3+1 and then copy and paste this formula down to the cells below.

Serial numbers generated using a simple formula in Google Sheets

This method is commonly used for numbering in Google Sheets or Excel spreadsheets. It automatically adjusts row numbering when inserting or deleting rows in between.

Auto Row Numbering Using the Fill Handle

The fill handle behaves differently in Excel and Google Sheets.

For example, in Excel, you may enter 1 in cell A3 and 2 in cell A4. Then, select both cells and drag the fill handle down. This method may not work in Google Sheets.

Serial numbering generated using the fill handle in Google Sheets

In Google Sheets, you do not need to select both cells. Just go to cell A4, then drag the fill handle down.

However, one thing is common in both. If you have values in the adjoining columns, left or right of the serial number column, double-clicking on the fill handle will fill the row numbers. In Excel, select both cells; in Google Sheets, only select the second cell.

Automatically Put Serial Number in Google Sheets Using the Row Function

Using the ROW function in Google Sheets

Here, I’ve used the ROW function. This function can return the row number of a specified cell.

Syntax:

ROW([cell_reference])

Example:

=ROW(A3) // returns 3

The following formula will return the row number of the formula-applied cell.

=ROW()

Now, back to our topic:

To get row numbering from row #3 (cell A3) downwards, enter the following formula in cell A3 and drag the fill handle down:

=ROW(A3)-2

The ROW function applied in cell A3 is =ROW(A3), which will return the row number 3 in the active cell. However, we need our serial number to start from 1. So, I have deducted 2 from it.

When you delete any row in between, in this case also, the serial number will automatically adjust.

If you want to start numbering from row #5, you should use =ROW(A5)-4. I hope it’s clear to you now.

Dynamic Auto Serial Numbering in Google Sheets

The ultimate way of auto serial numbering in Google Sheets!

The best option to automatically fill the serial numbers in Google Sheets is to use an array formula. You can either use the ROW function or the SEQUENCE function.

Row-Based:

If you want to get serial numbers 1 to 10 from cells A3 to A12, enter the following formula in cell A3:

=ArrayFormula(ROW(A3:A12)-2)

You only need to apply the formula in cell A3. No need to copy and paste it down. It will automatically number the cells below.

The ROW function with the support of the ArrayFormula returns the row numbers 3 to 12. We subtracted 2 from them to convert those outputs to 1 to 10.

Sequence-Based:

Another way to auto serial numbering is to use the SEQUENCE function. It’s an array formula. You just need to specify the number of sequences you want.

To get sequence numbers 1 to 10, use the following formula:

=SEQUENCE(10)

Conclusion

Although generating auto serial numbers using the SEQUENCE function is simple, the majority of Sheets’ users depend on the ROW function. This is because the SEQUENCE function is relatively new.

The purpose of the ROW function is to return the row numbers. However, we can also use it for auto-serial numbering. On the other hand, the SEQUENCE function is a dedicated function for returning sequence numbers with more features such as specifying the starting number of the sequence and step value. Additionally, it can return results in a 2D array.

Resources

  1. How to Use Roman Numbers in Google Sheets (generate Roman Numerals as Serial Numbers)
  2. How to Autofill Alphabets in Google Sheets
  3. Group Wise Serial Numbering in Google Sheets
  4. Skip Blank Rows in Sequential Numbering in Google Sheets
  5. Assign the Same Sequential Numbers to Duplicates in a List in Google Sheets
  6. Insert Sequential Numbers Skipping Hidden | Filtered Rows in Google Sheets
  7. Backward Sequence Numbering in Google Sheets
  8. Sequence Numbering in Merged Cells In Google Sheets
  9. Adding N Blank Rows to SEQUENCE Results in Google Sheets
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

17 COMMENTS

  1. Hi – I will try to explain it as best as I can. Do you know how I can do this in google sheets?

    When I write something in B3, I want the number “1” to appear in A3. When I type something in B7, I want the number “2” to appears in A7. When I write something in B20, I want the number “3” to appear in A20, and so forth.

    So essentially I want the number sequence to continue based on which row I type information into.

    Is this possible?!

  2. Hello – I am trying to enter “Payroll 1” into cell A17; “Payroll 2” into cell A33 and so-on for multiple sheets within the same workbook (there are dates in rows/cells between). I’d like for the numbers after the word ‘Payroll’ to increment by 1, by referencing the previous ‘Payroll X’ cell. I cannot seem to figure out a formula to make this work, would you please be able to help?

    • Hi, Adrienne,

      I assume you have the string “Payroll 1” in cell A17. Then you want “Payroll 2” in cell A33 but between A17 and A33 there are values.

      In cell A17 manually enter the string “Payroll 1”. In cell A33, insert the below formula.

      ="Payroll "&countif($A$1:A32,"Payroll*")+1

      Then copy-paste this formula in any cells down to increment the number to “Payroll 3” and so on.

  3. Hi Prashanth,

    I want to fill in Week 1, Week 2, Week 3, up to Week 13, and then repeat four times.

    That’s because each quarter has 13 weeks (based on a project management model), and four times represent four quarters of the year.

      • Sorry I didn’t get back to you sooner. What if I want to repeat that Week 1-Week 13 several times, so that I have 52 rows, as in 52 weeks in a year?

        • Hi, Trang,

          =ArrayFormula(transpose(split(textjoin("|",1,if(row(A1:A3),
          textjoin("|",1,"Week "&sequence(13,1))),),"|")))

          In this formula (13,1) for Week # 1 to 13.

          The formula part (A1:A3) is for repeating the week # 1 to 13, 3 times.

          • Thank you, Prashanth for taking the time to help me. What a monster formula. After half an hour, I finally understand it.

  4. The downside of using the Row() Function is that if you add a Data filter and use sorting on that spreadsheet, your ID’s column will not work anymore. The numbers stay the same, even though the order of your records has changed.

    Any solution for that?

    • Hi, Stece,

      Yes! When using the auto serial numbers, the issue with filtering/sorting is there. Because the generated numbers have no direct relationship with the data in adjoining columns. If it was not the case, I mean if the array formula has some connection, then there is a workaround – how to Stop Array Formula Messing up in Sorting in Google Sheets.

      So, the workaround is to go for Query function to filter as well as sort the data in another tab.

      For example B1:B contains auto serial numbers generated using the ROW or SEQUENCE function and C1:C contains some product names (in ‘Sheet1’).

      To sort the product names in ‘Sheet2’, use the Query as below.

      =query(Sheet1!B1:C,"where C is not null order by C asc,1")

      To include filter, like sort the column C in Asc order and then filter the items “product 1”, “product 2” and “product 3”, use the below Query.

      =query(Sheet1!B1:C,"where C matches 'product 1|product 2|product 3' and C is not null order by C asc,1")

      Note: Filtering is case sensitive.

      To learn Query, open my Google Sheets Function Guide. To master Query filtering, read this guide – The Alternative to SQL IN Operator in Google Sheets Query (Also Not IN).

  5. I achieved the same with a more convoluted formula.

    =IF(ISBLANK(B2);"";ARRAYFORMULA(ARRAY_CONSTRAIN(row(B2:B);
    COUNTA(FILTER(B2:B;LEN(TRIM(B2:B))>0));1)-1))

    You made good use of the logic operators. I didn’t know it worked that way. Thanks!

LEAVE A REPLY

Please enter your comment!
Please enter your name here