TED #033: Creating Numbered Sequences in Excel - Part 1
Diving into a few of Excels features that allow you to get this done!
Of all the things I do in Excel, creating a numbered sequence is probably one of my most frequent activities. A lot has changed over the years regarding this topic. So today I am going to dive into a few great ways that you can create a numbered sequence in Excel.
The “Drag & Drop” method
This one requires the least amount of work to get setup.
Step 1: Type in the numbers 1 and 2 in two separate cells.
Step 2: Highlight both cells.
Step 3: Grab the bottom right hand corner and “drag & drop” when you get to your desired result.
Using a simple formula
For most of my Excel life I have created numbered sequences in this way.
Step 1: Enter the first number of the sequence into a cell. In this case type 1 into cell A2.
Step 2: In the next cell type in =A2+1.
Step 3: Drag the formula down.
Utilizing Excels Fill Series option
This next option is really next level and can help users much more.
Excels Fill Series option is located in the Home menu. It is a literal control board that offers a ton of versatility when looking to create numbered sequences. Let’s take a closer look.
Open the Fill Series Menu: Go to Home > Fill > Series
That will open this gorgeous menu…
…just look at all of the options available to us!
Lets break it down.
Series in:
You can choose to fill the series in rows or columns depending on your preference.
Type:
Linear: This creates a series that increments each value by the same amount.
Growth: This will create a series where each new value is a product of the previous value and a constant multiplier.
Date: If you're creating a date series, this option will help you populate cells with dates, letting you specify the date unit to increment (day, month, or year).
AutoFill: This option attempts to fill in a series based on a recognized pattern in the adjacent cells.
Date Unit: If you chose the date type, you would see this option. Here you can select the unit that should be incremented (day, weekday, month, or year).
Step Value: This is where you specify the difference between each value in the series (it will be the increment amount or multiplier value).
Stop Value: You can specify a value at which you want the series to stop.
Lets look at a few examples.
Example 1: Creating a simple set of linear numbers, 1-8, in a single row:
Step 1: Enter the first number of the sequence in a cell. In this case, 1.
Step 2: Open the Fill Series Menu
Step 3: Choose Row & Linear.
For Step Value enter 1.
For Stop Value enter 8.
Example 2: Creating a set of linear numbers 1-21, all odd values, in a single column.
Step 1: Enter the first number of the sequence in a cell. In this case 1.
Step 2: Open the Fill Series Menu
Step 3: Choose Column & Linear.
For Step Value enter 2.
For Stop Value enter 21.
Thanks for reading Part 1 of this series. In the next parts I will cover the more of the Fill Series menu and dive into the newest way to tackle this challenge, the Data Array Formula (DAF) =SEQUENCE().
In the meantime, make sure you practice what we have covered today. Here is a link to the sample data so that you can follow along.
When you’re ready, there are a few other ways I can help you master Excel:
Do you have any burning questions about Excel that you want answered? Send your question to ajay@theexceldojo.com. I will address it in a future newsletter.
Do you or your organization need a little spreadsheet help? Book a 1 on 1 with me here.
Til next time ✌️
-Ajay
The Excel Ninja 🥷
PS - If you found this newsletter informative please share with a friend or two...
Thanks for reading The Excel Dojo! Subscribe for free to receive new posts and support my work.
Subscribed