All posts by Admin

Excel Universal Calendar Template

Happy New Year to all Excel Hero readers!
One of my very first posts on this blog was about the Live Calendar, and that was almost two years ago!
What an amazing community has developed here and on LinkedIn and especially at the Excel Hero Academy in the interim.
To celebrate our two year anniversary as a community and since it is the dawn of a new year, I thought it only appropriate to share another calendar.
But this one is truly special, a veritable tour de force of the Excel methods that we champion here.
When I develop Excel solutions, I try to componentize as much of the solution as possible.
The VBA is broken down into focused, reusable subroutines and functions. Variables are used to hold values that will be referenced more than one time. The With statement is used for object properties and methods that will be used more than once in close proximity of code execution.
But this methodology does not end with VBA.  On the front end of workbooks, I use and reuse Named Formulas in exactly the same way as variables in VBA, meaning I define names for any formula that will be reused.
All of this is done to maximize maintainability and to simplify complexity.
Today I’m sharing a Universal Calendar. While it does not make use of any VBA, it very clearly shows the power of componentized named formulas.
I’m proud of this workbook, as it is enormously instructive.  Everything is generalized.  The user can specify the year and month (school years start in September for example) that the 12-month calendar begins.  Likewise, the user can specify the day of the week the calendar starts with.
Then the fun really begins.  The user can select one through four date types to display on each day of the calendar from a pallet of seven date display types, and in ANY order.  The types are the normal calendar day (1-31), Ordinal Date (what many people incorrectly refer to as Julian Dates, and is really the day number for the year), Ordinal Date Custom (which is the day number since the start of the user’s custom start month for this calendar), Days to End of Year, Days to End of Year Custom, the true scientific Julian Date, and finally, BLANK.
Formula Symphony
This is all accomplished through named formulas, with no scratch areas on any worksheet and no helper cells.
Another interesting aspect are the formulas used to calculate the holidays, such things as the 3rd Thursday in November, the last Monday in May, and the like… and they work on a 12-month calendar where the starting date is specified by the user. For example, if the Custom Calendar Start month is February, then January comes after February.  So the formulas need to take this into account and work for any start month when calculating the standard holidays. This Universal calendar also allows the user to specify hard dates that will be counted as a holidays as well.
All of these dynamic calculations and dynamic reporting output is lightning fast. However, the workbook is not.  Because adding the conditional formatting required for the holiday lookup to the 2,220 cells in the calendar really slows down the performance.  On my computer running Excel 2010, the workbook takes about one second to recalculate. Usually this would be unacceptable performance for one of my models, but in this case I think it does not really matter.  A dynamic calendar is not something that is updated often.
If I had allowed myself to utilize VBA, the resulting calendar would be instantaneous, but I purposefully chose a pure formula play for this project since many companies restrict VBA usage and I wanted to make this Universal Calendar Template useful to as many individuals and departments as possible.
Explore the Workbook
When you open the workbook, first go to the setup sheet.  Play around with the various settings to see just how dynamic the calendar really is.  Study the formulas for the calculated holidays.  The technique is a special case of generalized approach that can be used in any situation where you need to find the Nth occurrence of a criteria from a list, either from the beginning or the end of the list.  The wrinkles here are that the list of dates is virtual (not in cells), and created as arrays within named formulas; and the other wrinkle of course is the fact that the months are in an unpredictable order!
Next, open the Name Manager and study the named formulas.
I’m calling this the Universal Calendar because it is a template that will work in all versions of Excel from 2000 onwards.  It should work in virtually all language versions as well. It allows the user to select the start year, the start month, and even the day of the week to be in the first column of the calendar.  It automatically calculates standard US holidays.  It allows for custom holidays and will display and highlight those holidays on the calendar. And it supports seven date display types and allows the user to select up to four of those date types to display on each day of the calendar – Calendar day numbers, Ordinal Days, Ordinal Days Custom, Days to End of Year, Days to End of Custom Year, true Scientific Julian Dates, and BLANK. And the four selected date display types can be displayed in any order on each day. And finally, if you are using 2007 or newer, this calendar fully supports Excel Themes.
The key to all of this is a contiguous array of dates in memory for the entire custom year that is indexed appropriately for each display day.
Get The Calendar
I feel that the average Excel user could learn a tremendous amount by studying this workbook. If you have not yet had a chance to enroll in the
Excel Hero Academy, please consider doing so.  The next class starts in a few weeks!  You can learn how to design solutions like this, taking Excel to the Next Level… and you’ll learn all about VBA as well.  There is no other course like it.
Here is the elegant Excel Universal Calendar Template:
To learn more about wielding Excel formulas like a master, read these pillar articles here at Excel Hero:
Enhanced by Zemanta

VBA vs Macros

A couple of months ago, Microsoft asked me to take a new, student grades template for teachers and enhance it with a little VBA so that it could print an entire class of student progress reports in a batch.
The existing template was built by fellow MVP, Beth Melton. It’s a good example of some of Excel 2010’s features, including Excel Tables.
It only took a few minutes to enhance the workbook with the VBA required to print each progress report in a batch.  It was very well received by Microsoft and they asked me if I’d be interested in penning an article for their Excel Blog specifically about how the code works.
The process of having an article published on their site is fascinating. A lot goes into it from their end.  And the experience is very different from what I’m used to with my little blog.
But I am happy to report that they published my article a couple of days ago!
Here it is, my very first article on Microsoft’s Excel Blog:

Chandoo’s VBA Class

Right now the Excel Hero Academy is full to the brim with students learning how Excel really thinks.

A big component of this is VBA or Visual Basic for Applications.  While VBA is the mechanism that Excel uses to record macros, it is so much more than this.  It’s a full fledged proramming lanuguage in it’s own right and when you understand how it works, you begin to understand the rest of Excel in a completely new light.
If you want to weild Excel to do your bidding in your workplace, VBA is a must.
Chandoo’s VBA Class dovetails perfectly with the Excel Hero Academy course.  You will be perfectly prepped for the intriguing concepts I present in the Academy.  I highly recommend it.

Chandoo has just opened registration for his new VBA Class – which is a 12-week, comprehensive VBA course.  If you know your way around regular Excel and have always wanted to learn how to devise your own VBA and not just copy a snippet you found somewhere, than this is the course for you!
At the bare minimum, please click on this image and watch a video of Chandoo (who is a Microsoft Excel MVP) describe the course in detail:
Chandoo’s courses always offer extraordinary value for money and this course is no different. But you can also choose to bundle it with his flagship Excel School and Dashboard programs. Extreme value indeed!
There is no doubt that enrolling in Chandoo’s VBA Classes will prepare you well for my upcoming VBA course or the regular Excel Hero Academy course, so please keep that in mind.
If you are reading this in email or RSS and connot see the above form, please click here.

Excel Hero Academy is Closing Registration for 2011

Sample Video from EHA 2 is Below
As of right now, over 250 students have enrolled in Excel Hero Academy 2.
But registration is closing very soon, August 15.
If you have been wanting to do this course, now is the time to enroll.
I’ve been answering a lot of questions about the course lately, so I thought it would be good to post some of that information here.
How many hours a day is required?
You should expect to put in between 2 and 4 hours each week to study the course materials and do your homework.

What’s the schedule?  Do I need specific hours each day?

The 12 modules are released on a schedule.  It happens each Tuesday.  Right now modules 1 and 2 are already posted in the academy.  Module 3 comes next Tuesday.  After every three modules, we have a “review week” that is dedicated to reinforcing the concepts from the previous three modules.  We learned in the first class that this is absolutely needed, as there is an incredible amount of information and much of it is unfamiliar to most students.

Once a module is posted, it stays available for the duration of the course.  On the regular admission level, this means six months from August 15.  On the Extended Enrollment, this means one year.

All of the study is done on your own, on your own schedule, and at your own pace.  Having a set time to meet with the students is virtually impossible since students come from many different time zones around the world.  I spend a lot of time interacting with students on the campus forums.  And the students do an incredible amount of inteteracting amongst themselves.  

Additionally we have four Excel Hero Academy MVPs that assist in moderating the forums and answering questions.  Each of the MVPs are alumni of this course and are extraordinarily helpful and knowledgeable. Their collective Excel knowledge is staggering.

You may know these EHA MVPs already from their huge contributions around the blogosphere.  They are Hui, Robert Mundigl, Jeff Weir, and sam.

Do I need plugins to watch the videos?
I am happy to announce that I have just now expanded the video options to include the iPad!  You do not need any sort of plugin for the iPad, as the videos are in Apple’s native QuickTime format.  This is really a great new feature if you have an iPad.  The video streams full screen and it is crystal clear!

For normal computers, you need Flash installed, but virtually every computer has this.

Can I see a sample video?  I want to make sure it will work for me!

This is a great idea.  I have chosen one video from the dozens in the course.  This video is a very basic VBA Primer, but it gives you some great information, but more importantly allows you to see the quality of the video.  All of the videos in the course are very much HD, at a resolution of 1024 x 768.  They are razor sharp and incorporate no zooming or panning.  The zoom level is set at 100% for the entire presentation.

Notice the easy way that I introduce subjects.  Many students have complimented my teaching style.  Please judge for yourself.

If this video works for you, then all of the videos in the course will work.  They are all the same size with the same encoding.

Here are the links to the EHA sample video:

Can I download the videos?

Yes.  If you choose the Extended Enrollment Option, you c
an download the videos for personal viewing, after the 30-day Guarantee period passes.  The Extended Enrollment also includes three additional weekly modules.
When is the deadline for enrolling?

Registration for the only class of the Excel Hero Academy in 2011 closes at midnight on August 15.
Please enroll now!

Excel Hero Logo Contest!

It’s been 19 months since I started the Excel Hero blog.

Excel Hero has grown into quite a number of operations and groups since then. I feel it is time to get a professional brand going. 

So I’m holding a logo design contest at 99designs.

So far there have been over a hundred entries, most of which I did not care for. But some hold some promise even though “The One” is not there yet. But the contest is an interactive process, so I will be guiding the designers.

Please help me by providing your comments here. You can refer to the entries by number.

And hey, if you have the talent, enter a design. The contest pays $695.00!

I have been very descriptive in the Contest Brief.

Leave your feedback here or at the Excel Hero LinkedIn Group.

Excel Hero Academy Is Reopening!

I am very happy to announce that the Excel Hero Academy will be starting the 2nd group of students on August 1, 2011.
I know many of you have been waiting the better part of a year for this.  The wait is almost over! 
I am pleased to announce that the firs 100 students to enroll will be eligible for the Early Bird Special discount of $50.  The actual buy buttons won’t be in place on the enrollment page until first thing, August 1, Pacific Standard Time.
To claim the Early Bird Discount when enrolling, please enter the code: HEROBIRD.
If you are not on the EHA Interest List, what are you waiting for?

Note: If you cannot see this clock, click here.

Excel Narrated Animated Business Chart

Sometime ago, I stumbled onto an interesting narrated chart at the NY Times that told the story of Oil’s price and consumption.
I thought it would be an interesting challenge to recreate this chart in Excel.  Here’s the result…
Click on the above image to see and hear a Flash video of this Excel 2010 chart in action!
I think this narrated and animated chart concept is very compelling for business presentations. I’m sure I would not have understood the story the chart tells, just by looking at a static version.
I love the fading images and how effectively they stir memories.  They really add to the chart story.
There’s a lot of interesting bits happening behind the scenes with this one.  Jad’s narration is a recorded WAV file from the NY Times original.  I’m using DirectShow from VBA to play and pause the WAV file.  It’s important for the WAV file and the Excel workbook to be in the same folder, so please unzip them together before opening the workbook.
The animation plays at a different speed on every computer, so the big challenge with this project was to devise a way to keep the WAV file, which plays at a constant rate in sync with the animation, on every computer.  I’d be interested in your feedback on how well the solution I settled on (synchronization points) works.
Tear this thing apart and figure out how it works.  There are a lot of valuable techniques showcased with this chart project.
Here is the zip archive:
If learning advanced Excel interests you, please stay tuned.  I will be making a major announcement on Monday regarding the next session of the Excel Hero Academy!
Add yourself to the Excel Hero Academy interest list.  Just fill in the form on this page.
Here’s a list of other animated charts on Excel Hero:
 – solarsong
 – Excel, A Presentation Platform (Number Spiral)
 – Lilac Chaser (Optical Illusion)
 – Stereokinetic (Optical Illusion)
 – Illusory Contours (Optical Illusion)
 – Breathing Square (Optical Illusion)
 – Enigma (Optical Illusion)
 – Two Sinusoids (Optical Illusion)
 – Perpetual Collisions (Optical Illusion)
 – Freezing Rotation (Optical Illusion)
 – Reverse Spoke Illusion (Optical Illusion)
 – Stepping Feet Radial Illusion (Optical Illusion)
 – Swimming Fish (Optical Illusion)

– Mutually Interfering Shapes (Optical Illusion)


Enhanced by Zemanta

Excel MVP

On July 1, 2011, I woke to a very pleasant surprise.  
In my inbox was a message from Microsoft congratulating me on the fact that they had just awarded me an Excel MVP for 2011!
I am not exactly sure what this means yet, but it is exciting.
I just wanted to say thank you to Microsoft and to the anonymous nominators!
The physical award is being shipped currently.  When it arrives I’ll publish a photo here…