How to automatically add numbers in google sheets

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. 

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.

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)
  • ROW function 
  • Column function
• 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

Syntax: 

ROW([cell_reference])

Input:

cell_reference (Optional) – The cell whose row number will be returned. 

Output:

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.

Example:

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:

=IF(B1<>"",ROW()-1,"")

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.

FAQ

Wrapping up

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.

Appendix

[1] automatically add numbers in google sheets – Link

Further Reading

New to google sheets ? Start here