Clicking and dragging the fill handle in Microsoft Excel to create a numbered list is unreliable, non-dynamic, and time-consuming, especially if the list is lengthy. Instead, you can combine two of Excel’s most straightforward functions to make the program do the work for you.
As at the time of writing (May 2025), although the COUNTA function is available on most versions of Microsoft Excel, only those using a version of Microsoft Excel released in 2021 or later, Microsoft 365, Excel for the web, or the Microsoft Excel phone and tablet apps can access the SEQUENCE function.
Microsoft Office 2024
Gain permanent access to Word, Excel, PowerPoint, and OneNote with the Office Home 2024 bundle. It isn’t a one-to-one replacement for a Microsoft 365 subscription, as it lacks some apps and does not include feature updates, but it’s a one-time purchase, so it’s ideal for those who just need the basics.
Imagine you’re a gaming administrator, and you have a spreadsheet containing a list of gamers, what they scored in the tournament, and their game status. Each time you add a new gamer to column B, you want the count in column A to increase automatically, so you can track the number of active players at any given time.
To see the formula I used in column F and follow along as you read this guide, download a free copy of the Excel workbook used in the examples. After you click the link, you’ll find the download button in the top-right corner of your screen.
Generating these ID numbers automatically will save you from having to waste time typing them manually, remove the possibility of error, and keep your spreadsheet tidy. Also, automatic numbering means that when you delete a row to remove an eliminated player from the list, the count will update accordingly.
Before you use the SEQUENCE and COUNTA functions together to create this scenario, ensure the data is not formatted as an Excel table. This is because SEQUENCE is a dynamic array function that produces a spilled array, and Excel tables aren’t compatible with results generated in this way.
Related
To convert a formatted Excel table to an unformatted range, select one of the cells within the table, and click “Convert To Range” in the Tools group of the Table Design tab.
First, delete any numbers you have already typed automatically in column A. To do this, press Ctrl+Shift+Home to go to cell A1 before using the Arrow keys to navigate to the correct cell. Then, press Ctrl+Shift+Down to select the remaining numbers. Now, press Delete.
Next, it’s time to generate the magic formula in cell A2. First, let me explain how it works.
The SEQUENCE function does what its name suggests: create a sequence of numbers:
=SEQUENCE(a,b,c,d)
where
- a is the number of rows to return,
- b is the number of columns to return,
- c is the starting number, and
- d is the step between each number.
Related
How to Use the SEQUENCE Function in Excel
Save time and avoid errors when generating a chain of numbers.
However, the number of rows you need to return (argument a) depends on the number of names in column B. As a result, for this argument, you need to tell Excel to count the number of non-blank cells from B2 downward. This is where the COUNTA function comes into play:
=COUNTA(e)
where
- e is the range or values you want to count.
Related
How to Count Cells in Microsoft Excel
For counting cells with numbers, text, or nothing at all, here are the COUNT functions you need.
With this in mind, in cell A2, type:
=SEQUENCE(COUNTA(B2:B901)
where
- SEQUENCE is the function that will generate the list of numbers, and
- COUNTA, the first argument of the SEQUENCE formula, will count the number of non-blank cells in the range B2 to B901.
At this point, notice that columns (argument b), start (argument c), and step (argument d) are in square parentheses in the SEQUENCE function tooltip, indicating that these arguments are optional. If left out, they default to 1, since Excel assumes that returning one column of numbers, starting at 1, and increasing by 1 each time is what most people using the SEQUENCE function will probably want to return.
And Excel was correct in its assumption in your case—you’re aiming to produce a single column of numbers starting at 1 and increasing by 1 each time.
So, close the rounded parentheses to complete the SEQUENCE formula:
=SEQUENCE(COUNTA(B2:B901))
and press Ctrl+Enter to exit edit mode and stay in cell A2.
The blue line around the result reminds you that this is a spilled array, meaning the result literally spills out from the cell where you entered the formula.
However, as it stands, since you only referenced cells B2 to B901 in the COUNTA part of the formula, the count in column A won’t increase if you go beyond this range. To fix this, within the COUNTA formula, reference the whole of column B, and minus one from the total to account for the header row:
=SEQUENCE(COUNTA(B:B)-1)
Now, even if you input thousands of new player names, the count in column A will continue to update dynamically.
Let’s say GameMaestro and Ghost lost their elimination match, and you deleted rows 4 and 6 from the sheet by right-clicking the row headers and selecting “Delete.” Because there are two fewer cells containing data in column B, the COUNTA function reduces the SEQUENCE result by two rows.
SEQUENCE isn’t the only dynamic array function in Excel that updates automatically according to any changes in your data. For example, the FILTER function continually reorders a range of data based on criteria you set, the SORTBY function repeatedly sorts a list based on independent data, and the PIVOTBY function lets you group, sort, filter, and aggregate data dynamically in a single formula.
