If you prefer keeping your records organized in Google Sheets, it’s essential to have rows with serial numbers. While you can number rows manually, it can be time-consuming, especially for large datasets.
The easiest way to number rows in Google Sheets is by entering a number, selecting the highlighted cell, and dragging the small blue box in the lower right corner downwards. This action will create a series of numbers, and you can quickly number multiple rows at once.
In this article, I’ve explained multiple methods to automatically number rows in Google Sheets.
How to Number a Column/row in Google Sheets – 5 Solutions that Work Well for Me
I tried multiple methods, and these five worked best for me for numbering rows and columns. Below I’ve explained each method in detail.
Method 1: How to Number Cells Using SEQUENCE Formula
You can use the SEQUENCE formula to quickly number multiple rows or columns at once. However, the number always starts with 1, so it may not work for bespoke entries (i.e. start with 0).
Step 1: To use this formula, select a cell in any row, enter =SEQUENCE(number), and press Enter. For example, entering the number “10” will number rows from 1-10.
Step 2: The rows will be numbered in a sequence, as shown in the image below.
Note: As you increase the number value when entering the formula, the sequence will increase automatically.
Method 2: How to Number Columns in Google Sheets Using Fill Handle?
The fill handle method is another easy trick to number rows and columns in Google Sheets. The best part about this method is that you can start the sequence from any number you want.
Follow these steps to use the fill handle method:
Step 1: Select a cell and enter a numeric value.
Step 2: Hover the cursor over the bottom right corner of the selected cell and click on the box-like icon.
Step 3: Drag it downwards up to the cell you want to number, and release the mouse click.
Method 3: How to Insert Numbering in Google Sheets Using ROW Function
The ROW function in Google Sheets gives you the row number of the selected cell, and it is a great way to number rows. Furthermore, if you don’t give it any input, the argument will return the row number of the cell you’ve used it for.
Follow these steps to use the ROW function to number rows:
Step 1: Select any cell in the spreadsheet and enter =IF(Cell Number<>"",ROW()-1,""), and press Enter.
Step 2: Paste the formula in all rows you want to create a sequence, or you can hold the small blue box and drag it downwards.
Method 4: How to Add a Series in Google Sheets Using COUNTA Function
Another way to number rows in Google Sheets is by using the COUNTA function. This method works well for large datasets, and you can quickly number as many rows as you want.
Follow these steps to use the COUNTA function to number rows:
Step 1: Open a Google spreadsheet and select a cell to start the numbering from.
Step 2: In the cell, type =IF(ISBLANK(cell_number),"",COUNTA($B$2:cell_number)), and press Enter.
Step 3: Now select the next cell and enter =IF(ISBLANK(A2),"",COUNTA(A$2:$B2)).
Here, you should refer to the cell used previously for entering the COUNTA function to get a sequence of numbers in rows.
Step 4: After entering the formula in all rows, press Enter, and you’ll get the value.
How to Automatically Add Numbers in Google Sheets Mobile App? (iPhone, iPad, Android)
Adding numbers in the Google Sheets mobile app is easy, and you can number multiple rows with a few clicks.
Step 1: Open the Google Sheets app on your phone and select the spreadsheet.
Step 2: Click on a cell and enter a value, for example, “1”. Select another cell and add a value “2”.
Step 3: Select the range of cells you want to create a series of numbers and tap on the screen. Choose Autofill from the options.
Step 4: It’ll return you a series of numbers for the selected cells.
Some Questions You May Have
How can I set a starting number for row numbering in Google Sheets?
You can set a starting number for row numbering in Google Sheets. However, there is no built-in function for it, so you’ll need to create a custom function first.
How do I create a custom formula to number rows in Google Sheets?
You can use the Apps Script feature of Google Sheets to create custom formulas. This is an open platform that integrates with your Google Sheets and lets you make new formulas.
How can I automatically number rows in Google Sheets without dragging?
If you don’t want to drag, you can use the following formula:
=ARRAYFORMULA(IF(A: A="", ROW(A: A)-ROW(Cell Number)+1))
This formula uses the ARRAY FORMULA function to apply the formula to an entire column at once. The IF function checks whether a cell in column A is blank. If it’s not, it calculates the row number by subtracting the first row of the sheet (ROW(A1)) from the current row and adding 1.
It will give you a sequential list of numbers in column A, even if you add or remove rows in your sheet.
Further Reading
If you’re interested in learning more about organizing and managing rows in Google Sheets, here are some additional articles to explore:
- Group Rows and Columns in Google Sheets Discover another way to organize rows in Google Sheets
- Named Ranges in Google Sheets Learn about named ranges and how they can be beneficial for users numbering rows, as they make it easier to reference specific cells or ranges in a sheet.
- Remove Duplicates in Google Sheets Find out how to remove duplicates, which can be useful for readers working with numbered rows to ensure each row has a unique identifier.
- Filter Data in Google Sheets Explore filtering data in Google Sheets, a technique often used alongside numbering rows to better organize and view the information in a sheet.
- Sort by Color in Google Sheets Learn how to enhance numbered rows by visually categorizing them using colors and sorting them accordingly.
Final Thoughts
With these methods, you can easily number rows in Google Sheets and eliminate manual effort. When copying formulas from this article, make sure to use the correct cell number and other details.
If you have any issues when numbering rows in Google Sheets, drop your queries in the comments section, and I’ll get back to you shortly.