Do you need VBA to create an Excel dashboard? Should you take the plunge and go beyond Excel formulas? Well, I think you should, but feel free to disagree. Charley Kyd, commenting on my review of his e-book, disagrees:
As a consultant, I visited too many clients where an Excel user had written some macros, used them for a while, and then left the company. The people who remained treated those macros as magic, until the magic failed.
Therefore, I always try to find solutions that avoid user-written macros… even recorded ones.
Yes, a reality check is always a good starting point, and based on my own experience, I would say he’s right. There seems to be an invisible line that most users don’t dare to cross, here be dragons. I would say that, for an advanced Excel user, adding VBA to her worksheets (at least in the form of recorded macros), is a natural path to take, but perhaps that’s not encouraged and never becomes a priority.
If you are planning an Excel dashboard, your top technical priority should be how to handle the data (how to get it into Excel; how to manage updates; how to calculate metrics, how to prepare it for the dashboard reports). It’s not an easy task, specially if you cripple yourself by not using at least a small amount of recorded code.
Let me give you an example. My Demographic Dashboard uses pivot tables to handle the data. There are several tables, and I use some basic macros to synchronize them (when the user selects a country, a macro selects that country in each table – very basic stuff).
But what happens if you ditch macros altogether? That’s what I am testing in a new project I am currently working on, a VBA-free version of Demographic Dashboard.
When you rely solely on Excel functions you need to bring data into Excel as close to the final format as possible. If not, you’ll have to calculate it and you’ll end up with hundreds or even thousands of formulas. So, unless you have a very fast computer or a very small data set, performance can be a serious issue here.
What do you do? You must aggregate the data before importing it into Excel. But you may end up with several views of the same data, and that raises a new problem, how to synchronize them. Assuming you are not copy/pasting the data into Excel (you aren’t, are you?), you’ll have to setup some queries in your data source and link them from Excel. Of course you’ll have to manually refresh the tables when data changes.
I believe that using VBA and pivot tables is much safer than relying on look-up formulas. It is cleaner, has a lower maintenance cost and you can have some amenities for the users that you can’t offer without code. For example, if you are designing a marketing dashboard to be shared among several product managers, it would be nice to automatically select the right configuration (product, market, measures) when the user opens the dashboard. But you need the user id to be able to accomplish this, and you need VBA to get it from the system.
There is a compromise solution whereby you store the data in a single, larger pivot table that you do not need to change, just refresh. Then there will be some intermediate calculations to setup the data source for the dashboard report itself. This is what I am testing now.
I don’t have a definitive answer to the question in the post title. When my inner voice says “this is stupid, find a better solution” I usually do it (I’m very obedient). This is the first step to learn new things, and curiosity only kills cats.