Performance vs flexibility in Excel: Demographic Dashboard Lookup Edition

I am sure Albert Einstein was thinking of Excel dashboards when he said “everything should be made as simple as possible, but not simpler”. Let me tell you why.

Demographic Dashboard: The VBA edition

I published some time ago a first version of my Demographic Dashboard to show how an average Excel user could design a dashboard. This version is relatively complex, because it uses pivot tables and some recorded macros to manage the data.

Demographic Dashboard: The VBA-free edition

Then, partially because of a discussion around the use of VBA, I launched the VBA-free edition. It still uses pivot tables but I managed to remove all the VBA. Although I believe that VBA is a powerful tool that should be used when needed, many users are not comfortable with a programming language (not even in the simple form of recorded macros), so this version was designed to address their concerns.

Demographic Dashboard: The Lookup edition

Prior versions use a pivot table connected to an external data source, but I believe that a large majority of Excel users are not aware of pivot tables and how powerful and helpful they can be. Wouldn’t it be nice to just copy/paste into the Excel sheet a simple flat file and design the dashboard around it? Isn’t that standard procedure for these users?

So, by popular demand (sort of) I decided to get rid of the pivot table and design a new version. No VBA, no pivot tables. Just a simple data table and some lookup functions.

Let me tell you this: If you want to follow this path, don’t. It can be really complicated. And creating or updating a data set in Excel by copy/pasting the data is unreliable, dangerous and can seriously compromise your dashboard. But if you need that adrenaline boost go ahead, try it!

Before that, let me share with you some interesting stats. Since I have three versions of a very similar dashboard it make sense to compare performances, so I implemented a simple metric: how long does each dashboard take to cycle through all the 55 time periods? Here are the results in my new computer:

• VBA version: 15 seconds;
• VBA-free version: 45 seconds;
• Lookup version: 2:15 minutes.

This is not exactly unexpected. If you need a (very) long formula to calculate something that you can easily get using a simple GETPIVOTABLE formula performance will drop sharply. On the other hand, the file size of the Lookup version is half the size of versions using pivot tables. If you are planning to email it this could be an option.

Get the Demographic Dashboard Lookup

The VBA-Free and the Lookup Editions of the Demographic Dashboard are bundled with the Demographic Dashboard Tutorial. If you want to create powerful Excel dashboards I am sure this tutorial can help you.