Here’s a problem I’ve run into before. You have a numbered list in Excel, but you insert or remove entries from the list. Then you have to renumber all the rows, or at least hopefully you remember to do it. What if the row numbers could be automatically maintained? This is where SEQUENCE() comes in.

Let’s start with a really simple example of how to use SEQUENCE(). If you’re making a top 10 list, you would simply go to the first row of your list and enter the formula =SEQUENCE(10). Excel would display a 1 in the cell you typed your formula into, and each cell below it would display the next number, 2 – 10, without you having to enter anything in the cells below.

But what about my original scenario in which rows get added or removed and you want the row numbers to adjust automatically? All we need to do is find a way to let Excel determine how many rows we need instead of entering a fixed number like 10. The easiest way to do that is to use the COUNTA() function. CountA counts the number of cells in a range that are not empty.

Assuming your row numbers will be in column A and your list data will be in column B, just go to the cell in column A where you want to row numbers to start and enter =SEQUENCE(COUNTA(B:B)).

In the first example, we simply told Excel to give us a sequence of 10. In the second example we told Excel to count the number of cells in column B that are not empty, and make the sequence that long. Now, as you add or remove items from your list, the row numbers will automatically adjust.

Let’s add one more small adjustment to the formula. Let’s say the first row of your list contains column headings. If you just use the count of non-empty cells you’ll end up with an extra numbered row. To account for the heading, change your formula to =SEQUENCE(COUNTA(B:B)-1). Now the sequence number will be equal to the non-empty cells in column B minus 1.

Here a few other ways you can control how SEQUENCE() works.

The whole formula is =SEQUENCE(rows,columns,start,step). In the previous examples, we only used the rows argument and ignored the rest. But if you wanted to number columns instead of rows, you would leave the rows setting empty, followed by a comma, and then enter the number of columns. For example =SEQUENCE(,10).

If you wanted 10 numbered rows, but want the first row to be numbered 100 instead of 1, you would leave the column setting empty and add the Start setting, like this. =SEQUENCE(10,,100)

And if you want 10 numbered rows, starting your count at 100, and incremented by tens instead ones, you can add the Step setting. The formula would be =SEQUENCE(10,,100,10)


If You Liked This Post: