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.

Excel Fill Series creates a list of copied numbers

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.

Excel's Fill Options button

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.

Filled list of numbers does not increase when new items are added to the list

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?

Deleting, adding, or sorting rows disturbs the integrity of manually numbered lists

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:

Basic demonstration of the SEQUENCE function

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.

Excel SEQUENCE function using COUNTA to determine table length

(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

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.
Learn More
Excel new functions course cover

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 )
Example of concatenating text to a SEQUENCE function to create custom lists
="(" & SEQUENCE(COUNTA(B:B) - 1 ) & ")"
Example of concatenating text to a SEQUENCE function to create custom lists

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.

Custom Number Formatting in Excel to add customization to numbered lists
Custom Number Formatting in Excel to add customization to numbered lists

(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.

Demonstrating the failure of the SEQUENCE function in an Excel Table - Spill errors

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
Using Excel's ROW function to generate a numbered list in an Excel Table

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)
INT, SEQUENCE, and COUNTA functions used together to create a list of repeating numbers

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.

Excel Download Practice file

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

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.