Auto-Generate Sequences in MS Excel

excel snapshotI 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.
excel snapshot
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:

excel snapshot

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.

excel snapshot excel snapshot

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.

excel snapshot excel snapshot

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”).

excel snapshot

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.

Comments

  1. So what are the formulas.. since you started?
    Thanks, nice tidbit!

  2. Thanks for the suggestion. I’ll post an article next week with the above examples using formulas.

    For a quick answer to your question though, the simplest formula you can use is =’CellAddress’ +1, where ‘cellAddress’ is the address of the previous cell in the series. so if A1 contains a 1, A2 would contain =A1+1 which would display the value 2.

  3. UPDATE: There is now a flash animation to illustrate these techniques. It’s at the end of the article.

  4. good presentation

    can you give more detail presentation for advanced level eg. vlookup or creating custom boxes in excel page

    better for the users

  5. Thanks, Odd and Even number series helped a lot

  6. Thanks…that’s a really nice presentation…wow :)

  7. How to generate alpha numeric series by formula?

  8. Very help ful
    Thanx

  9. Thank you so much you save my alot of time, i was looking something like this since last night at least i got it now
    thank you so much

  10. Is there anyway…where we can do it withour dragging..??
    I have to generate millions of numbers…so want something more easier……

    can anyone pls help

  11. Abhishek: Start by selecting the entries you would like to fill. In Excel 2010 (probably Excel 2007 and Excel 2012 also), go to the home ribbon, and on the right side select the drop down Fill menu, select Series. Trend will autofill the selection, or you can enter a Step and Stop value to autofill an arithmetic sequence.

    That fills an arithmetic sequence (linear, d+a*k), but you can also fill a geometric series (ar^k) with type “growth”. Date is probably…dates. I can’t make head nor tail of how Autofill decides what to do.

  12. great, very nice demo :)

  13. Very Helpful..
    thanku…

  14. Thanks a lot…

  15. ℓ̊ have a problem how can ℓ̊ write a sequence on the possible outcome of 15 sports event happening randomly(both team to score, true or false) are the two possible results…please reply soon thanks

  16. Thank you, seriously I love you!!!!!

Post a Comment


Your email is never published nor shared. Required fields are marked *



© 2006-2007 Maxim Software Corp.  All rights reserved.