Creating Numbered Lists – Fill Series Problem

When we try to number rows in Excel using the Fill Series handle, Excel doesn’t understand why we are filling it down. It 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.

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 biggest problem with numbering lists this way is that adding or removing an item can mess up the order. Sorting the list by a different column also scrambles the numbers.

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

Use the Excel SEQUENCE Function to Generate Numbered Lists

The SEQUENCE function is the perfect tool to number rows in Excel of any length quickly. It ensures 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 tell us 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. Then, use that count as the “count of numbers to generate” argument.

=SEQUENCE(COUNTA(B:B) - 1)
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

Imagine you need a list where each number repeats several times before moving to the next number. For example, the list would look like: 1, 1, 1, 2, 2, 2, 3, 3, 3, and so on.

Here’s a simple way to do this:

  1. Count the Items: First, find out how many numbers you need in total. Use the COUNTA function, which counts how many items are in a column.
  2. Generate the Numbers: Next, use the SEQUENCE function. This function creates a list of numbers. Set it to start at 3 and increase each number by 1.
  3. Round Down: Lastly, the INT function helps by rounding down any decimal numbers. It takes the list from SEQUENCE, divides each number by 3, and keeps only the whole number part. This ensures each number repeats three times.

The formula to use in your spreadsheet is:

=INT(SEQUENCE(COUNTA(B2:B35), 1, 3, 1) / 3)
INT, SEQUENCE, and COUNTA functions used together to create a list of repeating numbers

This makes each number repeat three times before the next increment. I demonstrate this method in this video.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the Excel Sequence function to number rows in Excel. Download the workbook here and start applying what you’ve learned directly in Excel.

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.