Excel dashboards according to Charley "ExcelUser" Kyd

If you google for “Excel dashboards”, 6 out of the first 10 results link to Charley Kyd’s ExcelUser website or some of his affiliates. MrExcel calls Charley Kyd “the king of Excel dashboards”.

There is a good reason for that. Three years ago, Charley Kyd published an e-book, Dashboard Reporting with Excel, probably one of the first books discussing charts in the context of dashboard reporting (he recently published an Excel 2007 version). The reader will find sound advice on the use of some Excel techniques, dashboard planning or external database connection. A useful step-by-step tutorial is presented at the end of the e-book.

Kyd helps you to create a static, printer-friendly dashboard…

Charley Kyd dashboard sample According to Kyd, a dashboard should be printed. This is obvious as soon as you look at any of his examples, like the one on the left, and, knowing the level of computer illiteracy at the higher management level, he has a point. For your dashboard project, don’t assume that the users will want to see the dashboard on screen. At least ask them.

Some of Kyd’s principles and techniques are freely available on the website, although you should refer to the e-book for a more detailed discussion. For example, regarding dashboard design, Kyd disapproves the use of gauges and condemns “pablum dashboards” (low data-density dashboards).

When discussing the benefits of dashboard reporting for management, he argues that Excel is often overlooked as a dashboard reporting tool because a) vendors prefer to offer costly proprietary solutions; b) users are not aware of the potential of Excel as a dashboard tool; and c) users overlook Excel’s capabilities to produce high-quality, magazine-like reports.

The dashboard design can be much improved by the use of smaller charts and templates for future reuse, and a magazine-like formatting. To achieve this “magazine-like” quality Kyd uses extensively the camera tool. In fact, he says that:

Without the Camera tool, you would find it virtually impossible to create high-quality dashboard reports.

[page 77 of the e-book]

The camera tool is interesting, but this is an overstatement (there are many good examples that don’t use it, as you can see in the final pages of Stephen Few’s Information Dashboard Design). And of course there is the well known and persistent Excel bug that resizes a chart if you used the camera tool to display it (Kyd alert for that here; possible workarounds can be found here).

I have a different approach to dashboard design (more on that later) but I would recommend the Dashboard Reporting with Excel e-book if you plan to design a printer-oriented dashboard. Perhaps you should explore the website first (check those links above) to see if you/your audience are comfortable with Kyd’s dashboard approach.

A side note: Kyd tells us that “the most common mistake that Excel users make when they create charts is to make their charts too large.” He’s right, but given the higher resolution you can get from a printer, you have the option to drop Excel standard charts altogether and use sparklines instead.

… but there is something that bothers me

I confess there is something that bothers me when I see Kyd’s arguments in favor of Excel dashboards. It is this magazine-quality thing (he emphasizes this throughout the e-book and also on the website). He says in the e-book:

Ideally, benchmark reports should look like they came from a magazine or newspaper. This makes the reports both interesting and easy to read.

As a rule, however, bean counters aren’t qualified to design magazine-quality reports, just as graphic artists aren’t qualified to design cost accounting systems. But bean counters can copy graphic designs they find in magazines and newspapers. [page 17 of the e-book]

I understand what he means, but I believe something is lost in translation, because that’s what the readers already do, and the results are far from stellar. (A creepy idea: if “bean counters can copy graphic designs” then graphic designers can design cost accounting systems. After all, they are designers…)

I don’t even know where to begin… let’s see:

  • When people don’t understand what they are copying they usually try to improve on it. So, that 3D, flying, 389 slices, primary colors, pie chart you saw in the last marketing presentation was probably inspired by a simple 2D, three slices, soft colors pie chart published in a magazine;
  • If you don’t have control over typography (and in Excel you don’t) you can’t expect to get a good replica of a chart made in Adobe’s inDesign;
  • Try to see a management dashboard as a portrait that captures the essence of the organization. You should make it simple, but if you use a magazine as a role model you’ll make it simpler than it should (quoting Einstein, “everything should be made as simple as possible, but not simpler”);
  • If yours is an untrained (design-wise) look, browsing magazines searching for something to copy from is a receipt for disaster. I love graphic design (it’s a platonic thing, I’m afraid), but your typical graphic designer couldn’t care less about good quantitative data visualization;

A different approach

I understand and respect Kyd’s work and I know that there is a market for printer-oriented dashboards. My approach is somewhat different. I believe that, given the amount of data we have to deal with, a minimum level of interaction is needed. For example, if you are monitoring your competition, you should show the user a predefined set of competitor products but you should also let her choose a different set from a larger list. Interaction is a fundamental way to build knowledge. In my Demographic Dashboard I try to show how this is easily implemented in Excel.

One of the problems with Excel dashboards is how you get the data into Excel… Kyd discusses six methods: 1) build-from-scratch spreadsheet reporting; 2) spreadsheet databases; 3) pivot tables and 4-6) OLAP cubes. I will not even bother to look at the first two (if you want an highway to spreadsheet hell, be my guest, use them). The last one (specially what he calls “Excel-friendly OLAP”) is the best option, but probably too advanced for the average Excel user.

This leaves us with pivot tables. They are safer than other basic methods and don’t require much interaction with the IT… According to Kyd, their main disadvantage is:

Excel formulas only can reference data that PivotTables return to spreadsheet cells. This forces Excel users to interact with each PivotTable to update their report data, rather than interacting only with controlling cell values in Excel.

He’s right, but the dashboard designer just needs a macro (he can record it) to change values. Let me exemplify:

PivotTables(“x”).PivotFields(“Year”).CurrentPage = Range(“selYear”).Value

This single line changes the Year field in pivot table “x” to whatever value is found in cell “selYear”. You just need one line for each pivot table. Then the macro is attached to a button et voilá! you have a basic interaction. Not too hard…

It really doesn’t matter if you use printer, on-screen or smoke signal dashboards. What matters are the insights a user gets from the data and how they can support better decisions.

Judging from your experience, do you think there is a pattern that can be used as a rule of thumb (like “higher management prefer printed dashboards, middle management prefer on-screen dashboards”) or, as usual, it depends?

6 thoughts on “Excel dashboards according to Charley "ExcelUser" Kyd

  1. “…but your typical graphic designer couldn’t care less about good quantitative data visualization;”

    What a pronouncement…

  2. One of the strengths of Charley’s e-book is his techniques to make the reports easily updated from month to month as the data changes. I tell people that every minute they spend optimizing their data layout saves an hour of frustration later. Charley shows ways to make the data easy to update manually, by clearly showing where rows and columns can be inserted. He shows how to make the report easily updated from external files, through careful design of the directory structure and carefully sequencing the update process, so the data is first changed, then the report is opened and its links also change.

    I think too that dashboards may not need to be printed (“it depends”, as you said), but they should be printable. That is, dashboards should be designed with the option to print in mind. This assures that the right data is present, that it fits into an appropriate area and is appropriately laid out, and that it is portable.

  3. LeMel: it is in their nature… Well, probably they care, but their care is usually too biased towards form. This is one of those subjects that needs a deeper discussion. I’m working on a case to share with you, it can be interesting.

  4. Thanks for your kind words. I’d like to clarify a few points, if I may.

    One way that I depart from many Excel gurus is in the use of macros.

    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.

    I also depart somewhat in regard to PivotTables. If you’re stuck with a relational database, they’re the best solution available. But an Excel-friendly OLAP database provides much more power and ease-of-use. It also provides a better audit trail.

    With regard to the Camera tool, its primary advantage is that it allows us to display tables and titles whose rows and columns are out of sync with the underlying dashboard spreadsheet’s rows and columns. A dashboard report that includes only charts seldom needs the Camera tool.

    With regard to “magazine-quality” designs, we’re looking for incremental improvement in our dashboards, not perfection. Excel users with no concept of graphical design can improve their reports significantly if they “steal” designs from magazines.

    I agree that many graphical designers ignore the sound visualization advice that Tufte, Few, Robbins, and others have offered. But so do Microsoft developers, who *really* should know better. (See Few’s: http://www.perceptualedge.com/articles/b-eye/excels_new_charting_engine.pdf)

    Finally, with regard to printing, managers are interested in *insights per minute*. I know, this is an idea that one can’t measure precisely, but you get the idea.

    A display method that provides insights in ten seconds is far better than one that takes ten minutes. As a rule, printed displays provide insight more quickly than do on-screen displays. Further, printed displays don’t tempt managers to drill into the data themselves. Managers are paid to manage, not to surf the data warehouse.

  5. Charley: I am sure you know Excel users that spend hours in a stupid task instead of minutes because they are not curious enough to search for a better solution (merging lists is a good example).

    What I mean is that if a user understands how to use advanced functions like OFFSET probably she should also be able to use some basic macros to automate simple tasks. What I try to show in my Demographic Dashboard is that you don’t need a programmer for actions like changing a value in a pivot table. But if digital literacy is very low and there is only one person in the organization at that level, I agree with you, it can be too risky.

    I like PivotTables because they minimize the need for lookup functions. I feel that this is a safer strategy when Excel-friendly OLAP databases are not available.

    The bug and the performance issue severely limits the use of the camera tool. I was playing with a chart format that is easily created using the tool but I can’t use it because of the bug.

    Charts in the financial pages are usually cleaner, so probably they are safer to “steal”, but I really can’t recommend magazines as a source of inspiration for business information visualization.

    I don’t see why “printed displays provide insight more quickly than do on-screen displays” (assuming we have exactly the same output). I can buy the “insights per minute” idea, but how do you measure that when you have an unexpected outlier? How long will it take until you are able to understand it? In a printed dashboard world, it will take you hours, but even with a very basic interactive dashboard it will only take you a few seconds.

    Printed dashboards are probably better suited for stable, business-as-usual scenarios. In a more dynamic environment, you are not paid to surf, but you should have at least some swim skills…

  6. Great discussion.

    I question the wisdom of building Excel based dashboards without using VBA. To avoid using VBA, Charley’s approach spreads the data across multiple sheets. With a VBA approach, all data can be properly consolidated and VBA procesures can extract the necesary data to generate the charts. This video shows an example of a VBA dashboard generated from a consolidated sata sheet.

    http://processtrends.com/Files/Process_Dashboard.zip

    VBA can significantly enhance the quality and reliability of dashboards, when sound information technology management practices are followed.

    Charley is concerned about those oganizations that let their staff develop VBA tools without documentation and staff training, those organizations that rely on a single super user to handle their reporting. This is poor IT management. Why solve this problem by banning VBA when the real problem is poor IT management practice. Let’s solve the the poor IT management practice, and use the best tools that we can.

    The more realistic approach is to establish good program development, testing, documentation and training practices and make sure that more than one user understands the tool and the VBA code.

    I have several VBA based dashboard videos and a downloadable workbook at this link.
    Here’s a link to a simple video and Excel workbook that uses VBA to build Excel dashboard screen layouts.

    http://processtrends.com/Video_list.htm

    Users who can master Charley’s techniques can surely understand the VBA code in this workbook.

    Let’s not ignore a powerful Excel capability (VBA) to compenate for poor IT management practice, let’s make sure we use sound software managment practice as well as the best tools.

    Kelly O’Day

Comments are closed.