Learn how to use AutoFill to create Lists.
Using AutoFill to Create Lists
Most of what we enter into an Excel worksheet is unique, and each cell contains a completely different number, date, name, or description. But sometimes we need to add content that’s part of a series, a set of common values – such as days of the week or months of the year. We also often have the need to create a series such as a series of numbers that follow a pattern, or a series of dates or times.
When you need to create that series or pattern, Excel’s Autofill feature will make your life much easier and your worksheet content even more consistent and reliable.
First, let’s look at some of your text series options.
Here, in this worksheet, I need to enter the days of the week for a series of tasks that need to be performed every week on the same day of the week. And while it’s not that difficult to type the days of the week manually, wouldn’t it be easier to ask Excel to do it for us?
Monday is already filled in, but when I select that cell and then grab the Fill Handle, I can drag through the adjacent cells and see Excel flesh out the rest of the week, from Tuesday through Friday. The key is to select the cell that’s serving as a starter – sort of acting as a sample so Excel knows what you want to fill in – and drag from there when your mouse turns to a little cross on top of the Fill Handle.
Note that if I drag too far, I can just drag backwards with the same Fill Handle, and remove Saturday from the series.
Bear in mind, as I’ll show you on this blank sheet, you can fill in the series horizontally or vertically, and you can start on any day of the week – Excel picks up right where it should. You can also use abbreviations, as long as they’re recognized abbreviations for the days of the week.
Now, back to our Event Preparation schedule, let’s put in the Months of the year. Just like days of the week, I grab the first one and then drag with the Fill handle, through the cells I want to fill.
Back to the blank sheet, see that I can fill in the months starting on any month, and if I go past December, Excel picks right back up again with January – and I can create a series with standard abbreviations for the months of the year, too.
Returning to the Event Preparation schedule worksheet, check out how Quarters of the year are filled in. Excel knows there are 4 quarters in the year – so if I go past the 4th, it starts over with the 1st – and then I can back that out if I don’t need it.
On the blank sheet, see that the abbreviations that work include Q1, Qtr1 (with or without a space after the letters), or the full word Quarter – and again, we can AutoFill either horizontally or vertically.
This also works for Weeks, as in Week 1, Week 2, and so on. It works with the word Week or the abbreviation, Wk – handy if you’re tracking weekly totals of anything – hours worked, units sold, milestones in a project, etc.…
The same thing works for Days, as in Day 1, Day 2, and so on. And again, you can go vertically or horizontally with your lists.
Now let’s look at numbers. Unlike text, which only requires a starter in a single cell, number patterns require a 2-cell sample. If I type a 1 in a cell and use the fill handle to drag through adjacent cells, I just get the number 1, over and over. But if I type 1 in a cell and then 2 in the next cell, when I select both cells and use the Fill Handle in the second cell, Excel repeats the pattern, incrementing by 1, and I get 1,2, 3, and so on, for as long as I drag.
You can also do patterns that skip numbers such as 100 and 500, which creates this series – 100,500,,000,1500, and so on, each number incremented by the difference between the first two cells.
And hopping over to the Registration Totals sheet, we can create combinations of numbers and letters, and will use that to create Project Number values. I’ll need to add the second number, PR0 (zero)2 (otherwise it will just repeat the one value, PR0(zero)1), but once I’ve done that and dragged with the Fill Handle for the 2-cell selection, it gives me a series of chronologically-numbered projects.
And finally, there are dates. Here in column H, we have the date that the records were updated for the current total registrations. If I drag from one cell, breaking with the need to create a pattern, Excel assumes I want a series of consecutive dates.
But what if I want to repeat one date over and over – because I updated the registration totals for 5 projects on the same date? If I enter the same date into the second cell, I can select that pattern and then use the fill handle – and voila! I have the same date for all 5 of my projects!
And finally – a little trick if you’re setting up a schedule for something that happens on the same date every month. Just type that date for two consecutive months, into two contiguous cells, to create the pattern.
So let’s imagine I want to set up a series of cells displaying the 15th of every month – say as a reminder to pay a bill. I can put in the first two months, and then drag – and now I have 12 months of cells (or more, depending on how far I drag), all filled in for the 15th of each month. If I go beyond the end of the year, Excel knows to increment the year for me, too, and here, I’m starting with October to demonstrate that.
Pretty handy, eh? You should experiment with different patterns for text, dates, and even times – and along the way, you’ll find lots of ways to use this AutoFill feature.