Automagic Lists

Every so often, every Excel user needs to create a list.

This is a fact of life, and and every Excel user across the world accepts it as fate decreed from the heavens. You know the kind of lists that we are talking about – you might have to enter dates in succession, perhaps, or maybe it just involves a long column of numbers from (heaven knows why) 2390 to 5630.

The thing is, creating this list can be an incredibly banal task (unless you are in the know).

Well, today’s banal dispenser… err… Excel Training…. is about lists – specifically, about how to automagically create lists.

Let’s begin with a fairly simple example.

Here’s a series, the beginning of which has been typed out manually:

five thingLet’s say this series needs to be extended upto 10. Now, typing out those seven numbers shouldn’t take very long at all, but what if the list had to go out to a 100? That’s where auto-fill comes in pretty useful. All you need to do is select the series typed out thus far (think of it as the ‘seed’ for Excel to know what to do), and move your mouse over to the lower right corner, until the icon changes from the usual white cross to a small black cross… like so:

… and that’s it! Just drag the series down for as long as you like.

Here’s a little known tip – it works the other way too! Select the cells in which you have typed in 1,2,3 and move your cursor over to the bottom right until it becomes the stubby little black cross that enables automagic fill. Now, instead of dragging the series down, drag it up.

Full of pleasant little surprises is Microsoft Excel, isn’t it?

The best part is, this feature extends to dates as well! If you were to type in the 1st of January 2011 (say) and extend that series out, Excel would assume you wanted daily increments and fill out the series for you.

And what if you wanted monthly increments? Well, no problem there. Type in Jan 1, 2011 in the first cell, Feb 1, 2011 in the second… and use the automagic drag. That’s all!

You need not restrict yourself to just the first of each month, or to a monthly frequency. It could be a week (and successive weeks) out from the 14th of July, if you so wish.

There you have it then, automagic lists.

How do you use the auto-fill feature in your course of work? Do let us know in the comments below!