TED #034: Creating Numbered Sequences in Excel - Part 2
Diving into a few of Excels features that allow you to get this done!
Last week we began this journey into understanding ways to create numbered sequences in Excel. We left off using an awesome but not well known tool, the Fill Series option. This week I will dive a little bit more into this option.
Lets review:
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…
Let’s dive into more of these options!
Adjusting the “Step Value” Option
The step value is a number that tells Excel how much to increase the last number in the series by. Let’s take a look. In this example we will start at the number 2, and increase the steps by 2 until we hit 24.
Changing the Type to Growth
This option is basically the Step Value on steroids. Instead of adding the value to the prior number, the growth option multiplies the two together! In this example we start at 1. We select a step value of two. This will multiply each value in the sequence by 2.
Quick note: I do not use a Stop Value moving forward. Instead I highlight the rows I want to fill. Excel will stop at the last highlighted row.
Using Autofill
Autofill will detect a pattern within the numbers and then will fill the series down accordingly. In this example I enter a series of numbers: 3, 6, 9. I ask Fill Series to detect the pattern and fill accordingly. Here is what it looks like:
Incorporating Dates
Choosing the type “Date” opens up another part of this amazing feature - the Date Unit. It works the same as above but instead you can execute dates instead of numbers. Let’s look at each one of the Date Unit options.
Day - This one is self explanatory. It will add the number of days and not exclude any weekends, etc. We will use the step value 4.
Weekday - This option will skip over the weekends. We will continue to use the Step Value 4. Take a look at how the results differ from using Day.
Month - This option adjusts the step value from days to months. Lets look at the same setup, using a Step Value of 4 but selecting month instead. Wait for a few seconds after the fill series is done. I adjust the date format for you to see how it impacts the answer over time.
Year - As you probably guessed this option will adjust the step value to years. Here is what it looks like, keeping the step value at 4.
Thanks for reading Part 2 of this series. I know a lot of you are mad at me because I teased =SEQUENCE() in last weeks newsletter. Not to worry, that will be in the next newsletter for sure. There is a lot to cover and I thought it would be best to give it the attention it deserves.
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.
Be sure to follow me on your favorite social media platforms!
Til next time ✌️
-Ajay
The Excel Ninja 🥷
PS - If you found this newsletter informative please share with a friend or two...