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…

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. [fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”][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?[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]