Number rows in Google Sheets

When you deal with data in Google Sheets, you usually want your data to be organized. That’s why it’s a good idea to number rows, so each of them has a column with a unique value.

Number rows manually

If you have a small dataset, you can add numbers manually.

Number rows using autofill

A better way to do it, even with a small amount of values is to use the autofill feature. Enter the number you want to start counting from and, while holding the Ctrl key, drag the small square at the bottom-right of the cell to fill the remaining cells. If you want to fill cells to the very end of the dataset, you can double-click the square icon.

Using a pattern

Another way to number rows in the dataset is to use the pattern of two values. The previous example can be created differently.

Insert 1 to cell A2, and 2 to cell A3.

Select both cells, and drag or double-click the square icon, this time, without holding Ctrl.

It doesn’t make much sense in this instance, but you may want to increment numbers by 10, and you can achieve it this way.

Use non-standard numeration

Google Sheets will recognize a non-standard numeration. If you want your dataset to use the following row numeration:

A001, A002, A003, …

You can do it. Just enter the first value and drag it to the rest of the dataset.

Incrementing the last number

If you want to increase the number by one, you can enter the first value, for example, 1. Then use a formula to increase the last value by 1.

A2: 1

A3: =A2+1

Drag cell A3 to the bottom, to fill the rest. This formula displays the same result as the methods used earlier. This time, all these cells, but the first one, are formulas. Remember to save them as values before you do any modifications to your dataset.

In this very case, it’s not that useful, but if you want to create more complicated formulas, this is the way to do it.

The ROW function

The ROW function returns the row number of a cell. For example, ROW(A6) will return 6.

With this function, you can create more sophisticated numbering methods. Let’s say you want every next number to be increased by the last value + row number:

1, 3, 6, 10, 15

If you start counting from 1, this is the way it should look:

A2: 1

A3: =A2+ROW(A2)

Now, if you autofill the rest of the cells, you will get the correct result:

You can also use ROW without parameter. This way, it will show the row number of the current row. Just fill all the cells with the =ROW() formula to add numeration.

You probably have headers inside your dataset, therefore, the function will start counting from 2. If it’s the case, just use =ROW()-1.

If you use Ctrl + ~ (tilde), you can preview formulas, instead of values, there are formulas.

Use the shortcut again to go back to values.