When working in Google Spreadsheets, there may be times when you want to auto-increment a number.
Whether you’re trying to number rows, columns, or insert other numbers, learning how to increment is a good skill for all spreadsheet users to have.
In this tutorial, I will show you three different ways to automatically increment numbers in Google Sheets.
Table of Contents
Increment Number with Fill Handle
The easiest way to auto-increment numbers in Google Sheets is by using the fill handle to auto-fill. This method works great for adding an index number to columns.
Here’s how to do this:
- Add two consecutive numbers to your spreadsheet. In this example, I place a 1 in the first row and a 2 in the second row.
- Click and highlight both cells where you placed the numbers
- Double-click in the blue square in the bottom right corner of your cells
- This will automatically increment numbers and fill up to the end of your data
This method will only fill the cells with numbers up to the data in the columns or rows next to it.
To continue incrementing numbers past where your data ends, you can repeat the steps but this time click and drag the blue square instead of double-clicking it.
In this example we created index numbers by incrementing rows. But, you can also use this method for numbers columns.
Increment Number with ROW and COLUMN Function
If you’re trying to insert row numbers or column numbers into your spreadsheet, there are a couple of great built-in functions that can do this: the ROW and COLUMN functions.
With these functions you can increment numbers by using the actual row and column numbers of your spreadsheet.
The syntax of the ROW function is:
To use ROW you can leave the formula blank “=ROW()” and it will return the row number of the cell that the formula is on
You can also use a cell reference in the formula to return the number of the row that your cell reference is located at.
You can see in the above example I am referencing cell A2, so my formula returns a value of 2.
The syntax of the COLUMN function is:
The column function works the same exact way as the ROW function. It will return the number of the column you are on if the formula is left blank, or the column number of the cell you are referencing.
Creating a Formula to Automatically Increment
You can also just create a simple formula to add one to a value and then copy and paste the formula to increment in your Google Sheet.
This method is somewhat similar to the first one. Here are the steps
- First, you need to have a starting value. I placed a 1 in my first row to have something to start incrementing with
- In the next cell, press the equals sign on your keyboard (=) and click on your starting cell to reference it (A2)
- Press the plus sign (+) on your keyboard and then enter a 1 (or whatever number you want to increment by), then press ENTER to calculate the formula
- Copy and paste this formula down to additional rows or double click the blue square in the bottom right corner
This method is very simple, you are just creating a custom formula to add “+1” to your cell and so it keeps incrementing on each additional row.
Those are three different ways to auto increment in your spreadsheet. They are all very simple to understand.
In this tutorial, I just showed the basics of how to use these, but you can use them in more advanced ways if you desire.