4 Ways to Create Numbered Lists in Excel

Bottom Line: Learn 4 different techniques for creating a list of numbers in Excel. These include both static and dynamic lists that change when items are added or deleted from the list.

Skill Level: Beginner

Watch the Tutorial

Watch on YouTube & Subscribe to our Channel

Download the Excel File

You can download the worksheet that I use in the video here:

Numbered Lists

Four ways to make numbered lists

If you have a list of items in Excel and you'd like to insert a column that numbers the items, there are several ways to accomplish this. Let's look at four of those ways.

1. Create a Static List Using Auto-Fill

The first way to number a list is really easy. Start by filling in the first two numbers of your list, select those two numbers, and then hover over the bottom right corner of your selection until your cursor turns into a plus symbol. This is the fill handle.

Use fill handle to complete a numbered list

When you double-click on that fill handle (or drag it down to the end of your list), Excel will fill in the blanks with the next numbers in the sequence.

Fill handle

An alternate way to create this same numbered list is to type a 1 in the first cell and then double-click the fill handle. Because Excel doesn't have a second number to identify a sequence or pattern, it will fill down the columns with 1s. But you will notice that a menu is available at the end of your column, and from that menu, you can select the option to Fill Series. This will change the 1s to a sequential list of numbers.

Fill Series for Numbered List

The numbered column we've created in both instances is static. This means it doesn't change when you make additions or deletions to the corresponding list of items. For example, if I insert a new row in order to add another task, there is a break in the numbered column as well, and I would have to repeat the same steps to correct the list.

Static Numbered List problem

So, unless we want the numbers to always remain as they are, a better option would be to create a dynamic list.

2. Create a Dynamic List Using a Formula

We can use formulas to create a dynamic list where the numbers update when we add or delete rows from the list.

To use a formula for creating a dynamic number list, we can use the ROW function. The ROW function returns the row number of a cell. If we don't specify a cell for ROW, it just returns the current row number of whatever is selected.

In our example, the first cell in our list doesn't start on Row 1. It starts on Row 4. But we don't want our list to start at 4; we want it to start at 1. To subtract the extra 3, our formula will include a minus sign and then the ROW function again, but this time pointed to the cell above our formula (which is in Row 3).

dynamic numbered list using a formula

We use the absolute values (dollar signs) in our cell selection because we don't want that number to change as we copy the formula down the list. In other words, we always want to subtract 3 from the row number to give us our list number.

When we copy down the formula, we have a numbered list that will adjust when we add or delete rows. However, when adding a row, we will have to copy the formula into the newly inserted cell.

Add the formula to new cells in the dynamic list

This additional step of copying the formula into new rows can be avoided if we use Excel Tables.

3. Create a Dynamic List Using a Formula in an Excel Table

If we write the same formula but we use an Excel table, the only difference we will make in the formula is that we reference the column header instead of the absolute cell value.

Use the ROW Function in an Excel Table

Because we are using an Excel Table, the formula automatically fills down to the other rows. When we add a row to the table, the formula will automatically populate in the new cell.

Dynamic numbered list in an excel table

4. Create a Dynamic List Using Dynamic Array Formulas

Below, I have a table that is populated using Dynamic Array Formulas. The FILTER formula spills a range of results based on whatever name is selected in the orange cell.

spill range filter function

The Dynamic Array formula we will use is SEQUENCE. This will return a sequence for the number of rows we identify in the formula. To tell Excel how many rows there are, we will use the ROW function as the argument for sequence. The argument for ROW is just the spill range. The way to notate the spill range is to simply place a hashtag after the name of the first cell in the range. So our formula looks like this:

=SEQUENCE(ROWS(B5#))

Once we complete our formula and hit Enter, the list will be numbered.

Dynamic Array Numbered List

With this formula in place, if the size of the range changes when a new name is selected, the numbered column for the list will automatically adjust as well.

Dynamic Array Numbered List Adjusted Spill Range

Bonus Tip: Formatting Your Numbered List

If you want to add periods or other punctuation to your numbered list:

  1. Select the entire list and right-click to choose Format Cells. Or use the keyboard shortcut Ctrl + 1.
  2. Choose the Custom option on the Number tab.
  3. Then in the Type field, type in the number 0 with whatever punctuation you would like to surround your number.

Here, I've just added a period.

Custom format for numbered list

When you hit OK, you will have formatted the entire selection.

Related Posts

Here are a few similar posts that may interest you.

Conclusion

I hope these four ways to create ordered lists are helpful for you. If you have questions or feedback, I would love to hear them in the comments. Have a great week!

3 comments

Your email address will not be published. Required fields are marked *

  • Hi Jon,

    The way that I do it is as follows:-
    Cell A4 shows 1
    Cell A5 is a formula “=A4+1”, then copy this formula down
    If you delete or add a row simply copy the formula down again

    Peter

  • I use this formula for a list that auto updates.

    I have a header row in column 1, my list numbers in column A and my data in column B. I put this formula in cell A2, and then copy it down.

    =IF(SUBTOTAL(103,B2)=0,””,IF(B2″”,MAX($A$1:OFFSET(A2,-1,0))+1,””))

    This will only show a number if there is something in the corresponding cell in column B. It starts at 1, but if I overtype any number at all in column A then it will use that number from then on. If a row is hidden then it doesn’t count that as a number at all. If I don’t want a number on a row I simply delete the formula and the list continues on the next row using the next number

    Parts of it I’m sure I borrowed from informative sites just like this one, and I think I’ve tinkered with it a bit since then. So thought I’d post a comment and pass it on…

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter