Jorge Camoes’ Charts

Do you need VBA to create an Excel dashboard? Our recent discussion clearly shows two lines of thought: a) you should use it because there are things you just can’t do without VBA and if you have access to a powerful tool you should put it to work; b) you should avoid VBA like the plague because the average user don’t understand it and it can cause a serious problem if the programmer leaves the organization.

In an informal survey among friends and colleagues (all of them Excel users), I’ve discovered that 55% doesn’t know what VBA is, 40% knows but doesn’t want to use it, 4% uses recorded macros from time to time and 1% actually edits the recorded macros to add some sort of functionality (well, this happens to be me…).

The real world always depresses me…

The poll on the right seems to tell a better story, but you guys are le crème de la crème, so I can’t use you as a representative sample, I’m sorry…

The first version of my Demographic Dashboard uses some macros to synchronize pivot tables and to add some functionality to the user interface. Simple stuff, really. But there is a divide, and I had to know if I could create a VBA-free Demographic Dashboard.

That’s a the simple story behind the VBA-free Demographic Dashboard. This dashboard uses the same data set (population by sex, age and country for the period 1996-2050) and the final result is similar (that was the idea). But how can you achieve the same results without VBA? Just simplify, simplify, simplify. Let me give you some examples.

Dashboard objects

Some times we use VBA out of laziness. Take a look at this object: I could place the background image on the sheet and use a few lines of code to correctly place the small triangle. That was my first idea. But why? Why don’t you just use a chart? It is simpler, safer and doesn’t require VBA. The final solution uses a simple scatter plot. Yes, I know, it doesn’t look like one. It’s amazing the things you can do with scatter plots.

I already published a screencast on how to create a population pyramid, but this technique is missing. I wanted to recreate a popular chart format in magazines: lines to encode the current data and areas for the reference data.

I was unable to come up with an elegant solution using the standard available formats in Excel, so I had to improvise. This is a regular area chart, but I use the camera tool to rotate the image to achieve this effect.

Please note that you shouldn’t use this technique if you are planning to print your dashboard because of a well known bug in Excel (but there is also a workaround).

In the previous version of the dashboard I used lines in a scatter plot to display current and reference data. No need for the camera tool.

I really like this chart. It is amazing how it shows the aging process that currently affects every country in the world, specially if you can animate it to see how the passage of time changes the dependencies. As an European I find it really scary…

In this version, the active country is shown in the context of the active region and, unlike the previous version, it also shows the remaining countries, so the user can see the region in context.

I usually delete grid lines, but here they are almost invisible and they actually seem to help. Sometimes I forget that scatter plots are square by definition, but not in this case…

I don’t have a single post discussing sparklines, partially because I like to stick to things that everyone can do with a standard Excel installation (and you’ll usually need an add-in to create sparklines) and because there is so much to say about them that I wanted to write a complete series, and I don’t seem to find the time to do that. But you should definitively consider using them when planning a dashboard.

What you see on the left are not exactly sparklines, just a small line chart where each line is aligned with the country name. I kind of like these lines jumping out of their “natural borders” (the row limits and the table itself). Download the dashboard and compare these lines with the top ten countries in Europe, for example.

Lately I’ve been playing a little with links to external sources and I decided to add these two. The first one opens a Google Map with the active country and the second one opens the CIA Factbook for the same country. You don’t need to hard-code the links, it is just a string that is automatically changed whenever you change your data (in this case, the name of the country). You can also put the data into your worksheet, like I did when I created my Excel thematic map.

There is something that you can’t do without VBA. When you select a region, the list of countries automatically reflects that change, but the current country is not changed. A simple macro can easily select a new value when a different region is selected. So, you may be comparing Angola with countries in Europe (Angola is in Africa). Since I can’t change the default country, I added a conditional formatting for that cell, and when the country is not found in that region the background changes to warn the user.

Availability

The dashboard is available to all subscribers, so just just fill the form on the right sidebar and you’ll get links to the various dashboard versions, including this one. It is an unprotected file, so you can see what was done.

There are several approaches to the design of an Excel dashboard, and I plan to publish a few more. This is my road map for the coming weeks/months:

• This version is released unprotected and zipped together with the VBA version; it will also be added to the How-To edition;
• A pivot table linked to an Access database is a strange concept to many users, so I’ll have a VBA-free, pivot table-free version, using lookups to get the data from an Excel database; this is scheduled to be published by the end of April;
• I’d like to have a version for Open Office to close this cycle.

I believe that the value I’m providing is not correctly reflected in the price tag of the How-To Edition ($24.95), so I’ll change it as soon as the screencast tutorial for this version becomes available (this means that if you think that these dashboards and tutorials can help you you should consider getting the How-To version now…).

If you're new here, you may want to subscribe to my RSS feed or subscribe via e-mail. Thanks for visiting!