Hi, this is Wayne again with a topic “Quickly Fill a Huge Series of Numbers or Dates in Excel”.
In this excel video, i’m going to show you how to quickly fill a series of numbers or dates in microsoft excel and we’re going to do this without using the autofill handle. So here in my excel workbook, i’m creating an inventory, and this inventory is for a warehouse that has tens of thousands of auto parts and it’s likely to grow to be even more than that. So i am not looking forward to giving each item in the warehouse its own item number, those of you that are familiar with the autofill handle in excel. You know that i could just put the number 1 in b4 i’ll just click on b4 put 1 and then i could go to b5 put 2 put 3 and b6 and then i could click and drag to highlight this series that i’ve created and then i’ll Click and hold the click on the little green square in the lower right corner of the selection, and as i pull that autofill handle down the spreadsheet and then release the mouse button.
Look what it does it extended. The series that i started with one two three and it extended it all the way down. As far as i went, but that’s for just 800 rows. What if i need 10 000 or 50 000 or 150 000 – i’m not looking forward to putting those numbers in, even if i use the autofill handle, so i’m going to hold ctrl and tap z to undo that, because all i really need is the number one Here in cell b4 and then i’ll just go to the home tab in the editing group. Here we have the fill button.
Sometimes it’s spelled out. Sometimes you have to put your mouse pointer on it to see fill. But when you select that button there are several options and we’ll look at some of these at the end of this video. But for now i want to focus in on just this option series so i’ll click that do.
I want my series of numbers to show up in rows or in columns in this case. I want it to be in columns column b. I want it to be filled with the number series that i’m about to create.
If i select rows, then row number 4 will be filled with this number series so columns next. What type of a series am i creating in this case it’s a linear series, but there’s some other exciting options to choose from below i’m going to leave trend, unselected, we’ll look at that. A little later, the step value just means going from the first number to the second number. Do you want to move up one at a time, two at a time, five, at a time, in this case, one at a time? What’S the stop value, let’s say for now we’re planning on two hundred and fifty 000 different nuts and bolts and other auto parts in our warehouse, so i’ll stop at 250 000. I click, ok and look how fast that was that quickly. I have a series from 1 to fifty thousand to double check that i can just hold the ctrl key and tap the down arrow on the keyboard, and it takes me down to the bottom of my data. There we have two hundred and fifty thousand i’ll hold ctrl again and tap the up arrow to get back up. How much longer would that have taken me if i had just used the autofill handle, i think it would have taken a bit longer. Let’S look at a second example, if i click here on my second spreadsheet exercise tracker here, i want to track the exercise that i do each day and i’d really rather not type each date into the spreadsheet.
I don’t even want to use the autofill handle. I would like excel to put all of the dates in there for me, so i’ll put in may 1st 2022 tap enter on the keyboard and then i’ll, select, b4 and again home tab. Editing, group click here on the fill button and i’ll choose series again. I want the series to be in columns column b, so i’ll switch this to columns excel automatically figured out that this is a date, and so it’s selected date now. What type of dates am i interested in day? In some cases, though, you might want weekdays, or just months or years i’ll, stick with day step value. I want every day added.
Stop value is december 31st of the year 2175. I’M planning on some improvements in the human lifespan in the next couple of years. So i’ll just click, ok, and look at that. How beautiful is that i can browse down the spreadsheet using this slider here every single day is listed through 2122. Again. If i want to see the very bottom of the data, i can click and then hold the ctrl key tap the down arrow there. It is december 31st, 2175. i’ll hold ctrl tap the up arrow.
Let’S take a look at some other things you can do with the fill series tool. Now, what if i have a trend like 1, 3, 5, 7, that’s definitely a trend and then i’ll click and drag to select the trend and more now, in this case it’s a little less handy for filling all the way down the spreadsheet. But let’s say i want to continue this trend all the way down dozens and dozens of rows with that range selected i’ll go to the same button choose series, but this time i’ll select, trend, click, ok and look. The trend that i established is continued as far down as i had selected in this case to row 208.
You can also do a similar thing, but with growth, so i have one and five and then i’m going to click and drag to select one and five and then also go beyond it. I’Ll just go down to row 30, this time with that selected i’ll, go to the fill button series button and i’ll switch it from linear to growth, click, ok and look what it did. Each number is going up exponentially now. If you want to try more of the fill options, you could take a look.
For example, at these arrows we have a down arrow right, arrow up, arrow and left arrow. So if i select a range watch, what happens if i then go to the fill button and choose down so what this did is it took the first number and applied it to every cell in the range? Let’S try the same thing but with words. So i’ve added a beautiful phrase here in cell g2. If i click and drag to select a range, i can then click the fill button, click down and it’s filled all the way down.
So, in some ways this is kind of similar to the autofill handle at least when you’re, using these arrows. So in this article, we’ve looked at how to use the fill button and the series options to very quickly fill a series of numbers or dates in excel. I think you’ll find this to be a great time saver in lots of different situations thanks for watching. I hope you found this tutorial to be helpful.
If you did please like follow and subscribe, and when you do click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, consider clicking the thanks button below the video or you could support me through my patreon account, or by buying channel merch and you’ll, find more information about those options. In the description below the video .