Don’t Create Numbered Lists Manually in Excel: Use SEQUENCE and COUNTA Instead

Clicking and dragging the capable grip successful Microsoft Excel to make a numbered database is unreliable, non-dynamic, and time-consuming, particularly if the database is lengthy. Instead, you tin harvester 2 of Excel's astir straightforward functions to marque the programme bash the enactment for you.

Imagine you're a gaming administrator, and you person a spreadsheet containing a database of gamers, what they scored successful the tournament, and their crippled status. Each clip you adhd a caller gamer to file B, you privation the number successful file A to summation automatically, truthful you tin way the fig of progressive players astatine immoderate fixed time.

Generating these ID numbers automatically volition prevention you from having to discarded clip typing them manually, region the anticipation of error, and support your spreadsheet tidy. Also, automatic numbering means that erstwhile you delete a enactment to region an eliminated subordinate from the list, the number volition update accordingly.

Before you usage the SEQUENCE and COUNTA functions unneurotic to make this scenario, guarantee the information is not formatted arsenic an Excel table. This is due to the fact that SEQUENCE is simply a dynamic array relation that produces a spilled array, and Excel tables aren't compatible with results generated successful this way.

First, delete immoderate numbers you person already typed automatically successful file A. To bash this, property Ctrl+Shift+Home to spell to compartment A1 earlier utilizing the Arrow keys to navigate to the close cell. Then, property Ctrl+Shift+Down to prime the remaining numbers. Now, property Delete.

Next, it's clip to make the magic look successful compartment A2. First, fto maine explicate however it works.

The SEQUENCE relation does what its sanction suggests: make a series of numbers:

=SEQUENCE(a,b,c,d)

where

• a is the fig of rows to return,
• b is the fig of columns to return,
• c is the starting number, and
• d is the measurement betwixt each number.

However, the fig of rows you request to instrumentality (argument a) depends connected the fig of names successful file B. As a result, for this argument, you request to archer Excel to number the fig of non-blank cells from B2 downward. This is wherever the COUNTA relation comes into play:

=COUNTA(e)

where

• e is the scope oregon values you privation to count.

With this successful mind, successful compartment A2, type:

=SEQUENCE(COUNTA(B2:B901)

where

• SEQUENCE is the relation that volition make the database of numbers, and
• COUNTA, the archetypal statement of the SEQUENCE formula, volition number the fig of non-blank cells successful the scope B2 to B901.

At this point, announcement that columns (argument b), commencement (argument c), and measurement (argument d) are successful quadrate parentheses successful the SEQUENCE relation tooltip, indicating that these arguments are optional. If near out, they default to 1, since Excel assumes that returning 1 file of numbers, starting astatine 1, and expanding by 1 each clip is what astir radical utilizing the SEQUENCE relation volition astir apt privation to return.

And Excel was close successful its presumption successful your case—you're aiming to nutrient a azygous file of numbers starting astatine 1 and expanding by 1 each time.

So, adjacent the rounded parentheses to implicit the SEQUENCE formula:

=SEQUENCE(COUNTA(B2:B901))

and property Ctrl+Enter to exit edit mode and enactment successful compartment A2.

However, arsenic it stands, since you lone referenced cells B2 to B901 successful the COUNTA portion of the formula, the number successful file A won't summation if you spell beyond this range. To hole this, wrong the COUNTA formula, notation the full of file B, and minus 1 from the full to relationship for the header row:

=SEQUENCE(COUNTA(B:B)-1)

Now, adjacent if you input thousands of caller subordinate names, the number successful file A volition proceed to update dynamically.

Let's accidental GameMaestro and Ghost mislaid their elimination match, and you deleted rows 4 and 6 from the sheet by right-clicking the enactment headers and selecting "Delete." Because determination are 2 less cells containing information successful file B, the COUNTA relation reduces the SEQUENCE effect by 2 rows.

---

SEQUENCE isn't the lone dynamic array relation successful Excel that updates automatically according to immoderate changes successful your data. For example, the FILTER function continually reorders a scope of information based connected criteria you set, the SORTBY function repeatedly sorts a database based connected autarkic data, and the PIVOTBY function lets you group, sort, filter, and aggregate information dynamically successful a azygous formula.