10 Tips for Building a Dashboard in Excel

This post has an unintentionally link bait-y post title, I realize. But, I did a quick thought experiment a few weeks ago after walking a client through the structure of a dashboard I’d built for them to see if I could come up with ten discrete tips that I’d put to use when I built it. Turns out…I can! I struggled to figure out the best order to put them in, loosely tried to make it from an early-to-late in the process thing, and then threw my hands up and just started writing.

Pre-Tip: Skip the “Insights”

This is more of a soapbox than a tactical tip, so I’m sneaking it in before we get into the meat of the content:

Do NOT leave a place for text-based insights or recommendations.

I’ll leave it at that. If you care to hear (or argue with) my rationale…I’ve written a whole post on it.

Tip #1: Plan First

This should go without saying, but massive overhauls after a dashboard are built can be tough. So, start by getting s solid set of requirements that includes:

• What metrics will be included
• Which of these metrics are KPIs versus just supporting/contextual information
• Which views of each metric will be included: a trend (and, if so, its granularity: daily, weekly, monthly, etc.), a total, a comparison to a target or a prior period, etc.

I often even do a little sketching of the dashboard — quick thoughts on how I can organize the information based on what I’m planning to include. It’s a lot faster to quickly scrawl boxes on a piece of paper than doing wholesale rearrangements of the information in Excel.

I’ve never had my plan be a 100% match with the final product. But, if I get it 80% figured out up front, I’ll have more time to figure out how to best cover what I discover along the way as I build it out.

Tip #2: Stick to One Page (One Screen)

This is a neuroscience-based tip. As Stephen Few puts it:

“…information that belongs together should never be fragmented into multiple dashboards, and scrolling should not be required to see it all. Once the information is no longer visible, unless it is one of the [3 or 4] chunks stored in working memory, it is no longer available.”

Since the point of a dashboard is to provide an at-a-glance view of performance, and since we want to see it all at once, the dashboard should be limited to one screen.

This doesn’t mean that there can’t be additional screens of drilldown information, but this should be wholly contained subsets of data. The main dashboard should be one screen and one screen only.

Tip #3: Figure Out Some “Widgets”

Based on the planning that you did, you’ll likely have 1-3 different types of metric displays. Figure out how you’re going to display them. Maybe, for some of the metrics, you need to show a total, a comparison to target (because they’re KPIs), a comparison to a prior period, and a trend. For others, you may just need to show the total and a comparison to a prior period. I like to design “widgets” for each type of metric display that I’m going to include. Some examples:

It’s worth putting some care into the design of your widgets. Figuring out the font size (I like to really bump my KPIs up by a lot), the palette (match your corporate one!), and how multiples of the widgets will fit next to or above/below each other (in the widgets above, you can see where there are labels outside of the widget and can imagine how those labels don’t need to be repeated when the widget is reused for additional metrics).

Tip #4: Make Narrow Columns

While Excel is way better than any of the major web analytics tools when it comes to layout flexibility, it’s still, inherently, a grid. For years, I would try to figure out what the ideal configuration of column sizes was to support the layout that I wanted. A year or two ago, I got tired of inadvertently painting myself into corners with that approach and started just making all of my columns (for the presentation layer sheet — not for all sheets! More on this in Tip #6) the same width and narrow:

It looks a little odd (but users never need to see it; see Tip #10), and it then requires merging cells as you build out the layout, but it’s worth it. And, this is one of the benefits of using widgets: once you do the requisite cell-merging to build the widget, that entire widget can be copied and pasted for each new metric.

Tip #5: Design for Printability

This goes for more than just dashboards. I’m amazed how often I see spreadsheets that someone might want to print out — to review offline, to take with them to a meeting, or even to mark up — that aren’t readily printable. Go ahead and define the Print Area (this lets you leave a little extra white space at the top and left of the spreadsheet — a blank row and a blank column — without impacting printing; exclude those from the Print Area). Adjust your layout, as well as the orientation, margins, and header/footer, to make sure that someone can easily print the dashboard.

Note: It’s tempting to just use the “Fit to Page” feature. I try to avoid that, because it’s then easy for the dashboard to start scaling drastically — 60% or 50% — to the point of unreadability when printing. Better to just add a little care and testing to the setup of the dashboard itself.

Tip #6: Be Organized

This is a “dashboard architecture” tip. But, over the years, I’ve found myself consistently using different worksheets for different distinct purposes:

• Presentation Layer — this is the dashboard itself; there is no data directly housed in this worksheet. I usually name this tab “Dashboard.” If I have drilldown sheets, then those also are considered Presentation Layer sheets. The data shown on these worksheets come from either the Data sheets or the Transformation sheets (or some combination)
  
• Transformation — this type of worksheet is not always needed. It depends on the structure of the raw data and the complexity of the dashboard. But, this is one or more sheets where I put pivot tables or lookup tables that grab data from the Data sheets and put it into an aggregated (pivot table) form, and/or grabs only a subset of the data (for instance, the data for only the selected report period).
• Data — this is the worksheet(s) where the raw data for the dashboard actually goes. Be it Report Builder queries, Facebook Insights exports, or anything in between, the key is for these sheets to be structured as close to the structure of the raw data as possible. If the data is automated (see Tip #9), then the structure will have to match the raw data exactly!
• Settings — this is always a single worksheet, and it includes the various constants and lookup tables that the dashboard needs. Examples of constants include: the % below target a metric has to fall before I display a red indicator next to it on the dashboard, how many periods I want to include in my sparkline trends, formulas to calculate the start and end dates for different metric displays based on the selected report date (see Tip #8), and so on. I always store these values in named cells, as they get referenced extensively by the Transformation and Presentation Layer sheets. Examples of lookups include mapping a “pretty name” to raw identifiers that are included in raw data exports. Or, the values that need to be shown in the date selection dropdown (see Tip #8).

Most of these worksheets ultimately get hidden, but they make for a clean overall architecture for maintaining and documenting (Tip #7) the ins and outs of the file.

Tip #7: Document, Document, Document!

Just as it’s important to document the specifics of where the data came from and how it was pulled when doing an analysis, I use comments and ancillary cells in the Data and Settings worksheets to provide in-context documentation. The closer this goes to the actual data, and the more complete it is, the better! There will come a time when the dashboard needs to be updated or an underlying data source changes (Facebook Insights, anyone?), and having in-context information of exactly what the data is and where it came from is an enormous time-saver.

There is also some documentation that needs to go on the Presentation Layer (Tip #6): the date / date range for the report, definitions for any included metrics that are not clear to the casual user, and any important caveats/clarifications about the data. Much of this “on the dashboard” documentation can be included in footnotes, but a dashboard is a failure if one of the recipients has questions about what the data actually is.

Random personal background anecdote: I spent several years as a technical writer early in my career, which included writing online help for the software my company developed. That experience has likely contributed to my stickler-ness on the clarity-and-completeness-of-documentation front.

Tip #8: Dropdowns…Even If Only You Use Them

At a minimum, every dashboard I develop that is not fully automated (read: “scheduled and published through Report Builder“) has at least one dropdown on the dashboard itself: a selector to choose the date to display. That single cell (as a named cell!) should be the key by which all of the displayed data gets updated. I rely heavily on dynamic named ranges to make that happen, but it means I never-ever-ever manually update the chart or cells that display the final data.

Depending on the dashboard, I use in-cell dropdowns to enable additional control of what gets displayed on the dashboard. For example:

• Controlling whether the dashboard is a weekly, monthly, or quarterly display of the data
• Controlling how many periods to include in historical trends (sparklines or charts) on the dashboard
• Controlling which metric is used to sort any “Top X” lists of values on the dashboard, as well as whether the metric is sorted ascending or descending

The possibilities are endless. Obviously, the more control you put in a dropdown, the more logic you have to build into the Transformation sheets (Tip #6), and that requires something of a cost/benefit assessment. An Excel dashboard cannot be a standalone in-depth analysis tool, but it can provide a first-level dive into the data as a jumping off point for deeper analysis.

Note: I’ve found the best Excel feature to use for making in-cell dropdowns is the Data Validation feature. I generally wind up using the INDIRECT (see step #3 in this post)  function to reference named ranges for this — often named ranges that reference cells or tables on the Settings tab (Tip #6).

Tip #9: Automate! (Or Quasi-Automate!)

This tip should probably go without saying, but I regularly run into recurring dashboards or reports that are not as automated as reasonably possible. Several of the tips I’ve already listed help with automation or near-automation, but, the better you know Excel, and the better you develop a good overall dashboard structure (Tip #7), the more automated your dashboard can be.

The biggest challenge with automation is usually getting the raw data out of its home system and into Excel. Depending on the data source(s) needed, the platform itself may have an Excel automation tool already (Adobe Analytics has Report Builder, of course, and Google Analytics has a number of third-party tools to do the same thing; my three favorites: Shufflepoint, Analysis Engine, and Supermetrics).

In the absence of an Excel integration, most platforms offer exports in Excel or comma-delimited text (or both). Figure out which export gives you as much of the data as you need in a single flat table, and then use that as one of your Data worksheets (Tip #6). For Facebook data, I always use the .csv export option, but there are so many columns, and the specific order of the columns can change, so I actually have a macro I use to clean up that export. This is an example of quasi-automation: I still have to go to Facebook Insights and export a file, but I then have a macro that takes 5 seconds to turn that file into the raw data that I drop into a dashboard to update the data.

Tip #10: Hide the Unnecessary

I once sat in a meeting with the CMO of a $4 billion company where we reviewed an Excel dashboard I’d built. One of his top VPs asked, “Will I be able to view this dashboard on my phone or my iPad? Does the software support that?” The only real clue she would have had that the dashboard was actually just a spreadsheet was the little green icon in the top left of the window, and that was the subtlest of clues. At the same time, the dashboard had some basic interactivity (using the techniques described in earlier tips) and, while she probably wouldn’t have much luck with it on her phone, her organization’s incremental licensing fee for the dashboard was: $0.

So, what to hide? It doesn’t take much:

• All the worksheets that aren’t the Presentation Layer (I sometimes use the xlVeryHidden property for this; that’s a good way to bury the sheet one level deeper than the basic Hide feature without using a password that you will then have to keep track of; if someone knows enough to change the property back to Visible, they probably know their way around Excel enough that it’s okay for them to poke around in that content)
  
• The column headings — because I make a bunch of narrow columns, the headings can be very distracting; this is a simple checkbox in the View group in Excel:

• The formula bar — this is also a checkbox on the toolbar
• Sometimes…I hide the actual worksheet tabs at the bottom of the screen, too.

If the dashboard is something that I have to manually update, I often record a simple macro that does all of my hiding (as well as one that does the unhiding) for me. I don’t like to distribute workbooks with macros in them — they have a pesky habit of giving the recipients a scary warning when they open them — so I sometimes put these macros in a separate workbook that only I use when working on the dashboard.

<whew!> I Didn’t Promise “Quick Tips”

Hopefully, there’s a nugget or two here that you can apply in your day-to-day work. Some of the tips I considered are more “Excel tips” than “dashboard tips,” but (go figure!) I actually covered a number of those a while back in this post.

What’s missing? What are some of your tips and techniques for getting more power out of Excel as a dashboard delivery platform?