## 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.

Read more: Windows, Productivity

Johnwrote:So what are the formulas.. since you started?

Thanks, nice tidbit!

Posted 04 May 2007 at 6:28 am ¶

Vladimirwrote: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.

Posted 04 May 2007 at 1:53 pm ¶

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

Posted 09 May 2007 at 1:25 am ¶

vijay vagguwrote:good presentation

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

better for the users

Posted 18 Jun 2008 at 11:42 pm ¶

Bharateshwrote:Thanks, Odd and Even number series helped a lot

Posted 15 Jun 2009 at 12:14 am ¶

Durgeshwrote:Thanks…that’s a really nice presentation…wow

Posted 24 Aug 2009 at 11:46 am ¶

Ramratan Guptawrote:How to generate alpha numeric series by formula?

Posted 03 Apr 2010 at 12:13 am ¶

khawarwrote:Very help ful

Thanx

Posted 23 Mar 2011 at 11:07 am ¶

AdibaBhattiwrote: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

Posted 25 Dec 2011 at 10:09 pm ¶

Abhishekwrote: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

Posted 17 Feb 2012 at 11:17 pm ¶

Aaronwrote: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.

Posted 12 Mar 2012 at 2:29 pm ¶

mahalakshmiwrote:great, very nice demo

Posted 27 Mar 2012 at 2:06 am ¶

sharad patilwrote:Very Helpful..

thanku…

Posted 23 May 2012 at 4:28 am ¶

thrinathwrote:Thanks a lot…

Posted 16 Nov 2012 at 6:58 am ¶

leonardwrote:ℓ̊ 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

Posted 12 Mar 2014 at 6:13 pm ¶

jessicawrote:Thank you, seriously I love you!!!!!

Posted 23 Mar 2014 at 5:08 am ¶