When working with a large number of rows or columns, it’s tedious to manually put numbers before each cell.
In google sheets, you can automatically fill rows and columns with a sequence of numbers, dates, or even text.
Here is how to automatically add numbers in google sheets –
On a computer:
- Open google sheet
- In at least 2 adjacent rows or columns, enter numbers, dates or text
- Highlight both the cells. You will see a small blue box in the lower right corner
- Drag this blue box down till any number of cells
- This will add sequential data if the cells form a series
Google sheet will automatically detect the sequence while dragging and will fill the series. If the cells do not form a series, then the values will repeat across the selected cells.
On Android or ios mobile phone:
- Open a spreadsheet in the google sheets app
- In at least 2 adjacent rows or columns, enter numbers, dates or text
- Select the first cell and swipe down to the cell you want to fill.
- Make sure all the cells are highlighted
- Tap anywhere on the highlighted cells, a popup menu will open
- Choose Autofill. This will autofill the cells to form a series
If google sheets do not detect a series, then it will just repeat the values.
Here is a video showing this in action:
Table of content
- Fill handle method to auto increment
- Using Function (auto-update series if row added/deleted)
- Custom formula to auto increment
- Pre populate row number automatically
Fill handle to automatically add numbers in google sheets
The fill handle in google sheets allows you to automatically fill a list of cells by simply dragging one cell down. This saves a lot of time compared to manually adding numbers for each cell.
Instead of manually typing numbers, dates, or text in google sheets every time with your data, you can use the Autofill feature. This will make you more efficient.
Based on the data you have selected in the beginning, Google sheets will automatically fill a list of cells with sequential data if they form a series. If they do not form a series, then google sheets will simply repeat the data in the dragged cells.
Note that the fill handle fills a linear series of data.
A linear series is formed when every consecutive data point in a series has the same relation. It follows a pattern where each value is related to its preceding value in the same way across all the data.
Examples of linear data are 1,2,3,4,5 ….. or 100,99,98,97,96,95…. or even negative numbers like -5,-6,-7,-8 so on.
Example – automatically add row numbers:
Here is an example showing this in action:
1. Let’s say we have a data like the below and we want to add serial numbers to them in a separate column
2. First write 1 then 2 in cell A1 and A2
3. Now select both the cells. A blue box will appear in the lower right corner
4. Double click on this blue box. It will automatically fill the data in column A up to the cell number that B has data
5. You can manually drag this blue box also till cell A11, to fill the series
Here we used a fill handle to automatically fill the data in a row. We can do the same for columns.
Example – automatically add column numbers:
Here is an animation showing the same
Google sheets increment number and on delete auto update row and column
Autofill rows or columns using a fill handle is great. But the problem is they do not auto-update when there is a change in the series.
If you have a series from 1 to 10 and you deleted row number 5, then the sixth row will not automatically be renamed to 5. The same applies when you are adding a new cell.
Problem with Fill handle to autofill numbers:
We are using the same employee table from above. Rose is there at the 5th serial number and Steve is at the 6th. If we delete Rose from the list, we expect Steve to be the new 5th serial number.
But this will not happen as the list has been created using the fill handle.
Here is the animation showing the problem with the fill handle.
And here is the solution:-
Use functions like ROW() or COLUMN() to fill a series. This will ensure that even if a row or column gets deleted, the serial numbers will always be in a series format.
ROW formula – Google sheets auto increment number in rows
cell_reference (Optional) – The cell whose row number will be returned.
Numerical Row position
Let’s use the same example as above.
1. In column A2, write =ROW(A2)-1 . This will output 1 because ROW(A2) = 2
2. By dragging, copy this formula to any cell you want to fill. This will fill the series with sequential numbers.
Now delete cell number 5.
As you can see from below, Column D which was filled using the Fill handle did not update the serial number for Steve as 5th. But Column A which was filled using the ROW function did update automatically.
Note: if cell_reference is a range more than one cell wide and the formula is not used as an array formula, only the numeric value of the first row in cell_reference is returned.
COLUMN formula – Google sheets auto increment number in columns
The COLUMN function works the same way as the ROW function.
It will automatically add numbers in google sheets column instead of the row.
Similar to the ROW function, you write the COLUMN() function in one cell and drag it across the cells where you want to fill.
Here is how it will look:
Google sheets automatically add row for each new data
In all these examples of auto-filling a series, we learned how to automatically add numbers in google sheets when we already have data in other columns. What if we want to add data in the future, will the series data be added too? Let’s find out.
We have column B which has 10 rows with employee names and in column A we have serial numbers from 1 to 10.
Let’s say you have added another employee’s name after the 10th row. You want the serial number to automatically add 11 before the employee name.
Here is how to do this:
First you have to check if cells in column B is not empty.
If the cells in column B is not empty, then use the ROW formula to add number to cells in column A.
The formula is:
Here is the formula in action:
As you can see from above, the formula is applied in cell A7 but it does not have a serial number. This is because, cell B7 is empty.
Custom Formula to automatically add numbers in google sheets
You can use a custom formula to auto-increment numbers in google sheets. This will only work with Number fields. It will not work with time, date, or text.
The simple formula is to add +1 to each cell value.
So, if we want to serial from 1 to 10, then we will write 1 in cell A1 and in cell A2, we will write “=A1+1”. This will fill 2 in A2. Then you can drag the formula to other cells where you need to fill the number.
Here is the example showing this in action:
Examples of how to automatically add rows in google sheets?
How to automatically add cells in google sheets?
To automatically add cells in google sheets, make sure the cells are forming a series in a row or column.
Once they are in a series, select the cells and then drag the fill handle to automatically add numbers in google sheets cells.
How to automatically add a column in google sheets ?
To add column A automatically, in cell B1 write the formula =SUM(A:A) and this will add all rows in column A automatically. Even if you add new data in column A, the formula will automatically add them.
How to automatically add dates in google sheets?
To automatically add dates in google sheets, write the dates in a column in chronological order. Make sure they are forming a series.
Here are some example of how to form a series:
1/1/2022, 2/1/2022, 3/1/2022 and so on.
1 Dec 2021, 5 Dec 2021, 9 Dec 2021, 13 Dec 2021, 17 Dec 2021 and so on.
Once you write the dates in a series, then select them all and drag the fill handle to automatically add dates in the following rows.
Google sheets click and drag autoincrement not working, what to do?
If you are trying to automatically add numbers in google sheets and its not working, then check whether the Autocomplete feature is turned off in google sheets. From the top navigation menu, go to Tool > Autocomplete > Enable autocomplete. Make sure it’s checked.
How to make google sheets stop autoincrement?
To stop auto increment, you can turn off the Autocomplete feature in google sheets. Go to Tool > Autocomplete > Enable autocomplete. If it’s checked, then uncheck it.
How to increment months in google sheet?
To auto increment months in google sheets, write down the months in rows. Make sure they are in a chronological order. Use the fill handle to drag down to auto increment months.
How to auto increment date in google sheets?
To auto increment dates in google sheets, write down the dates in rows. Make sure they are in a chronological order. Use the fill handle to drag down to auto increment days.
How to increment week in google sheets?
To auto increment weeks in google sheets, write down the weeks in rows. Make sure they are in a chronological order. Use the fill handle to drag down to auto increment weeks.
In this tutorial, we learned how to automatically add numbers in google sheets. We discovered the fill handle method, ROW and COLUMN function method, and also a custom formula.
We also learned the shortcomings of the fill handle method and how the function method overcomes this. Also, we saw some practical use cases of these methods in action. Now go ahead and use these in your next worksheet.
 automatically add numbers in google sheets – Link
New to google sheets ? Start here