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
Download the Excel File
You can download the worksheet that I use in the video here:
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.
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.
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.
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.
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).
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.
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.
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.
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.
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:
Once we complete our formula and hit Enter, the list will be numbered.
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.
Bonus Tip: Formatting Your Numbered List
If you want to add periods or other punctuation to your numbered list:
- Select the entire list and right-click to choose Format Cells. Or use the keyboard shortcut Ctrl + 1.
- Choose the Custom option on the Number tab.
- 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.
When you hit OK, you will have formatted the entire selection.
Here are a few similar posts that may interest you.
- Excel Tables Tutorial Video – Beginners Guide for Windows & Mac
- New Excel Features: Dynamic Array Formulas & Spill Ranges
- How to Prevent Excel from Freezing or Taking A Long Time when Deleting Rows
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!
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
Great , Thanks
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.
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…