How to Automatically Increment Numbers in Google Sheets? (2023) [4 Easy Methods]

Wondering how to automatically increment numbers in Google Sheets?

Yes, you are at the right place to learn about automatically incrementing numbers in Google Sheets.

There may be times when you want to auto-increment a number in Google Spreadsheets.

In this guide, we’ll explain to you 4 different ways to automatically increment numbers in Google Sheets.

1. Increment Number by using Simple Drag

2. Creating a Formula to Automatically Increment

3. Increment Number by using ROW and COLUMN Functions

4. Increment Number by using CountA Function

We will discuss each of these in detail below.

1. Increment Number by using Simple Drag

In Google Sheets, the quickest approach to auto-incrementing numbers is to use a simple drag. Follow the below steps to increment the number with the simple drag method.

In your spreadsheet, enter two consecutive numbers. In this example, we placed 1 in the first row and 2 in the second row.

Click and highlight both cells where the numbers were entered.

Click on the cells’ bottom right corner. Now, you can either drag the cell or double-click on it to fill the data to the bottom rows.

If you use double click method to auto increment the number, the cells will automatically increase the numbers and fill your data to the end. This method will only fill the cells with numbers that are equal to the data in the columns next to it.

If you use the simple drag method, you can continue to increment it to as many cells as required.

Note: This method can also be used for incrementing the columns as well.

2. Creating a Formula to Automatically Increment

You can also simply create a formula to add one to a value, then copy and paste the formula into your Google Sheet to increment.

Follow the below steps to increment the number with the simple drag method. 

This method is somewhat similar to the first one.

1. You need to first establish a starting value. To begin incrementing, we placed a 1 in the first row. 

2. To reference your starting cell, press the equals sign (=) in the next cell and click on it ().

3. On your keyboard, press the plus sign (+), then type 1 (or whatever number you want to increment by), then press ENTER to calculate the formula.

4. Double-click the blue square in the bottom right corner to copy and paste this formula into additional rows.

This method is really simple; you simply create a custom formula to add “+1” to your cell, and it will continue to increase on each additional row.

3. Increment Number by using Row and Column Functions

The ROW and COLUMN functions can be used for inserting row numbers and column numbers into your spreadsheet.

You can use these methods to increment numbers by using the spreadsheet’s actual row and column numbers.

ROW function

ROW function returns the row number of a specified cell.

The syntax of the ROW function is:

=ROW([reference])

=ROW([reference]+1)

To use ROW, simply type “=ROW()” and it will return the row number of the cell where the formula is entered.

You can see in the above example we are referencing cell A7, so the formula returns a value of 7.

You can see in the below example we are referencing cell A5 and adding 1 with the row, so the formula returns a value of 6.

COLUMN Function

The COLUMN function works in the same manner as the ROW function does. If the formula is left blank, it will return the number of the column you are on or the column number of the cell you are referencing.

The syntax of the COLUMN function is:

=COLUMN ([reference])

=COLUMN ([reference])+1

To use COLUMN, simply type “=COLUMN()” and it will return the column number of the cell where the formula is entered.

A cell reference can also be used in a calculation to return the number of the column where your cell reference is located.

You can see in the above example we are referencing cell F6, so the formula returns a value of 6.

You can see in the above example we are referencing cell A6 and adding 1 with the column, so the formula returns a value of 2.

4. Increment Number by using CountA Function

We’ve completed the most basic and often used methods for populating the serial number field. We may also use the built-in functions to accomplish the same goal.

CountA is a function that counts the number of non-empty cells in a range. The syntax is straightforward.

Simply type CountA, choose the range of cells, and the number of items will be calculated.

We are counting the number of goods entered by the user in our example.

The initial cell address for the Product range will be B2. The last value changes depending on where your cursor is.

It’s one fewer than your present cellphone number. In the serial number cell that corresponds to, we wrote the following formula.

COUNTA($C$3:C4)

The ‘$’ symbol freezes the value of a cell. 

i.e., the range’s beginning address will now be ‘C3’. 

The entire method fails if the ‘$’ sign is removed since it is unable to accurately determine the range’s starting point.

It also inserts the serial numbers for the vacant cells. As a result, we may add a condition that ensures that the serial number is filled only when it pertains to a certain product, otherwise it is kept blank. The formula was changed to:

=IF(C3=””,””,COUNTA($C$3:C3))

In the above picture, we can see that vacant/empty cells are not filled with numbers.

Wrapping Up

Hope you got an idea about how to automatically increment numbers in Google Sheets.

In this guide, we’ve covered four simple methods for auto-incrementing numbers in a spreadsheet.

For more complex requirements, you can edit and modify the functions based on requirements.

Kindly let us know if you have any queries.