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.
I think you are correct about VBA instead of formulas and lookups, but I will disagree with you about using pivot tables. I have found errors in my pivot tables–especially when I have played with them in VBA.
My development team wrote their own code that gives me pivot table functionality but it is completely transparent to me and I can catch it when it has a bug. It may be a bit slower than the magic of pivot tables (in Excel 97 and 2000), but I was always willing to make the tradeoff.
Jorge has a good point in that Pivot Tables are create the need to use VBA. However I work in an environment similar to Charles Kyd’s description using VBA takes a huge toll in the sustainability.
For me there is an additional cost: VBA makes it impossible TODAY to leverage any excel-to-web offerings, in our outside your organization firewall (Editgrid / GoogleDocs/ MS Excel Services / Jedox Spredasheet Services).
Perhaps I coincide in the path taken recently by Jorge in that I’ve implemented Pivot Table functionality across multiple tables without them nor VBA; this is achieved by a combination of functions. Basically:
1) database / webqueries which have the nice option of refresh upon open.
2) nameranges that mapped to the query output
3) Index, match and rank
Again, thanks and credit goes to Charles Kyd for providing detailed examples to understand this hidden Excel Gems.
Regards,
Jose
@Chris: we don’t disagree: the moment I start finding errors in pivot tables I’ll stop using them.
@ Jose: The key is always Occam’s razor: the simplest solution is the best. If you can do it without VBA do it. If the combination of functions becomes unmanageable, use VBA instead or find a better solution.
If there is a time and resource efficient effective system to train all those within a company in Excel on a regular, continual basis then Charley Kyd’s justification for avoiding VBA and advanced Excel becomes irrelevant. A culture of self empowerment and education needs to be fostered to avoid reliance and dependance on the individuals that currently hold the knowledge.
I have been using XML and xml mappings to bring in data to my Excel dashboard. The source database exports to xml and “live data” can be updated when the user refreshes. The data is in tables, then can be formatted and manipulated further. The flexibility is excellent.
I have learned a lot from your site already and look forward to following it!
Thanks!
The problem with using Macros is not the macros, it’s the writer. If it’s well written, includes good error checking and some basic functionality for updating to flow with changes then there is no issue with it. Often when I create a macro for a custom solution I also include a non-macro backup in the file as well for fail safe when/if something stops working.
I have built a dashboard style sales report before without the use of macros and it worked quite fine. I really love macros but not many users go that far… unless you want support services after you leave the company. What I used is a pivot table in a separate file from the report file. I used nested ifs, choose, sumifs and it worked quite well… well I had to use a myriad of other formulas to get it in the format for the report.