Jorge Camoes’ Charts

Too often Excel is overlooked as a valid Business Information tool. Too often organization turn to expensive, proprietary BI solutions-that-become-problems after many months of expensive development. Too often the hidden agenda of some clueless IT dismiss Excel because of some stupid reasons. Excel is a powerful tool that can handle probably 80% of the business information needs. Give it a chance and you’ll not regret.

Because of its flexibility, you can virtually design a dashboard in Excel exactly the way you imagined. Once you know what will the dashboard be used for and what kind of metrics users expect, there are some major areas that you must address, like data management and report design. Let’s discuss them.

Data management

Excel is a very flexible tool, but to create a dashboard you can’t just drop some data and create a few charts. The dashboard must be maintained and updated and if you want to minimize the cost associated with that tasks you must impose some structure to your data.

Usually the data will (should) not be entered directly into the spreadsheet. This means that you must copy /past the data from the source (not wise, really) or link the spreadsheet to the data source. There is a standard way to communicate with databases called ODBC. You can link your dashboard to a table in Access or to a central database, for example. Once you have that link every time the data changes at the source it also changes in the spreadsheet, after refreshing.

Keep in mind that a good practice is to minimize the amount of data you bring into your spreadsheet. If you don’t need it, don’t use it, and if there are calculations to be performed, try to do them at the source.

Data can be imported using two basic structures: a flat file and a pivot table. As a rule of thumb, I would say that a pivot table tends to create larger files but the calculations will be faster, while the flat file will be smaller but, if you need complex formulas to select the data, performance will suffer (you must tested this in your own project).

Lookup formulas

If you use pivot tables you can extract the data using the GETPIVOTTABLE function. If you use a flat file, there is a vast array of formulas you can use, like database formulas (DSUM, DGET), lookup (VLOOKUP, OFFSET, MATCH) or math formulas (SUM, SUMIF, SUMPRODUCT).

A large number of formulas can be dangerous for the integrity of your dashboard. If you use pivot tables you’ll need only a fraction of them. That’s one of the reasons that make me usually choose pivot tables.

Do I have to tell you that you should document your work? Ok, I’ll tell you: yes, you should. But you can simplify that boring task by using named ranges. I am sure this is a familiar example: “=Sales-Expenses” is better than “=$G$4-$H$5″. Named ranges not only help you make your formulas cleaner and more understandable but they play a fundamental role in data management for Excel dashboards. Example: let’s say that the next update adds a new column to your table. If you don’t use named ranges you’ll have to change every single formula and add that extra column. If you use named ranges, that ranges can be defined in a way that automatically uses the new data. Less work, more safety.

Recorded macros and VBA

If you find yourself spending your precious time in some basic and repetitive tasks you should consider studying recorded macros and some simple VBA procedures (or ask someone to do them for you…). You don’t need programming skills to use macros. A typical dashboard will not need sophisticated programming, and some macros will really save you a lot of time.

The image above belongs to my “Demographic Dashboard” (see details below). There are three versions of the dashboard at the moment, and each is defined by how the data is managed. The VBA version uses pivot tables and VBA, the VBA-free version uses pivot tables and lookup functions and the third uses lookup functions, database functions and flat tables. This shows how flexible Excel can be, but the VBA version is much faster.

You may want to read the discussion around the use of VBA in dashboards as well as my 10 tips to improve your Excel dashboard. Recommended reading are John Walkenbach’ Excel Bible (2003 or 2007) and Excel Charts (2003 or 2007); Tomothy Zapawa’ Excel Advanced Report Development (2003 or 2007); Michael Alexander’s Excel 2007 Dashboards and Reports for Dummies and Jon Peltier’s blog.

Chart and report design

Excel chart defaults are ugly and you should avoid them like the plague (make sure you select the acceptable formats). But you should also avoid the “professional-looking charts” of marketing-oriented products like Dundas or Crystal Xcelsius. I tried (and failed) to replicate the Demographic Dashboard using Xcelsius and detailed the process in a series of posts (1, 2, 3, 4, 5).

Throughout this blog you’ll find many charting tips, so I’ll not elaborate much on this. The basic premise is this: a chart is used to discover patterns in the data, and you should do your best to reveal those patterns. This means that you should remove everything that has no added value (3D effects, backgrounds, etc), create a hierarchy of focus and context data and add at least a basic form of interaction (let users select the series, for example).The reference in dashboard design if of course Stepen Few’s book, Information Dashboard Design.

Dashboard screencast tutorial: the Demographic Dashboard

If you are interested in Excel as a dashboard reporting tool you may be interested in my Demographic Dashboard series. The Demographic Dashboard uses a population data set (from the US Census Bureau) to display the evolution of age structure in every country in the world. You can see how it works in this screencast. The three current versions use different sets of techniques to achieve basically the same result, a working Excel dashboard.

The Excel files are free. You just have to subscribe to the blog (use the form on the right sidebar) and I’ll send you the download links. The VBA-free version of the dashboard is discussed here and the post for the Lookup version is this one.

If you need some help to understand how the dashboards were designed consider getting the “How-To Edition”. This is a series of screencast tutorials that shows you step-by-step how the dashboards were designed. The tutorial for the VBA version is available and it will show you:

• how to link your Excel file to an Access database;
• how to create pivot tables;
• how to use advanced Excel functions;
• how to create the charts;
• how to put everything together in a working dashboard.

There are eight screencasts in the VBA version, totaling more than two hours. You can sample them here (this one shows how to bring the data into Excel by linking a pivot table to an external source). I’m sure there are plenty of ideas that you can use in your own projects. It really is an Excel crash course, and I have been getting great feedback, like these messages from Daniele and Jeff:

“In less than half an hour, I have learned more than I ever imagined with your dashboard, and that is the start. Thanks you so much, and yes, the 14.95 Euro are really nothing compared to what you get.” (Daniele)

“Great job. It has helped me get a dashboard project off the ground in record time. Thanks!” (Jeff)

But of course you should judge for yourself.

Each tutorial costs only $24.95, but if you buy the VBA version before the other two become available (it will be soon) you’ll get them free of charge. I offer you a no questions asked, 30 day full money back guarantee. If you want to buy the How-To Edition now, just click here. You can find more info, including a detailed guarantee and my privacy policy in the product page. You you have any questions feel free to e-mail me (charts at jorgecamoes.com).

________________________

Bookmark this page for future reference.

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