Category Archives: Uncategorized

Master Excel PowerPivot Now

Outside of VBA, Excel’s single most powerful feature is PowerPivot, which is an official MS add-in for Excel that takes pivoting not just to the next level, but to a whole other world.

With PowerPivot you can connect to your enterprise databases, analyze data, create advanced reports & prepare dashboards right from familiar interface of Excel.

You should learn as much as you can about PowerPivot if you are looking for ways to enhance your skills in data analysis, pivoting & reporting areas. To do so, you’ll need Excel 2010 or newer.
 
This is a big subject and if you want to get up to speed quickly and learn the secrets of how best to capitalize on PowerPivot my recommendation is to take Chandoo’s new PowerPivot course
 

If you are familiar with Excel and PivotTables and you want to take your career to the next level then it’s time to learn PowerPivot now

Back By Popular Demand – The Awesome Excel Dashboards Course!

Mynda’s Excel Dashboard Course is open once again and right now she is offering a 20% Discount Code:  DASH

That code is valid until the end of the day on January 30.


You may recall that last summer Mynda offered this course for the first time and it was an absolutely smashing success. Mynda Treacy has put together a truly fantastic Excel Dashboard Course
Dashboards are the new buzzword when it comes to Excel reporting and employers are specifically asking for these skills.
If you need to take your Excel skills to the next level to get that dream job, then knowing Excel Dashboards will help you stand out from the competition. 
Dashboarding is an incredibly valuable tool in today’s market for consultants, analysts, and managers.  But Excel surely does not make it straightforward to build highly professional and interactive dashboards.  That’s why this type of training is crucial.
The course is video based, delivered online and is available 24/7. You also receive comprehensive workbooks and sample dashboards to keep. There’s even an option to download the videos.
The previous classes have been a huge hit with many saying that they love the cool techniques and that they’ve been able to impress their colleagues by using them in all sorts of reports, not just dashboards.
Click here to find out details of the course, read comments from past students, and watch the ‘behind the scenes’ video that shows you what you’ll receive as a student.
BONUS
If you sign up by the 30th of January you can get it for 20% off.
I am really happy that Mynda and her crew have put this Excel Dashboard Course together. 
There are many techniques in this course that will surprise you, and you will immediately be more productive.
Do yourself a favor and read this page.  The course is awesome.  The price is incredibly fair.  And for the next six days, you can enroll with a 20% discount.
Consider this: although I get approached by countless people to promote their Excel related products, I rarely do.  I must consider it awesome first and foremost, and it must be extraordinarily valuable to you.  The only Excel training products I have ever recommended are my own course (EHA), Chandoo’s courses, and now Mynda’s Dashboard Course.  Think about that.
If you are reading this in email or RSS and connot see the above form, please click here.

Regional Excel Differences – Leave Your Mark

My friends in Europe (Italy and Hungary) are on a mission to collect some frequently used regional settings from different languages and versions of Excel. 
I would like to support their effort by drawing your attention to this project. Excel comes in many different language versions.  I only use the English version of Excel, but I have seen first hand how trivial differences between the versions can cause differences in how formulas work. 
Complicating matters, we can change the regional settings of Windows – which adds another layer of nuanced differences. For example a common problem are the different separator characters – these are dependent on both the regional settings and the language and version of Excel too.
In most cases the average Excel user does not realize what is happening inside Excel, but as Excel developers we must keep in mind the challenges presented by these differences – especially when we are not 100% sure what local settings will be used by our end users.
I have already sent the English settings of my Excel 2010 and 2013. Now it is your turn – if you use a local-language version of Excel, please take a look at this post, where you can find how you can contribute to this important project – it takes just 3 minutes!
I am really interested to see the final collection and read the experiences collected!
Thank you!

Advanced Excel Training!

The Excel Hero Academy opened registration yesterday for the 4th class ever. And just like that the enrollments came flying in!  Several hundred so far.
The quality of each of the classes in terms of the students in truly amazing and this class, EHA4 is no different.  I know many of you have been waiting for an open enrollment.  This is it.  The next one won’t be until the Spring of 2013 most likely.
EHA is truly an amazing journey.  I break down Excel into concepts that most experts have never considered and by the time I’m done weaving it all back together,  you are left with that feeling of Eureka… and think to yourself, of course that’s how Excel works!
Hundreds of alumni will be retaking the course and are very happy to do be doing so because there is just so much information that sometimes viewing a particular lesson a dozen times reveals new actionable insights each time.
How does it work?
The course is actually a hybrid of work-on-your-own-schedule and a preset schedule.  Allow me to explain…
All of the materials and resources for the course are online.  However, they are not presented as a webcast.  Rather, the videos play on demand, 24/7, when it is convenient for you. The modules come out on a weekly schedule.  So during week 1, all of the students work on the materials from Module 1 whenever it is convenient for them, 24/7.  
There is massive interaction in the class forums discussing the concepts and their application.  Homework is assigned for every module and students are encouraged to post their completed assignments (usually workbooks) to the module’s HOMEWORK WALL.  This is an extraordinarily beneficial activity, as everyone learns from everyone else.  
It’s really amazing.
So then on to week 2 and Module 2 is released.  The same activity occurs 24/7 during that week, but this time on the Module 2 lessons and homework.
Week 3 brings Module 3, and the same activities.
Now we take a break for a week so that students can review all that they’ve learned from the first three modules. And trust me, you will need this time as the concepts are very powerful and take time to fully penetrate.  We do some challenges during this time as well.
This pattern of three modules followed by a review week is followed throughout the duration of the rest of the course.
So we are on a schedule to do three modules every four weeks, but you decide when you will study throughout the process.
Are you ready to discover the next level of Excel?
The value proposition of EHA is off the charts.  You will learn in 16 short weeks and for a few hundred dollars what it took me decades to learn.  The value of the course is that it dramatically shortens your learning curve.  Master Excel on one side, and command VBA on the other.  With EHA you will become an Excel Hero.
And for the first time Chandoo and I are teaming up to offer our courses together, at a discount.  Imagine that the EHA Super Bundle has my entire course as well as Chandoo’s Excel School & Dashboards Course. That’s more than 50 hours of Excel and VBA training, and more than 60 example workbooks and tools.
Master Excel Now by enrolling in EHA4.
Testimonials
If you’ve spent any time on this blog, you know the types of potent Excel solutions I share.  Well, it took me two years to fully develop EHA. I’m telling you… you should take the course.  It’s that simple.
Here’s what students have to say:
  • “Jaw-dropping… For the advanced Excel user, Daniel’s Blog and the Excel Hero Academy are
    treasure chests full to the brim with invaluable Excel information, techniques and ideas.” – Robert Mundigl

  • “Daniel’s expertise in Excel is unrivaled in my opinion… Daniel’s Excel training has literally changed the way I view Excel and use it for business purposes.” – Vedantham Srinivas

  • “I was especially impressed with the Excel-Access set of tools that enables Excel to used to retrieve data from and update an Access database.” – Dennis Plum

  • “I challenge ‘anyone’ to put together a better course than EHA for a comparable cost. I’ll go out on a limb here say, it aint gonna happen.” – Calvin LeBlanc

  • “I’ve never seen anything like Daniel’s course. Nothing is remotely like it. Daniel’s course has paid for itself time and time again.” – Michael Shallal

  • “History will show that Daniel Ferry has built a fabulous body of work.” – Brendan Drew

  • “There is nothing like Daniel’s course out there. The content is like discovering gold.” – John Hackwood

  • “This is an empowerment system with so many extras. Thank you, thank you for a fabulous Excel journey and experience.” – Danièle Questiaux

  • “I am very very happy with the course, best I have ever invested in. I am glad I ordered the download option as I believe I will keep learning from your modules for a long time to come.” – Ajay Gajree

  • “The content and videos in Daniel’s class are the best I have seen.” – Wanda Norrick

  • “Forget all the businessy stuff. Daniel is to Excel as Neo is to The Matrix. By the end of the course, you’ll be ‘walking the path’ with your new powers of Excel-fu. Learn from The One!” – Mark Sessoms

Master Excel Now by enrolling in EHA4.
But hurry.  The next class is next year.
.

Awesome Excel Dashboard Course

The next class of the Excel Hero Academy will open in about a month (mid September, 2012)!
I am thrilled that there are already several hundred students on the waiting list, and many of them have been waiting since February.
In the meantime I have recently learned that Mynda Treacy has put together a truly fantastic Excel Dashboard Course. She was kind enough to allow me to review several of the videos, and I can report that her My Online Training Hub has put together a truly first-rate course.
Dashboards are the new buzzword when it comes to Excel reporting and employers are specifically asking for these skills.
If you need to take your Excel skills to the next level to get that dream job, then knowing Excel Dashboards will help you stand out from the competition. 
Dashboarding is an incredibly valuable tool in today’s market for consultants, analysts, and managers.  But Excel surely does not make it straightforward to build highly professional and interactive dashboards.  That’s why this type of training is crucial.
The course is video based, delivered online and is available 24/7. You also receive comprehensive workbooks and sample dashboards to keep. There’s even an option to download the videos.
The previous classes have been a huge hit with many saying that they love the cool techniques and that they’ve been able to impress their colleagues by using them in all sorts of reports, not just dashboards.
Click here to find out details of the course, read comments from past students, and watch the ‘behind the scenes’ video that shows you what you’ll receive as a student.
BONUS
If you sign up by the 7th of August you can get it for 20% off, and if you do, I will be happy to offer you a special $150 discount on Excel Hero Academy 4 next month. Just email me your receipt!
I am really happy that Mynda and her crew have put this Excel Dashboard Course together. 
There are many techniques in this course that will surprise you, and you will immediately be more productive.
Do yourself a favor and read this page.  The course is awesome.  The price is incredibly fair.  And for the next six days, you can enroll with a 20% discount, and save $150 off of EHA4, next month.
Consider this: although I get approached by countless people to promote their Excel related products, I rarely do.  I must consider it awesome first and foremost, and it must be extraordinarily valuable to you.  The only Excel training products I have ever recommended are my own course (EHA), Chandoo’s courses, and now Mynda’s Dashboard Course.  Think about that.
If you are reading this in email or RSS and connot see the above form, please click here.

Excel Formulas Can Make a Graph

If you have followed my work for any period of time, you know that I’m a big advocate of using named formulas to do the heavy lifting in my models and my dynamic charts.
Today I’d like to share with you a very fine example of this by the Frankens Team.
I love this chart, but whether you love it or hate it, the techniques behind how it works are fascinating!
A big thanks to the Frankens Team for writing this article and sharing it with the Excel Hero Community!

Enchanted Radar Chart

Infographic with Excel



picture 1




picture 2


Can Excel be a creative tool to create advanced, infographic-styled charts? Our answer is YES!

We created some interesting charts combining different chart types and using the built-in features in a bit of an unusual way.

In this article we would like to show how to create the chart you can see above, and how you  can make it dynamic, based on the cell selected by the user.

The data we use on the chart comes from this infographic:
http://awesome.good.is/transparency/web/1010/political-climate-chart/interactive.html

The numbers are percentages representing the fraction of the people with regard to the particular issue that is the top priority.

We designed a chart a similar to the original one, because the circle format could visualize this kind of data  more effectively than a linear one. All of the 20 measured  issues (categories) are visible on the chart now, but you can add more data to the table. The names in the file are built to be dynamically growing, so the new data will immediately appear on the chart.

To make the comparison easier
we turned the chart inside out and used active-cell based highlighting together with a marker line. 



Chart components

On the chart you will find 4 data series, three of which are filled with the radar type chart, and the fourth one is a doughnut chart with a  label. The dynamic chart title serves to make the chart more “readable”.

It is important to note that nothing is drawn manually.  The only pain is that Excel cannot set the angle of the label added with the help of the doughnut series, so it is adjusted manually.

Filled radar data series:

  • The white columns growing from outside to the centre of the circle represent the data. But the series itself is the green area – it “cuts out” the data columns from the white background of the radar chart.
  • The black outline around a white column highlights the data selected by the active cell (row)
  • The grey dotted circle is a contour-line and it indicates the value of the category to make comparable with the others.


Doughnut data series:

  • The black circle at the outer rim was created as doughnut-type which makes creating the labels easier.


How do we manipulate our data to build up the series?

Primary data display – inside-out columns



picture 3




picture 4


The whole chart is inside-out, so we need the data for series as 1 – <percentage value>. The gridlines may help to find out what is happening – you can see by counting on picture 4 that there are 8 data points for each category value (data column) plus one for the gap between the columns. So the total number of data points = 9 * number of categories.

The array for the green data series should look something like this: (remember, the data here is 1 – <percentage value>)

1

0.25

0.25

0.25

0.25

0.25

0.25

0.25

0.25

1

0.39

0.39

0.39

0.39

0.39

0.39

0.39

0.39

1

0.26

0.26

.

.

.


How can this array been created as a named formula – without using helper cells on the worksheet?


Capturing input data

First we define the parameters of the data series:

Named formula Note Refers to
n_ColPoints Predefined number of data points for one category + 1 point for the gap =9
n_Category Number of categories in the data table. In the example n_Category = 20 =COUNTA( Sheet1!$A$1:$A$100 )
n_TotPoints Total number of data points on the radar chart
In the example n_TotPoints = 180
=n_ColPoints * n_Category


You can change the value of n_ColPoints - lowering it will result in a more triangular-shaped column, increasing will diminish the gap. In the example we use 9, as this gives a good result regarding visibility.

Note: Number of categories are limited to 100 in the dynamic named formulae, however the number of effectively displayable categories is far less.

Create a dynamic range for the input data:

rng_h n_Category could be used in the INDEX formula to make the data table dynamic =Sheet1!$B$2:INDEX( Sheet1!$B$2:$B$100, n_Category)


Building the main data series

And here comes a bit more difficult part…

The structure of the array we would like to have is simple: a gap data followed by the 1st category data repeated 8 times, gap again, 2nd category data repeated 8 times, gap, etc.

How can we imagine it?


Step 1 – Repeated values for the chart columns

First of all, let’s see that if we can separate the problem of category data from the problem of gap points! If we build up an array without gaps but having 9 times the category data, it will not be a big deal to change the 1st, 10th, 19th, 18th, 37th,…,172nd data to the gap-value.
So let’s start with this array as intermediate result – every value listed 9 times, and there is 20 category values, so we need an array of 180 elements:


0.75

0.75

0.75

0.75

0.75

0.75

0.75

0.75

0.75

0.61

0.61

0.61

0.61

0.61

0.61

0.61

0.61

0.61

0.74

0.74

0.74

.

.

.


And what do we have as input?

The name rng_h contains the input data as a vector of 20 category values:

0,75

0,61

0,74

.

.

.


So we want to have a one-dimensional vector of 180 elements using another one-dimensional vector with 20 elements and we need to repeat each element nine times. A very powerful function of Excel could be used in this case: MMULT ! What is the array we should multiply by our rng_h to have the above mentioned result? If you are familiar with the rules of matrix multiplication, you will easily find the answer:





Step 2 – Build a helper matrix for the value selection

The next step is to find a way to build up this 180 x 20 sized matrix of 1/0 values. It is simple if you see the pattern: 1 = TRUE, 0 = FALSE, so this matrix is a 2D representation of which data point belongs to which category in the 180 length array:





Step 3 – Build a helper array for the value addressing

Now we are close! We need the left-hand-side vector from the above picture which tells us which category the data point belongs to. It consists of 180 elements: repeating number 1 nine times, number 2 nine times, going up to 20 repeated nine times. We can create it easily using the INT formula!

Let’s summarize what we have now!


We will need two helping-vectors:

(please note, your row and column separator character could be different, depending on your regional settings.)
arr_pnt row-sequence to number the data points from 1 to n_TotPoints {1;2;3;….;180} =ROW( Sheet1!$A$1: INDEX(Sheet1!$A: $A,n_TotPoints))
arr_cat column-sequence to number the categories from 1 to n_Category {1,2.3,…..,20} =COLUMN( OFFSET(Sheet1!$A$1,,,,n_Category))


Determine the structure of the data points:

arr_pnt_cat this array – as a sequence – identifies which category the data point belongs to. It consists of 180 numbers, repeating 9 times each number from 1 to n_Category: {1;1,1;1;1;1;1;1;1;2;2;2……..;20;20} =INT( (arr_pnt-1) / n_ColPoints )+1

or an other solution:
=MATCH( arr_pnt, arr_gap, 1 )
(see arr_gap below)


So here is the formula for the 1/0 matrix from the first picture, with double negation to convert the TRUE-FALSE to 1/0: –(arr_pnt_cat=arr_cat)

And the matrix multiplication will create us the array without gaps:
MMULT(–(arr_pnt_cat=arr_cat), rng_h)


Step 4 – Inserting gap values

To create the gap values, we need to change the 1st, 2nd…172nd value of this array to 0. The idea is this: if the sequence number of the data point (arr_pnt) is an element of the set of gaps then it should be 0. So we will need the set of gaps:

arr_gap sequence number of data points used to separate the categories (the gap) {1;10,19;28;37;…..;172} =(ROW(rng_h)- MIN(ROW(rng_h)))*n_ColPoints+1


With a MATCH and ISERROR we can have the 0s for the gap points:
ISERROR(MATCH(arr_pnt, arr_gap, 0)

One minus this whole array turns the series inside out :-) So here is the final named formula for the main data series!


arr_01 Data series representing the inside-out columns. Repeats 1 minus data percentage 8 times alternating gap points with value 1. =1-MMULT(–(arr_pnt_cat=arr_cat),rng_h)*
ISERROR(MATCH(arr_pnt,arr_gap,0))


After creating a filled radar chart adding arr_01 to data series you should see something like on picture 3. Please change the maximum of the axis to 1,1.  The minimum should be 0.


Category labels

Now we have a static inside-out chart. The easiest way to add data labels is by using a doughnut chart with number of slices equal to the number of categories. We will need a very simple array as data series for this doughnut consists of 1s for each category. Power of 0 would not be a solution in this case, because Excel interprets 0^0 as #NUM error, so if we have 0 value or missing data in rng_h, the doughnut ring chart will not be in line with the columns – that is the reason why we use ISNUMBER here, and *1 converts the boolean values to numbers.

label_1 ISNUMBER for the data range rng_h gives back TRUE where we have data. You need to multiply by 1 to convert it to numeric values: {1;1,1;…;1} =ISNUMBER(rng_h)*1


Add label_1 to the chart series, change the chart type to Doughnut and set the hole size to maximum.

Create this named range for labels:

labels Dynamic range for labels =OFFSET(rng_h,,-1)


And insert it to the series formula of the ring via the formula bar:
=SERIES( “label_1″,
Enchanted_radar_chart.xlsm!labels,
Enchanted_radar_chart.xlsm!label_1, 4)

Now you can add labels to the chart, you need to change it to show the category names. Unfortunately Excel can not adjust the angle of the labels according to the slices – the labels must be rotated manually.


Active-cell based highlight

Excel’s CELL formula without the second parameter makes it possible to determine the active cell. (You can read more about it here.)


active_row
The formula gives back the row number within the data table. =CELL(“row”)- CELL(“row”,rng_h)+1


Now we build up the black outline highlighting the data of the active category. We do not take care of the rest of the categories, so all these data must be zero. We use only the part of the data point-category array (arr_pnt_cat) where the numbers are equal to the number of the active category:

arr_act_cat This array contains 0s for all the categories except the active row-category (9 times 1). =(arr_pnt_cat=active_row) * arr_pnt_cat


With exactly the same logic used to build up the main data series we can create the data series for the black outline:

arr_a1 Comparing to arr_01 the only difference is in the 1-0 matrix: here we use the active-category based arr_ac
t_cat instead of arr_pnt_cat which contains all of the indexes.
=1-MMULT(–(arr_act_cat=arr_cat),rng_h)* ISERROR(MATCH(arr_pnt,arr_gap,0))


Add the arr_a1 series to the chart. It should be a filled radar chart. Set the marker fill colour to none but set a line at least 2,5 pt wide with black colour.


Active-cell based circle

This circle represents the value of the active category and makes the comparison easier . The only thing to do is to set up a series that contains this value for all of the 180 data points.
arr_a2 Choose the active data from the data table using INDEX and create a vector of this data by multiplying with a 1-vector. If the active row is not in the data table, we do not need this circle, this is why multiplied with is_in_rng =(1 – arr_pnt ^ 0* INDEX(rng_h,active_row))* is_in_rng


Where is_in_rng is:

is_in_rng We have chosen this nice formula to determine if the active row is within the data range or not. If the active row is below the table, MEDIAN gives back the last row of the table, so the equation will be false. Similarly when the active row is above the table MEDIAN gives the
first row of rng_h (which is CELL(“row”,rng_h) so the equation will be false too.
=CELL(“row”)=
MEDIAN( CELL(“row”),
CELL(“row”,rng_h),
MAX(ROW(rng_h)))


Add the series arr_a2 to the chart. It should be filled radar. Set the marker fill colour to none but set a line at least 2,5 pt wide with black colour and dotted style.

Because cell selection does not trigger calculation, you need to add a one-liner code to the worksheet object. In the VB Editor click on the sheet name under your project, and copy this code to the code window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Target.Parent.Calculate

End Sub

It tells Excel to calculate the worksheet for each and every selection change.



Dynamic Title


Only one thing left: to make the chart title dynamic showing the name and value from the active row.

TT This name will be added to the chart title. The formula itself is a concatenation on the active data, the name of active category and some line breaks CHAR(10). =IF(is_in_rng,
TEXT(OFFSET(OFFSET(Sheet1!$A$1,active_row,),,1),”0%”)
& CHAR(10) & “of Democrats viewed” & CHAR(10) & LOWER(OFFSET(Sheet1!$A$1,active_row,))
& CHAR(10) & “as a top priority” & CHAR(10)&”in 2011″,””)


On the formula bar assign the name TT to the chart title box.


Download

You can download the example file described in the article:

Enchanted_radar_chart.xlsm

And you can find some more charts with this technique here:
https://sites.google.com/site/e90e50fx/home/infographic-chart-with-excel

by Frankens Team
With special thanks to Suzanne Laing for the language corrections!



To learn MUCH more about Excel formulas, please go here:


http://www.excelhero.com/blog/2011/05/excel-formulas.html 

If you like the Excel formulas information and other powerful techniques shared here on the Excel Hero blog, please sign up for our newsletter:












Time to Learn!

It is with great pride that I announce that the third class of the Excel Hero Academy is now open for registration!
photo4.jpg
We are definitely growing.  I’ve hired my first employee!
If you have been waiting for an opportunity to truly understand how Excel works, the time is now.
But hurry because registration will only stay open for two weeks and the next class will not be until summertime.
But whether you decide to enroll or not, please let me know what you think of our brand new Enrollment Page!
.

Excel VBA Class 2012

The next class of the Excel Hero Academy will open on February 1, 2012!
There’s already a couple of hundred students on the waiting list, and many of them have been waiting since last summer.
While EHA is not a VBA class per se, Visual Basic for Applications is a significant component of the class because of the sheer power it brings to the table.  VBA is the mechanism that Excel uses to record macros, but it is so much more than this.  It’s a fully fledged programming 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.
Think about this.  Even if you cannot distribute workbooks in your organization that contain macros, you can still use VBA to craft incredibly awesome workbooks that contain no VBA themselves.  The resulting workbooks may then be shared with your colleagues.
If you want to weild Excel to do your bidding in your workplace, VBA is a must.
The way that I teach VBA is very intuitive, but I have you jump straight in to the deep end.  For some, it can be helpful to have some foundation first.
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 21-hour, 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 also a Microsoft Excel MVP) describe the course in detail:
vba-classes-msg1-chandoo.png
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 the 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 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.
excelhero_universal_calendar.png
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: