MS Excel Sequences Using Formulas
Last week, in Auto-Generate Sequences in MS Excel I described how to create a series in Microsoft Excel using the built-in automatic series tools. (By the way, that post has been updated to include a flash animation of the technique.) In passing, I mentioned that this could also be done using formulas. Reader John asked if I could show what the formulas were. I started a brief reply to his comment and realized there’s more to it than just getting formulas into the cells.
Creating a Series of Numbers: Let’s start the same way we did in the previous article, by entering 1 in the A1 cell. Move down a row to A2 and type =A1+1. Next, drag the small square in the lower right corner of the cell (as shown in the previous article) to however many rows you’d like. Excel will automatically update the A1 cell reference to the next row (or column) number.
There’s also a quicker way to do this if you’re a keyboard-centric person like me. The “fill” keyboard shortcut, Ctrl+D, does a “fill down” (to fill across columns use Ctrl+R which does a “fill right”) of a value or formula. Let’s go ahead and do this the keyboard-centric way:
Using the above example select the A2 cell. Now while holding the shift key down press the down-arrow key a few times on your keyboard. You’ll notice that you’ve selected the A2 cell and as many additional cells below A2 as down-arrow keypresses. Now that you’ve selected the cells you want to fill in, press Ctrl+D. You’ll now notice that you have your series as you did before.
Creating a Series of Odd Numbers: To create a series of odd numbers you’d follow the same steps as above except that you need to change the formula to =A1+2.
Creating a Series of Dates: Just as in the previous article you’ll start by typing a date in the A1 cell. Type 1/1/2007 and press enter. To create a series of dates you can use the same formula as we used to create a series of numbers, =A1+1. The reason we can do that is that Excel represents dates as a series of numbers. (Day number one for Excel is January 1, 1900 for you trivia buffs out there.)
Days of the Week: All the previous examples were only slightly harder that the method I described in my previous article. Unfortunately, creating days of the week and months of the year in formulas becomes much more complicated. I’ll show examples of this just for completeness, but I’ve never done it this way in practice. Again, type 1/1/2007 into cell A1. Fill the cells as in the previous examples. Now you’ll notice that you’ve got a series of dates not days of the week. To change this you’ll need to change the date formatting. Select the cells you’ve just created. Press Ctrl+1 (or from the Format menu, select cells…) to bring up the cell format dialog box. Select the “Custom” category, and then under “Type:” enter “dddd” (four lower-case D’s) to get the fully spelled out day of the week. To get the three letter abreviation you can use “ddd” (three lower-case D’s).
After you press OK, you’ll notice that your text is all right aligned. That’s not exactly what I’d expect, but it makes a little bit of sense because dates are automatically right aligned. (I could have changed this in the format dialog box had I known that it would happen.) Anyway, just click on the left align button in the tool bar to fix this minor glitch.
Creating Months of the Year: By far the most complicated formula I’ll use in this article is the one to create months of the year. Again, you’ll start by entering 1/1/2007 in the A1 cell. In A2 you’ll enter =DATE(2007, MONTH(A1)+1,1). Copy this formula down 10 rows as described above. You’ll need to left justify the text as before.
Because Excel stores dates as the number of days since January 1, 1900 you need to use the date formula to essentially build the date. The date formula takes separate parameters for the year, month and day letting you manipulate each value independently.
So, there you have it, a much more powerful, abeit slower, way of doing what I described in my previous article.
Read more: Windows, Productivity
Mats wrote:
Thank you! This is awesome. I tested it in OpenOffice.org Calc and it works the same. The difference I find is that in OOo Calc you just right click and bring up the contextual menu to convert to the days of the week. Format cells -> User-defined -> Fill in dddd in the Format Code field.
Keep up the good work! I´ve signed up with this blog in Google Reader.
Posted 11 May 2007 at 8:16 pm ¶
Shobhit Mathur wrote:
Hi,
This site looks great. I stumbled upon it as I was looking for help in manipulating data in MS Excel, and looks like I am asking the right question to right set of people. This is what I want to do.
I have raw data that simply contains sales by date. The dates flows almost endlessly as it is a database dump like following:
Tue, Mar 01 2007 , Sales of Electronics
Tue, Mar 01 2007 , Sales of Books
Tue, Mar 01 2007 , Sales of Vanity Gifts
Wed, Mar 02 2007 , Sales of Electronics
Wed, Mar 02 2007 , Sales of Books
Wed, Mar 02 2007 , Sales of Vanity Gifts
And, so on.
I will like to roll it up into a weekly view or a monthly view of each item. I have tried my hands on Pivot Table without success. Do I need Macros for this?
My output format should allow me to see the number of Sales in a Calendar like fashion. Intent is to see periodicity and correlation of sales by the day of the week, or date of the month.
Posted 12 May 2007 at 6:30 am ¶
Vladimir wrote:
Mats - Does Open Office have the drag copy feature that excel does that I described in my previous article? It’s been a while since tried out Open Office. Though I’m a huge proponent of open source software, I just couldn’t get used to the differences it had between excel and word so I don’t have it installed anymore. Excel and Word are by far my favorite Microsoft products.
Posted 12 May 2007 at 2:14 pm ¶
Vladimir wrote:
Shobhit Mathur - you’re on the right track with the pivot table, the only thing you’re missing is that you need to group the dates together by week or month or whatever to have something to pivot on. For both the easiest way to do that is to create a new column to calculate the grouping. Month is pretty easy, there’s a formula you can use, MONTH(), that returns the numeric value of the month 1 trough 12.
Week of year is a bit harder, since there’s no built-in formula for that, you have create it yourself. I luckily found an article written by Microsoft about exactly that.
http://msdn2.microsoft.com/en-us/library/bb277364.aspx
They recommend writing a Visual Basic extension as an excel plug-in and then calling it. That’s some serious work! Sure go for it if you use that in a lot of spreadsheets, but you can also use the following formula (also from the article):
=INT((A1-DATE(YEAR(A1),1,1))/7)+1
Assuming your date is in A1, this will give you the week number that you can pivot on. I don’t think the above is the ANSI standard for week numbering, but it’ll should do the trick.
Posted 12 May 2007 at 2:26 pm ¶
ace wrote:
pls. give a formula of function
Posted 06 Sep 2007 at 5:23 am ¶
Vladimir wrote:
ace - Could you be more specific. I’m not sure what you’re asking for.
Posted 11 Sep 2007 at 1:07 pm ¶
iranna baligeri wrote:
if i enter one date the next cell or selected cell will go up by three months
Ex : FD date start 01.4.2008 the interest date will show in next cell increased by three months
Posted 19 Feb 2009 at 5:15 am ¶
madembo wrote:
I downloaded into excel a list of invoice numbers. Now I want to know if all the invoice numbers have been included. How do I generate a report of missing numbers?
Posted 03 Mar 2009 at 3:15 am ¶
Vladimir wrote:
Madembo: If you’re not doing this often, I’d do the following:
1) Sort the list.
2) In another worksheet use the “IF” formula to see if the current row’s invoice number is 1 greater than the previous and note 0 if everything is ok or the missing number if it is not.
eg. =IF(A2-A3=1,0,A3-1)
3) Extend this formula down for the length of your list.
4) Select “Remove Duplicates” from the “Data” ribbon
Now you’ll be left with only 0 and all the missing numbers.
If you’re going to do this a lot, I’d write a macro to go through the same calculation and add items to a new list.
Good luck!
Posted 03 Mar 2009 at 9:17 am ¶
Raja wrote:
How to increase a cell value, if the month is increased(in character ie. March, april, May etc…)
Pl give me the formulae
Posted 20 Apr 2009 at 11:25 pm ¶
Vladimir wrote:
Raja: the article gives the formula for creating a sequence of months:
=DATE(2007, MONTH(A1)+1,1)
Posted 21 Apr 2009 at 12:13 am ¶