Add Auto Number to Google Form Response Sheet

To easily and uniquely identify each Google Form response is to have a key field. That field should be in the form of a number that will be autogenerated to avoid duplicates. This option is not available in Google Forms, so you have to adapt the Form response sheet to do it for you.

The auto-number formula

The formula that we are going to use to add auto number to Google Form response sheet contains a COUNTA, INDEX, ROW and ARRAYFORMULA functions. For the ones that are only here for the formula, here it is:

=ARRAYFORMULA( ROW (B1:INDEX (B:B, COUNTA (B:B)-1)))

Where to put this formula?

Numbering always goes before the data, so what I suggest is to add a column at the beginning, column A, and push the Form responses in the next column. So, instead of the response sheet to start with “Timestamp” in column A, it will start with the auto number.

To insert a column before column A, just right click on the column heading (in this case “A”) and then from the drop-down menu select “Insert 1 left”. Here is a screenshot:

Now, add a name for that column in cell A1 and in the cell A2, next to the responses, add the formula.

Why column B?

It doesn’t have to be column B in the formula, it can be any column that holds responses submitted from the Form. But because I’m using column A to add auto number to google form responses, I will use column B in the formula because it will always have values in it.

The functions

The most important function here is the ROW function. The Row function returns the number of the row for the specified cell, here is the usage: ROW(A9).

Syntax: ROW([cell_reference])

• cell_reference – [ OPTIONAL – The cell in which the formula is entered by default ] – The cell whose row number will be returned.
• 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.

Source: Google Docs Help – ROW function

For the ROW function, we are specifying a range that will return a set of row numbers only if we use the ARRAYFORMULA function. Learn more about ARRAYFORMULA function on Google Docs Help.

To get the range for the ROW function we are going to use the INDEX function that will return the last value of column B using the COUNTA to count the values. The minus one is because we must count from the first cell in the column (B), but we don’t have data in the first row.

This formula returns the number of the entry in the Google Form starting from 1. You can use a more complex formula to change the number if you don’t like to start with 1. Here is an example:

=ARRAYFORMULA( 10000 + ROW (B1:INDEX (B:B, COUNTA (B:B)-1)))

Now your number is going to start at 100001.

I have one article where I use this formula, you can check it here: Generate Invoices using Google Form and Sheets

If you have any more questions just add a comment and I’ll get back to you as soon as possible.

Edit

If you want your auto number to include also letters you can use this formula:

=ARRAYFORMULA ("ABC" & text (ROW (B1:INDEX (B:B, COUNTA (B:B)-1)), "000"))

You are going to get this output:

ABC001
ABC002
ABC003
ABC004