What Is Numbering In Google Sheets?
Numbering in Google Sheets is a technique that enables us to automatically populate cells with sequential numbers to organize rows of data. We can use options such as the ROW function and fill handle to number cells in Google Sheets.
Users can utilize the numbering technique in Google Sheets to number key cells in massive datasets containing financial and accounting data. It helps save time to manage such standardized datasets.
For example, the source dataset lists employee names and their entry times.
The aim is to update the entry number for each employee in column A, with the entry number starting with 1 and incrementing by one in the subsequent cells till cell A11.
Then, we can insert numbering in Google Sheets using the ROW(), which works similar to Excel ROW function, in each target cell.
In this automatic numbering in Google Sheets example, we enter the ROW() in a target cell, with the reference to the cell above the target cell as its argument value.
The reason for supplying such an argument value to the ROW() is that the top-most cell, which we must number as 1, is the second cell in column A, cell A2. So, supplying the reference to cell A1 as the input to the ROW() in cell A2 ensures the function output is 1.
So, we can then continue numbering in Google Sheets by dragging the fill handle till cell A11 to number the cells from A3:A11 in the required sequence.
Table of contents
Key Takeaways
- The numbering in Google Sheets is a method that helps us to update cells with sequential numbers automatically.
- The more straightforward methods to number cells in Google Sheets are to use the fill handle option, ‘+’ arithmetic operator-based or ROW function-based customized formulas.
- We can dynamically number cells in Google Sheets using inbuilt functions such as the ARRAYFORMULA and SEQUENCE. Also, we can create customized formulas using other inbuilt functions, such as OFFSET and COUNTA, for sequentially numbering the desired cells in Google Sheets.
- Numbering cells in Google Sheets helps organize and manage massive financial and statistical datasets, making the data more readable.
Numbering() In Google Sheets Formula
The Numbering() in Google Sheets is the ROW(), which has the same logic as the Excel ROW function. Its syntax in Google Sheets is the following:
Where,
- cell_reference: The cell whose row number the ROW() must return. The argument value is optional. However, when we omit it, the ROW() returns the number of the row where the cell in which we entered the formula is located.
Furthermore, assume the cell_reference argument value is a cell range containing more than a single cell, and the expression is not an array formula. Then, in such a case, the ROW() output is the number denoting the first row in the range supplied as the cell_reference argument value.
How To Add Numbering In Google Sheets?
We shall see three techniques of adding automatic numbering in Google Sheets
#1 – Using The Fill Handle Option
- Please choose the top-most cell in the set of cells, which we aim to number automatically.
- Please enter the number we wish to view in the chosen cell and press Enter.
- Enter the second number in the required sequence we want to view in the next cell. Press Enter.
- Choose the top two cells. Next, place the mouse cursor at the right corner at the bottom of the chosen cell range. Next, by pressing the mouse’s left key, drag the cursor up to the cell we want to number.
Thus, in this way we can add numbering in Google Sheets in the required set of cells, with the numbers in the sequence we set in the top two cells.
#2 – Using The Addition Operator-based Customized Formula
- Please select the top-most cell in the set of cells, which we aim to number automatically.
- Update the number we want to view in the chosen cell and press Enter.
- Enter the following formula in the next cell.
=previous_cell_reference + number
The number term in the above formula is the numeric value to add to the value in the cell above the current cell. We do so to set the required sequence of numbers in the target cells.
- Select the second cell. Next, place the mouse cursor at the right corner at the bottom of the chosen cell. After that, by pressing the mouse’s left key, drag the cursor to the cell we want to number.
#3 – Using The ROW Function-based Formulas
The steps to use the ROW()-based insert numbering in Google Sheets technique are as follows:
- Please choose the top-most cell in the set of cells, which we aim to number automatically.
- Type the appropriate ROW() formula to populate the chosen cell with the required number.
- Utilize the fill handle to continue numbering in Google Sheets target cells using the ROW()-based formula.
Examples
The examples below show the different ways to add numbering in Google Sheets.
Example #1
The source dataset contains a list of the top 10 laptops in the US.
We must update their ranks, starting from 1 to 10, in column A.
Then, the steps to number the required cells using the fill handle are as follows:
Step 1: Select cell A2, enter the value of 1, and press Enter.
Step 2: Select cell A3, enter the value of 2, and press Enter.
Step 3: Select the cell range A2:A3.
Next, place the mouse cursor at the right corner at the bottom of the cell range A2:A3. Next, by pressing the mouse’s left key, drag the cursor till cell A11.
Thus, the required cells get numbered automatically, following the sequence we updated in cells A2:A3.
Example #2
The source dataset contains the date-wise inventory level data of various product categories at a store.
We must update the order numbers, from 1 to 11, in column A to complete the dataset.
Then, the steps to use the ‘+’ operator to number the targeted column A cells are as follows:
Step 1: Select cell A2, enter the value of 1, and press Enter.
Step 2: Choose cell A3, enter the following expression, and press Enter.
=A2+1
Step 3: Utilize the fill handle option to implement the formula in the cell range A4:A12.
The formula we entered in cells A3:A11 keeps adding the value of 1 to the previous cell value. Thus, in this way, the target cells get numbered, with each number incrementing by 1.
Example #3
We have a list of stationery items and their units sold data.
We must update their invoice numbers, from 1 to 10, in column A cells A2:A11 of the source dataset. However, the invoice number must be preceded by the text “INV_”.
Then, the steps to achieve the desired outcome using the ROW() are as follows:
Step 1: Select cell A2, enter the following ROW()-basedformula, and press Enter.
=”INV_”&(ROW()-1)
Step 2: With cell A2 chosen, utilize the fill handle option to copy the formula in cells A3:A11.
The ROW() does not have an argument value supplied to it. So, it returns the current row number in each target cell. For instance, the ROW() in the cell A11 formula returns the value of 11.
Next, the formula deducts the value of 1 from the ROW() output. So, in cell A11, the value now is 10. Finally, the expression appends the text “INV_” before the determined value to return the required invoice number in the desired format. For example, the cell A11 formula adds the specified text before the value of 10 to return INV_10 as the required invoice number for the corresponding stationery item.
Important Things To Note
- Ensure that the ROW()-based customized formula used for numbering in Google Sheets is correct. Otherwise, the cells may get populated with incorrect numbers or numbers, not in the desired sequence.
- Ensure that the number we update in the top-most cell of the set of target cells we aim to number in Google Sheets is correct. Otherwise, continuing the sequence in the rest of the cells with the fill handle or the addition arithmetic operator-based formula will lead to incorrect numbering.
Frequently Asked Questions
We can do dynamic auto-serial numbering in Google Sheets using the ARRAYFORMULA or SEQUENCE function.
For example, we have a list of employees.
The task is to update the car park slot for each employee, with the allotted car park slot being consecutive, even, and odd in columns B, C, and D, respectively.
Then, the steps are as follows:
Step 1: Select cell B2, enter the following ARRAYFORMULA(), and press Enter.
=ArrayFormula(ROW(A2:A8)-1)
The formula executes as an array formula. The ROW(A2:A8) returns the row number 2 in cell B2, 3 in cell B3, and so on till 8 in cell B8. However, the expression inside the ARRAYFORMULA() deducts the value of 1 from the respective ROW() output in each target cell. Thus, the cells B2:B8 get numbered from 1 to 7.
Step 2: Choose cell C2, enter the following SEQUENCE(), and press Enter.
=SEQUENCE(7,1,2,2)
The SEQUENCE() accepts four input values. The first input is the number of rows we aim to number, which is 7 (rows 2 to 8). The second input is the number of columns we want to number, which is 1 (column C). The third input is the value that must appear in the top-most cell of the concerned set of cells, which is 2 since we must display the even car park slots in column B. Finally, the last input is the value by which we want the numbers in the subsequent cells to increment. In this case, the last argument value is 2 since we require the numbers to be even.
Thus, for the input values, the SEQUENCE() populates cells B2:B8 with the required even car park slots.
Step 3: Choose cell D2, enter the following SEQUENCE(), and press Enter.
=SEQUENCE(7,1,1,2)
The logic of the SEQUENCE() in column D is the same as explained in the previous step. However, while the first, second, and fourth input values remain the same, the third is different. The number to show in the top-most cell of the concerned set of cells is 1 instead of 2. The reason is that we must display odd car park slots.
We can customize the starting number when numbering cells in Google Sheets by entering the required number in the top-most cell. After that, we can use the fill handle to continue the sequential numbering of the required cells.
We can remove numbering in Google Sheets by first selecting the cells where we must remove the numbering. Next, right-click and use the applicable Delete option to remove the numbers from the chosen cells.
Download Template
This article must be helpful to understand the Numbering In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Numbering In Google Sheets. Here we explain how to add Numbering in Google Sheets with examples (automatically with examples). You can learn more from the following articles. –
Leave a Reply