+'&title='+encodeURIComponent(document.title)+'&overview='+encodeURIComponent(document.title)+'&urllanguage=en&urlaffiliate=32003&encoding=UTF-8&urlpicture=http://www.viadeo.com/v_img14/shareit/viadeoflower.gif')
Let's jump in and look at a very simple project that really is nothing more than presentation. The project makes its own information out of thin air. Many people like to have a small calendar on their spreadsheet or report that always shows the current date. This project will make a live calendar that can be included in any spreadsheet in just seconds after you learn how.
This is an example of what the calendar can look like. It can be placed anywhere on a worksheet. While simple to construct, it does use some concepts you may not have heard of.
SEVEN QUICK STEPS TO BUILD A LIVE CALENDAR
- Define three named ranges using the Define Name dialog:
- DaysAndWks ={0,1,2,3,4,5,6} + {0;1;2;3;4;5} * 7
- DateOfFirst =Date(Year(Now()),Month(Now()),1)
- Calendar =DaysAndWks + DateOfFirst - Weekday(DateOfFirst,2)
- Select cells C3:I8. Press F2 on the keyboard and array enter the formula: =Calendar
- Center format those cells and apply the cumstom number format of "d".
- In C1 and C2 enter the formula: =C5
- Apply the custom number format of "ddd" and center text to C2. Apply the custom number format of "mmmm yyyy" to C1.
- Copy C2 to C2:I2. Apply "Center Across Selection" and a background color to C1:I1.
- Adjust the column widths to your preference and format a box around the calendar.
If that's all Greek to you, don't despair. Someone with the experience to understand those instructions could complete them in less than a minute! If you're in the Greek camp, keep reading...
Here is a video of me constructing the live calendar in real time. It's just under 2 minutes, but I was talking while doing it ;)
Start a new workbook and right-click on the Sheet3 tab at the bottom of the window. Select Rename and change the name of the tab to Scratch. Select columns C, D, E, F, G, H, and "I" at the top of the sheet by clicking on C and before releasing the click, drag to the right until the mouse pointer is hovering over I. Now let go of the click and right-click anywhere in the shaded, selected area. Select Column Width... from the context menu and type the figure 4.29 into the box. Click on OK. To make this calendar we first need a grid of numbers ranging from 0 to 41. The first thing you need to learn is the Auto-Fill feature of Excel. Instead of typing 0,1,2,3,4,5,6 in the first row and 7,8,9,10,11,12 in the next and continuing that for a total of six rows, the Auto-Fill will make quick work of this task. Just enter 0 and 1 into cells C3 and D3, and enter 7 and 8 into cells C4 and D4.
Now just below the 8 and slightly to the right you'll find a tiny, black, square anchor. Click on this and drag to the right to include everything through the "I" column and let go of the click. Now click the anchor again and drag down to include all rows through 8 and let go of the click. It should look like this:
You've just entered 42 numbers instantly and accurately, certainly better than typing them all. Excel is quite smart and can figure out many patterns. Try this technique in different situations. It will often save you lots of time. If it doesn't produce the results you wanted, just click the undo command from the Edit menu.
These figures are still static numbers however. What we really need is one formula that will calculate all of them. When designing spreadsheets, this should always be your goal, crafting a single formula to fill a large range of cells. So let's make one. Select all the cells that have numbers in them. You can do this using the Edit menu and then Go To Special... Select Constants and click on OK. [Excel 2007 Note: Go To Special... is under the binoculars on the Home tab of the ribbon.]
After the cells are selected, press F2 on the keyboard. All formulas should begin with an equal sign. Type ={0,1,2,3,4,5,6} without the quotation marks and then on the keyboard press Control-Shift-Enter together. That is, press three buttons on the keyboard simultaneously. This places the formula into the formula bar at the top. This formula will apply to all of the selected cells.
For our very first formula in this blog we are using an advanced type called an array formula that most users never discover. As an Excel Hero you will use them frequently. They do impressive calculations, some that are not possible any other way. Some refer to them as CSE formulas because of the keys on the keyboard required to input one. If you had not hit all three keys simultaneously the formula would be a normal formula and would work incorrectly. In this case we have entered an array of constants within our array formula. The fancy parentheses tell Excel that we are entering an array of constants. When we press Control-Shift-Enter on the keyboard, Excel will enter another pair of fancy parentheses around the entire formula. Excel does this for us; we need only type the fancy parentheses around the array of constants. At this point, it should look like this:
What this has done is given us daily offsets, meaning Sunday is 0, Monday is 1, etc. If we add these to weekly offsets then we should have our 0 to 41, produced by formulas. Let's replace the daily offset with a tiny formula for weekly offsets. Make sure the cells in the calendar are all selected like before and then press F2 and type the following into the Formula Bar:
={0;1;2;3;4;5}*7
Now press Control-Shift-Enter on the keyboard again. The asterisk means multiply. Notice that we have another array of constants surrounded by the fancy parentheses. You'll see that the result in the cells is quite different. We now have weekly offsets. Notice that the constants are separated by semicolons instead of commas. In Excel arrays, commas separate items horizontally going across a row. Semicolons separate items vertically going down a column. This formula is telling Excel to take the first item, 0, and multiply it by 7 and place the result in every cell of the first row. This process continues for each respective row. It should look like this:
Now let's combine the two formulas. Select the cells and type into the Formula Bar:
={0,1,2,3,4,5,6} + {0;1;2;3;4;5}*7
and voila, we have our numbers back, but calculated by formula. All that is happening here is that each cell is now the result of both our previous formulas added together. You should have this:
Let's change the format of our calendar cells. Select the cells, right-click and choose Format Cells... On the Format Cell dialog make sure that the Number tab is selected at the top. Under Category select Custom and in the box directly under "Type", enter "d" without the quotation marks and click OK at the bottom. This tells Excel to present each number as a day of the month. The numbers in the Calendar look a little better. We now have 31 as the maximum. Now select the cells again, press F2 on the keyboard and add the following to our existing formula without the quotations:
+ Date(Year(Now()),Month(Now()),1)
Remember to use Control-Shift-Enter on the keyboard to enter this array formula. The entire formula should now be:
={0;1;2;3;4;5}*7 + {0,1,2,3,4,5,6} + Date(Year(Now()),Month(Now()),1)
While this may look completely foreign, don't be intimidated. The phrase we just added is nothing more than a formulaic way to identify the date that is the first of THIS MONTH. This is exciting because this allows our calendar to display the LIVE date when we are working on whatever worksheet we put it in.
In simple English we are adding to our existing offsets the date in which the year is now, the month is now, and the day is 1. Depending on what month you are reading this, your calendar will now look something like:
OK, but the 1st was not a Sunday! Let's fix that. Excel has dozens of built-in worksheet functions to do all sorts of different calculations and lookups. One of these is the WEEKDAY function. It simply takes any date, January 18, 2010 for example, and calculates the day of the week in numerical representation (Monday = 1, Tuesday = 2, etc.). In this step, we need to determine what day of the week the 1st was and subtract that number from our existing formula. This is the last step, the last fragment of our formula!
If all of this is new, your head may be spinning, but please hang in there. This type of project will seem old-hat in no time, I promise. We are learning a new way of thinking, instructing Excel to do the hard work. The formula fragment that we need for the last part then is: - (WEEKDAY(1st of month, 2)). Of course, when we really enter it, we need to replace 1st of month with the fragment that we used in the previous step to calculate the 1st. Look at the ", 2" near the end. This is an option to the WEEKDAY function. Placing a 2 here instructs the WEEKDAY function to make Monday = 1. The final, entire formula, array entered of course, should be:
={0;1;2;3;4;5}*7 + {0,1,2,3,4,5,6} + Date(Year(Now()),Month(Now()),1) - Weekday( Date(Year(Now()),Month(Now()),1), 2)
Now, while all the cells are still selected, center format them. Just click on the center format button at the top of the window. Let's add the Day titles. In cell C2 we are going to enter a tiny, normal formula (NOT a CSE):
=C5
Let's immediately format this one cell to change its presentation. Right click on it, choose Format Cells..., Number tab, Custom, and enter "ddd" in the Type box (without the quotation marks). While you're at it also change the justification to Center Text on the Alignment tab and click on OK.
Now click on the tiny anchor at the bottom of this cell and drag to the right through column "I" and let go of the click. Let's also add the month title at the top. In cell C1, enter the formula:
=C5
Yes this is the exact same formula we put into C2. We'll just format it differently. Before we do, select all the cells at the top of the calendar at the same time, C1 through I1. Right click on this selected area and go to the Alignment tab of the Format Cells dialog once again. Under Horizontal, select Center Across Selection. Switch over to the Number tab and under Category choose Custom and in the Type box, enter: "mmmm" without the quotes. Switch over to the Fill tab and click on one of the colors for Background Color; click OK. Wow. It really looks like a calendar now. Your calendar should look similar to:
What about the dates before and after this month? Can't we make them dimmer? Select all of the cells with numbers in them. Click on the Format menu at the top of Excel and select Conditional Formatting... Under Condition 1, select "Formula Is" and then enter this small formula into the box just to the right:
=Month(Now())<>Month(C3)
and click the Format button. On the Format Cells dialog, pick a very light grey under Color. Click on OK and OK again. Go back to the Conditional Formatting dialog and click the Add button on the bottom. Under Condition 2, select "Formula Is" and enter:
=C3=INT(Now())
and click the Format button. On the Format Cells dialog, pick white under color and Bold above that. Switch over to the Patterns tab and choose a background color for the current day highlight. Click OK and OK again. Now select all of the cells in the calendar, including the titles and place a box around them by right-clicking on the selection, Format Cells..., switch to the Border tab and click on the Outline button; click OK at the bottom. And we're done! It should look like this:
While normal formatting is useful and essential for presentation, Conditional Formatting kicks that up a few notches, as Emeril might say. Conditional Formatting is extremely powerful and we will use it extensively throughout Excel Hero.
This tiny project has been instructive. We've learned:
- There are many ways to do just about anything in Excel.
- Better methods are faster, shorter, easier to maintain, or simpler, and in that order.
- Excel can auto-fill cells by recognizing a pattern. This is done by clicking and dragging the anchor.
- Formulas should always start with an equal sign.
- Excel has dozens of built-in formulas.
- These can be combined to create countless custom formulas.
- CSE or array formulas are potent and are entered by Control-Shift-Enter.
- Arrays of constants need to be surrounded by fancy parentheses.
- Horizontal elements are comma separated; vertical elements are semicolon separated.
- The date system used by Excel makes date arithmetic easy.
- Formatting can dramatically change the way information looks.
- Conditional Formatting takes presentation to the next level.
So what can I do with this live calendar? For starters, you can take a picture of it and place the picture anywhere in your workbook. Two things are interesting about this. The picture floats above the Excel grid and is not limited to the underlying rows and columns of where you place it. This can really help in formatting your entire page for presentation. Secondly, the picture itself is live, so if the original calendar is updated either by the computer clock or by changes you make to the original's layout or design, the copy updates as well.
I will introduce one more idea in this post. What if our long, custom formula is really annoying, or just confusing? Excel offers a robust subsystem that allows you to assign names to your custom formulas. They modestly call this feature, "Named Ranges." In Excel a range is a group of cells (a single cell is also considered a range).
Most instruction on Excel teaches that the idea behind named ranges is that you can select some cells (or one) and invent a name for that range, which the workbook will remember. This can make your custom formulas much easier to read than all of the cell references. However, in my opinion, the fact that you can name a range, is almost beside the point. If you were to name cell C5 as MyFirstRange, Excel stores this in a workbook table as MyFirstRange: =C5. As you can see it is a formula, because the definition of the name starts with an equal sign.
Extreme kudos are in order for the designers of Excel on this topic because what this enables is Named Formulas. In fact this feature of Named Ranges should have been called Named Formulas. You can have just about any formula, simple or extremely complex, defined as a named formula. The name does not even need to refer to a range at all.
For example, the following are all valid named ranges: =5, ={1,2,3}, =Now(), ="Excel Hero", =SQRT(ROW())-1. That's five named range definitions that have no reference to any range whatsoever. I could just as easily give you a thousand. From now on this blog will always refer to named ranges as Named Formulas.
To define a named formula for a worksheet or workbook, just click on the Insert menu at the top of Excel, select Name, and then Define. On the Define Name dialog, create a name at the top and type a formula at the bottom, and then click on Add or OK. Later you can manage your named formulas by modifying and deleting. We will be delving deeply into that later. What if we define three named formulas for our calendar as follows?
- DaysAndWks ={0,1,2,3,4,5,6} + {0;1;2;3;4;5}*7
- DateOfFirst =Date(Year(Now()),Month(Now()),1)
- Calendar =DaysAndWks + DateOfFirst - WEEKDAY(DateOfFirst,2)
We would then be able to array-enter our custom formula for all of the day cells as:
=Calendar
Is that a little easier to read? What could be simpler? Notice that none of these formulas refers to a cell or a range. They truly are named formulas, not named ranges.
[Excel 2007 Tip: Conditional Formatting is on the Home tab of the ribbon; select Manage Rules, then New Rule, click on "Use a formula to determine which cells to format" and enter the first formula into the box below, click OK and then New Rule to add the second formula in the same way. Pick the colors as described at left.]
This is not difficult stuff at all; it just may be a lot of new things all at once. Please stick with it. You'll learn that a lot of these menus that we clicked through have shortcut alternatives that eliminate most of the work. If you keep reading this blog, the concepts and processes will become so old-hat that you will be able to construct this entire calendar from scratch in a new workbook in less than a minute.
Once again, here is that video of me constructing the live calendar in real time. It's just under 2 minutes, but I was talking while doing it ;)
If you liked this article, please share it!
Hello. This is kind of an "unconventional" question , but have other visitors asked you how get the menu bar to look like you've got it? I also have a blog and am really looking to alter around the theme, however am scared to death to mess with it for fear of the search engines punishing me. I am very new to all of this ...so i am just not positive exactly how to try to to it all yet. I'll just keep working on it one day at a time Thanks for any help you can offer here.
@pop - I just selected the Professional Theme in MovableType 5 for the blog.
Nice approach to the calendar; you really pushed my understanding of array formulas with this one.
For a short month such as this one (February 2010), the resulting calendar looks a little odd. Since the month ends in week #5, the entire 6th week of the calendar is dimmed (because it's really March). Ideally, week #6 would disappear entirely in a situation like this.
Sounds like a job for a bit more conditional formatting...
A thoughtful opinion and ideas I will use on my blog. You've obviously spent some time on this. Congratulations!
You sate that a picture of the calendar is live and will be updated along with the original. This is not the case in Excel 2003.
@Terry -
Did you use the Camera Tool to make the picture?
No, Daniel, I used the Shift/Edit/Copy Picture feature. Just tried it with the Camera Tool (never used that before) and it works as it says on the tin (and as you said). Nice blog, by the way. Thanks
@Terry -
You are welcome.
Glad you were able to get it working. The Camera Tool can be used in many a creative situation...
Regards,
Daniel Ferry
excelhero.com
EHA student read and understood :-)
@Terry, you don't have to use the Camera tool to create a dynamic image. Use the Copy Picture feature, paste the picture, then select the picture, click in the formula bar and type the reference to the cells you want the picture to show, e.g.
=$A$1:$D$5
cheers,
I have been practicing this at least once a week for well over a month to help drill it in and understand it better - long before starting the EHA
Oli
I have been practicing this at least once a week for well over a month to help drill it in and understand it better - long before starting the EHA
Oli
EHA student
After skimming the article and viewing the video - I was able to follow the "Seven Step" program and create a minimually formatted calendar in 8 minutes. Another 10 minutes got the formating and the picture. I will have to start practicing like music43. Not possible pre-EHA.
3 things:
1. I like how you can resize the picture and Excel tries to keep the proper proportions.
2. This exercise re-emphasized the comma-semi-colon distinction (horizonital/vertical portions of an array).
3. You should add to you list of items learned (and you memtioned it in the multiplication tables module) - where the cursor is when the formula is created matters. Conditional formatting is another great example of that statement.
Thank you excelhero.com.
OK, I see what you are doing and it makes sense as shown, but I still don't think I'm 100%. That is, the light bulb is still pretty dim.
Maybe some additional examples might help?
EHA Student
For those using the non-english version of excel you might get some confusion as I did.
The "\" sign is used in the array constants formula to identify the Column while the ";" sign is used for the row identification
In Daniel's Name formula "DaysAndWks" you should type the following to make it work:
DaysAndWks={0\1\2\3\4\5\6}+{0;1;2;3;4;5}*7
Hope it helps
EHA Student
I have always struggled with understanding arrays. It is getting clearer now, but still some work to do. I finally have the "," vs ";" distinction straight now.
Todays short lesson was very good. I actually liked todays appproach (shorter videos and assignments). However, I am going to let our master what he thinks is best for all. In any case, I did well replicating the the calender. I still need to touch it up a bit format wise.
@ Michael Pierce
The conditional formatting necessary to remove the last week if the whole week is grey, would be to select the entire 6th week, and add the conditional formatting formula of =MONTH(NOW())>MONTH($C$8). Now choose white for the font color, choose a solid black line for the top boarder, and remove the bottom and side boarders. Make sure that this is the first rule in the set of rules. Unfortunately, conditional formatting does not allow for the thick black line used for the outside boarder of the original calendar, so a thin line would be one option. Alternatively you could add the thick black line to the first 5 weeks and add one more line of conditional formatting to the 5th week to change the bottom boarder to the thin grey line when a 5th week is required.
Even more fun can be had using ingeborg.hawighorst's suggestion of typing in the range for the picture link. Amazingly, this range can use Named Formula's, which means we can create a picture that will expand and contract based on the number of weeks required.
EHA student
EHA Homework.
I've played with this a few times over the last few months and will no doubt carry on until the concepts are cemented in my mind.
Excellent little tutorial.
EHA Homework
Another example of how to use array formulas and conditional formating to produce dynamic results with very little effort.
EHA Homework
Fantastic. I stumbled upon this a month or so ago. Makes a heck of a lot more sense now.
Also, never even knew about the camera function in excel until I just read this now. Holy useful!
EHA. I feel like this could be an exercise like multiplication tables - rehearse it every day or every week for a while until the primary concepts are second nature. It's nice that it the project itself is contained / limited (can be done in a few minutes), but involves a variety of useful skills. Daniel, if you can come up with more of these kinds of exercises, we could add them to our multiplication table repertoire!
Well I've built it using the instructions - with the 3 range names slightly amended - using TODAY() instead of NOW().
NOW() I have to sit and think how and why it works.
EHA student
I think I get it
I modified one of the names to make more sense to me. The DaysAndWeeks is a 7 x 6 matrix of sequential numbers that are added to the current date and you deduct the weekday number of the current date so that you can guarantee that the first of the month is always on the first row.
Pretty neat!
Calendar = DaysAndWeeks+DateOfFirst-WEEKDAY(DateOfFirst,2)
DateOfFirst = DATE(YEAR(TODAY()),MONTH(TODAY()),1)
DaysAndWeeks = {0,1,2,3,4,5,6}+{0;7;14;21;28;35}
EHA student
This is a great post - helps my understanding of array formulas after watching module 3 for EHA. The comma / semicolon distinction is one of those simple things that i wish i had known years ago.
I agree with @terry that remembering that where you are when you create a named formula matters. The 1st module of EHA makes this point much better, and its start to become a pervasive thought in my work as i go through EHA.
Im liking the 'all cell references are relative to where I am' so much that im starting to work in r1c1 notation - something i never ever thought i would do.
keep it coming, Daniel - you're pushing me hard but i love it.
Jesse Warburg
Hi Daniel,
I had read this at the same time as your sumproduct article.It is a very good example as what arrays are and how to use them.
Being so small and compact makes it easier to
analyse,play with it and understand the concept.
A few more of these practical examples would certainly help to digest arrays and array formulas.
Martin L (EHA student)
Read and understood. I walked through the steps. I didn't get why you set the title of the days/month to C5. So I set mine to C3. And found out why - C3 is not typically going to represent the current month.
I did a little experiment --- I was wondering what would happen with the conditional formatting if the first day of the month was a Sunday. If I replace DATE(YEAR(NOW()), MONTH(NOW()), 1) with DATE(2010,8,1) and adjust the conditional formatting similarly, it still works just fine --- the first Sunday shows up in the second row of dates. Which means that C3 can never be the current month. Very cool.
Janice (EHA)
Useful explanation and applicatio of arrays.
This was very helpful. I now need to practice the concepts daily.
Plis, help me. I'm noob. I select cells c3:i8 and press f2 do input ={0,1,2,3,4,5,6}. But when i press Ctrl+Shit+Enter, return the comand in all cells that i selected. Don't return 0 1 2 3 4 5 6 like example. I'm using excel 2003.
Hi, I followed this so carefully but my live date is a week behind the actual date. How can I fix this??
I work with a Dutch Language version of Excel 2010.
Therefore the seperators had to be adjusted for a bit.
When using the Name
DaysAndWks = {0\1\2\3\4\5\6} + {0;1;2;3;4;5} * 7
and replace the comma with the backslash, it works fine.
Regards
Gerard
Very good example of your use of named formulas and arrays and really helped me understand arrays. Daniel I was trying to figure out how you could have a blank cell under each day (by modifying your array formula to be wither non contiguous or incorporate blank cells into the array)for notes pertaining to that day. I could figure it out but then I just entered 90% transparent text boxes for each day and right aligned/top the cell to accomodate these boxes and this works better anyway - what a useful tool!
Hi Daniel!
Your writings here about live calendar 2013, is a kind of a very usful and practical informations for me. Clear serene way of explaination.
1.- Regarding to others peoples comments: I made excel 2013 understand the two kind of array formulas by changing Regional settings list separation to ","(commas).
2.- John H(18th of Feb 2012) is asking about how to use the benefits of an array formula feature(if I´m right) simulationally to extra empty row(and/or column) next to(in between) the resaults of an array formula. In other words: How to make array, multiply/calculate itself to every second row and/or column. Is there anyone who knows how?
3.- I´m designing a 12 mounth cal, with extra row and column for evry day, all the year/sheets in a workbook and need to connect the 12 mounths/12 sheets. How´d you design the continuation?
4.- Can someone tell me the logic/method or the code behind a dropdown-list(menu) which affects on all other 12 mounths/sheets in your workbook?
-------
My way so far to create extra empty cells for notes is to create a new space for every mounth which I make visual, including that extra space for notes. I just used equal-sign(=) for every cell in my/your cal and inserted extra column and rows after the calculation(the Dummy way). The image of my calendar, so far: https://www.dropbox.com/s/l1nme95t0ywrnm0/Dagatal.png