Thread by @benlcollins

Today I want to show you a quick way to add an ID column to your #GoogleSheets tables, using the SEQUENCE and COUNTA functions.

It's a quick way to see how many records you have in your table.

Let's go! 👇

(1/9)

We need a dataset for this example so let's grab an education dataset...

Here are some student test scores along with some variables that affect those scores.

(2/9)

Insert a new blank column on the left of this dataset. Add the title "ID" in cell A1 and this formula in cell A2:

=SEQUENCE(COUNTA(B2:B))

(3/9)

This generates a row ID in column A that auto-increments whenever you add new rows of data (provided column B is not blank).

It counts how many records there are in your table and generates an array of incrementing numbers 1, 2, 3, etc. matching the number of records.

(4/9)

It works great until you sort this data.

If you do that, this SEQUENCE function ends up further down your dataset because it moves with row 2 during the sort.

Yikes! 😱

(5/9)

The solution is to add this SEQUENCE formula to the header row, making use of an array literal formula.

More on those here: benlcollins.com/spreadsheets/g…

(6/9)

Delete the current ID header and the formula, and replace it with this formula in cell A1:

={"ID";SEQUENCE(COUNTA(B2:B))}

This puts the text "ID" in cell A1 and then generates the row IDs below that. Again, it auto-increments anytime a new row is added.

Nice!

(7/9)

⚠️ Important ⚠️

These ID numbers are not locked to their rows.

If you sort the data, the ID column will always show 1, 2, 3, etc. in order, because it's generated by the sequence formula.

(8/9)

If you want to lock the ID numbers to their rows, copy column A and paste as values.

Of course, this removes the auto-incrementing feature. 😔

Anyway that's it! I hope you found this tip helpful.

(9/9)