In Google Sheets, there are many way to fill automatically serial numbers in a column. In the above image as example shown, column A contain auto serial numbers, and the formula placed in cell A2 uses the combination of the SEQUENCE and COUNTA functions.
By this formula the result will be automatically fill the column A with serial numbers based on the entries in column B. As you enter the supplier name in column B, the formula creates a series of sequential numbers in the corresponding row of column A, creating an automatic list of serial number as much as you enter the suppliers name in the column B.
How Google Sheets Create Auto-Updating Serial Numbers by Formulas:
The SEQUENCE function combined with the COUNTA function in Google Sheets automatically generates sequential numbers based on non-empty cells. This dynamic setup is highly efficient, as it adjusts numbering automatically when rows are added or removed, making it ideal for maintaining accurate serial numbers in large or frequently changing datasets without manual updates. It’s a time-saving, error-free solution.
Automating Serial Numbers Guide, Step-by-Step:
Assuming your supplier name data starts from row 2 in column B (e.g., Supplier) and here you want that the serial numbers to be auto-filled starting from cell A2 (since the first row contains header like “sr. no.” and “supplier” and so on (or wherever you want the serial numbers start from), use the following formula in cell A2 or as your preference.
Syntax : SEQUENCE(rows, [columns], [start], [step])
SEQUENCE(2, 2, 1, 1)
Google Sheets Made Easy to Automatically Fill Serial Numbers:
For example:
=SEQUENCE(COUNTA(B2:B))
As in below image, here the COUNTA(B2:B) determines how many rows are contain data (in column B2 to downwards), and the SEQUENCE function automatically generates sequential numbers in column A based on that count. This ensure that the serial numbers match the number of non-empty cells in column B.
=SEQUENCE(COUNTA(B2:B))
In example, shown in above image, cell A2 initially displays the error “#NUM!” because the range B2 is empty. The COUNTA(B2:B) function counts the non-empty cells in column B, but when no data is present, the SEQUENCE function returns an error due to the lack of rows to generate serial numbers for.
However, once you enter a supplier name in cell B2, the error resolves, and cell A2 will automatically display serial number 1. This ensure that serial numbers appear as soon as data is entered in column B, as shown below.
=COUNTA(B2:B)
Here, the SEQUENCE function’s first argument, derived from the result of COUNTA(B2:B) (its count 3 (three) non-empty cells), is combined with the SEQUENCE function to generate a numbered sequence in column A.
This sequence starts from 1 and extends to 03 in a single column downwards 03 rows. The SEQUENCE function automatically adjusts the number of rows based on the number of entries in column B, as determined by the result of the COUNTA function. This ensures that serial numbers correspond dynamically to the filled rows in column B.
=SEQUENCE(COUNTA(B2:B))
In Google Sheets, the SEQUENCE() function is a dynamic tool used to generate an array of sequential numbers. It allow you to create a series of consecutive values, such as serial numbers, without manually entering them. This function is especially helpful for automatically filling rows or columns with numbers, saving time and effort when creating lists, tables, or data that require sequential numbering.
If you have a short data list, like in the example above, you can manually enter the serial numbers for simplicity. However, for larger data sets, it’s more efficient to use the SEQUENCE function in combination of COUNTA function, as explained earlier. This method automatically generates sequential numbers, saving you time and minimizing errors when dealing with longer lists or dynamically changing data.
Remember:
- Use this function to automatically generate serial numbers that adjust when data is added or deleted. This ensures the numbering always stays in sync.
- If you are working with a large dataset,
SEQUENCEeliminates the need to manually type or drag-fill serial numbers. It saves time and reduced errors. - Specially, if you delete any rows, the function will automatically adjust the numbering value, maintaining a clean and correct sequence without gaps.
- You can start your sequence at any number by specifying the starting points in the formula.
=SEQUENCE ( COUNTA ( B2 : B ), 1, 15 ), by this formula the serial numbers will be start from 15 to onwards.
