Auto-Generate Sequences in MS Excel
I admit it. I’m lazy and I don’t like to type. So, when it comes to typing up sequences, or “series,” of data (numbers, dates, days of the week), I let Excel do the work.
I’ve used Microsoft Excel since version 2.0 on the Mac. I even sometimes prefer to use it as a “word processor” over Microsoft Word, or in conjunction with it. Its facility for automatically generating sequences is the main reason why. I’ll use the series feature in Excel to create the data and then paste it into a Microsoft Word document, or into an email, etc.. I do this with days of the week the mostly, but let’s start with the simplest of examples first.
UPDATE: There is now a flash animation illustrating this technique, below.
Creating a Series of Numbers: First, type a 1 in the A1 cell and 2 in the A2 cell. Select the two cells. You’ll notice a little square in the bottom right corner of the selection. I have it circled in red below to highlight it. It’s easy to miss. Next move your mouse over the little square. When you hover over it you’ll notice that your mouse pointer becomes a cross. You can do a couple of things by dragging the square. First, if you drag it to the right it copies the column over to the next. That’s not that big of a deal. I prefer the ctrl+R hotkey to do that much faster. But if you’re more mouse-centric that may be useful to you.
Now if you drag the square down, Excel does something very interesting. It figures out that you have a series of numbers that you want to extend. Dragging to row ten gets you:
So with no typing you were able to add 8 more rows. Nice.
Multiples of Two or More: The next thing Excel lets you do is create a non-sequential series, say odd numbers, though any multiple of the previous number (or numbers) works fine. Begin the series, by giving Excel just enough information to create the series. For a series of odd numbers, type 1 and 3 and then drag the two cells just as before.
Creating a Series of Dates: So far I’ve only shown examples of series created in the rows of a single column, but Excel will create them across columns too. This is really handy for creating column headings of dates or days of the week. With dates it becomes even simpler because Excel immediately assumes that you want to create a series with only one date. You don’t need to type two dates as you do with a number series.
Days of the Week, Months of the Year: At the beginning of this article I mentioned that I often use this feature to create the days of the week. The first time I did this was actually by accident and I was really surprised that you could do this. Just type the starting day (“Sunday” or “Sun”) and drag like in the other examples. Excel will fill in the rest of the days for you in exactly as you’ve typed it, preserving your capitalization and even abbreviations. You can start with any day of the week. If you drag over more than 7 cells, Excel will just start over at the beginning of the week and keep going. Same thing applies for months of the year (“January” or “Jan”).
The Techniques in Action: Here’s an animation showing all of these techniques.
[kml_flashembed movie=”http://www.codejacked.com/wp-content/uploads/2007/05/excel_day_series.swf” width=”321″ height=”274″ /]
The Formula Option: By the way, everything I’ve described here could also be done with formulas, but that method is slower. Using formulas is more flexible though, so I recommend going the formula route if you need to change the series frequently.