Creating Numbered Lists – Fill Series Problem
When creating a list of numbers using the Fill Series handle, Excel doesn’t understand why we are filling it down and assumes we wish to copy the value from the first cell.
There are ways to get the sequential list we had hoped for by either supplying a number pattern (like 1 & 2) or clicking on the Fill Options button to change the behavior from Copy Cells to Fill Series.
The problem with these methods is that anything beyond a list of 20 to 30 numbers becomes a frustrating exercise. Imagine trying to build a list of 1 million numbers. The time it would take to scroll down 1 million rows is a dealbreaker.
Another problem is that when new items are added to the list, the numbers do not automatically increase to account for the new items.
The worst reason for numbering lists in this manner is that what happens if you delete or add a row in the middle of the list, or worse, sort the list by a column other than the column of numbers?
Use the Excel SEQUENCE Function to Generate Numbered Lists
The Excel SEQUENCE function is the perfect tool for generating numbered lists of any length quickly and ensuring that the list remains correct even when rows are added, deleted, or sorted.
The simplest use of the SEQUENCE function is to give it a value that represents the count of numbers needed. A list of numbers that goes from 1 to 10 in whole number increments would look like the following:
=SEQUENCE(10)
Although easy, it doesn’t provide us with the intelligence to stop where the adjacent list ends or extend when new list items are added.
A smarter way to do this is to have the SEQUENCE function count the number of items in the adjacent list, and then use that result as the “count of numbers to generate” argument.
=SEQUENCE(COUNTA(B:B) - 1)
(NOTE: The “negative one” is in the formula to account for the heading in Column B.)
It doesn’t matter if the list grows, shrinks, or is sorted, the number sequence remains correct.
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
Custom Formatting of Number Sequences
If you like your numbered lists fancier, the way to achieve it is to concatenate text before or after the generated numbers. Here’s a couple of examples:
="No. " & SEQUENCE(COUNTA(B:B) - 1 )
="(" & SEQUENCE(COUNTA(B:B) - 1 ) & ")"
If you don’t want to change your formulas to include the extra text, you can use Custom Number Formatting to alter the appearance of the SEQUENCE results.
Select the cells (or the entire column) containing SEQUENCE numbers, then press CTRL-1 to open the Format Cells dialog box.
On the Number tab, select the “Custom” category and enter something like the following examples.
(If you’d like to learn more about custom number formatting, here’s everything there is to know.)
Automatic Row Numbers when Using Excel Tables
If you are a fan of Excel Tables (and who isn’t?), you’ll be disappointed to learn that you can’t use the SEQUENCE function because you’ll encounter a #SPILL! error.
An alternate method of creating dynamic numbered lists in an Excel Table is to use the ROW function.
The ROW function returns the row number in which the function resides. If the function is used on row 3, the number 3 is returned.
Below is an example of using the ROW function in a table. Note the “minus one” to account for the table’s heading.
=ROW() - 1
Create a Sequence of Repeated Numbers
Suppose you need a list of numbers that repeat the same value N number of times, then increment by 1 to show the new number N number of times. For example, 1, 1, 1, 2, 2, 2, 3, 3, 3,…
This can be accomplished with some function teamwork. Our elite team of functions consists of SEQUENCE, COUNTA, and INT.
- COUNTA will determine the height of the list by counting the number of items in the adjacent column, just as before.
- SEQUENCE will create a 1 column by N row list that starts at 3 and increments by 1. (The N rows are determined by the COUNTA results.)
- INT is the MVP on this team. INT will take the values generated by SEQUENCE and divided by 3, and only retain the whole number (integer) part of the result. INT will discard the remainder.
The formula reads as follows:
=INT(SEQUENCE(COUNTA(B2:B35), 1, 3, 1) / 3)
I demonstrate this method in this video.
Final Thoughts
I’m always curious to know what users prefer: dragging with Fill Series or writing formulas.
I start off as a Dragger myself, and if it starts to annoy me because my list is always changing, I switch to writing formulas. The point is that there is no right or wrong. Whatever works for you is the right way, okay?
Practice Workbook
Feel free to Download the Workbook HERE.
Featured Course
Black Belt Excel Package
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
