ExcelInsider Team
Adding serial numbers in Google Sheets is crucial for organizing data and keeping track of dataset entries. It improves data readability and helps maintain order, especially in large datasets. Google Sheets allows users to add serial numbers in multiple ways.
To add serial numbers in Google Sheets, follow the steps below:
➤ Open the spreadsheet file.
➤ Select the first cell of the column where you want the serial number to start and type “1”.
➤ Again, select the cell directly below and type “2”.
➤ Now, select both cells and drag the fill handle to auto-fill the column with a sequence of serial numbers.
In this article, we will learn 5 easy methods of adding serial numbers to datasets in Google Sheets.
Add Serial Number Using the Fill Handle Tool
This is the simplest and easiest method of adding serial numbers to datasets in Google Sheets. This method works best if you are working with moderately sized datasets.
In the sample dataset, we have information about Item Code, Product Name and Category in Columns B, C and D, respectively. We have to fill the SL No column with a sequence of serial numbers by using the Fill handle. Follow the steps below.
Steps:
➤ Select Cell A2 and type the number “1”.
➤ Again, select Cell A3 and type “2”.
➤ Now, highlight both Cells A2 and A3, then drag the fill handle downwards to automatically fill the rest of the columns with serial numbers.
Add Serial Number Using Incremental Addition
This is another simple and effective method for generating serial numbers in your dataset. This method is ideal for users who want to avoid complex formulas but still want a quick and accurate way to add serial numbers. We will work with the same dataset and fill Column A using the Incremental Addition method.
Steps:
➤ Select the Cell A2 and type 1.
➤ Then, select Cell A3 and put the formula:
=A2+1
Note:
It is a simple formula that generates a serial number by taking the value of the previous cell (A2) and adding 1 to it.
➤ Press Enter and drag Cell A3 to fill all cells of Column A with the serial number.
Add Serial Number Using the ROW Function
The ROW function in Google Sheets is a powerful tool for automatically generating serial numbers. By returning the row number of a cell, it eliminates the need to manually type in serial numbers. We will work with the same dataset and generate serial numbers in Column A, this time using the ROW function.
Steps:
➤ Select Cell A2 and put the formula:
=ROW()-1
Note:
The ROW() part of the formula returns the row number of the cell. In our case, Cell A2 returns 2 because it is in the second row. -1 part of the formula subtracts 1 from that value. So, =ROW()-1 in Cell A2 gives us 1, which acts as the starting serial number.
➤ Press Enter, and you should see the number “1” being displayed.
➤ Now, highlight Cell A2 and drag it downward to fill Column A with serial numbers.
Add Serial Number Using the SEQUENCE Function
The SEQUENCE function is another powerful tool in Google Sheets used to generate serial numbers. The biggest advantage of using the SEQUENCE function is that you can generate a list of sequential numbers in a single step without needing to drag cells. We will again work with the same dataset, and by using the SEQUENCE function, we will fill the SL No column.
Steps:
➤ Select Cell A2 and type the formula:
=SEQUENCE(10,1,1,1)
Note:
In the formula,
➧ 10 depicts the number of rows that need to be filled. In our case, we want to generate serial numbers for 10 rows, so the value is set to 10.
➧ 1 indicates the number of columns. Since we want the sequence in a single column, it is set to 1.
➧ The third 1 represents the starting number of the sequence; we start at 1.
➧ And finally, the fourth 1 represents the step value. In our case, it is set to 1, so the serial numbers will increase by 1 at a time.
➤ Press Enter. You should now have Column A filled with serial numbers.
Add Serial Number by Combining ARRAYFORMULA and ROW Functions
ARRAYFORMULA in Google Sheets is an effective function that allows you to apply a formula across an entire range of cells at once, without needing to enter it individually in each cell. And the ROW function returns the row number of a given cell or range. By combining these two functions, you can quickly generate serial numbers for a dataset. We will use this method to generate serial numbers in Column A for the given dataset.
Steps:
➤ Select Cell A2 and put the formula:
=ARRAYFORMULA(ROW(A2:A11)-ROW(A2)+1)
Note:
In the formula, ROW(A2:A11) returns the number of rows from Cell A2 to A11. ROW(A2)+1 returns the row number of the first cell, which is 2 and adds 1 to it. And the ARRAYFORMULA function helps to fill Column A with the generated serial number at once, without needing to drag cells manually.
➤ Press Enter, and you should now see serial numbers being automatically generated in Column A.
Frequently Asked Questions
Which Method Should I Use to Generate Serial Numbers for Large Datasets?
If you are working with large datasets, SEQUENCE and ARRAYFORMULA with the ROW function are the most efficient methods to use. They can generate serial numbers automatically without needing to fill in cells manually.
Can I Start Serial Numbers With Any Number Other Than 1?
Yes, you can start generating serial numbers with any numbers you desire. For instance, if you want your serial number to start with 100, you can use the formula: =SEQUENCE(10,1,100,1).
Which Method Should I Use Between SEQUENCE and ARRAYFORMULA with ROWS function?
Use the SEQUENCE function if you are working with a dataset where the number of rows is fixed. On the other hand, if you are working with a dynamic dataset that might grow or shrink, use ARRAYFORMULA with the ROW function.
Concluding Words
Adding serial numbers to datasets enhances readability and improves data organisation. It is crucial for organising and maintaining data effectively. In this article, we discussed five useful methods of adding serial numbers in Google Sheets, including using the fill handle, incremental addition, ROW function, SEQUENCE function and ARRAYFORMULA with ROW function. Feel free to try out all the methods and select one that suits your needs the best.
