Dashboards

Now that we’ve learned about basic principles of graphical excellence and have added a bunch of advanced Excel data visualizaton techniques to our toolbox, let’s put those together to create information rich dashboards.

Intro and Objectives

While the term “dashboard” can mean many things, there are some common design principles to create effective dashboards. And, while there are numerous specialized dashboard building tools, we can do some pretty good dashboards in Excel. There are several advanced Excel techniques that facilitate building effective and maintainable dashboards. As I’ve probably already said multiple times, Excel may not be the best tool for certain things, but it’s often the second best tool for many things. Dashboard building is no exception. The techniques we’ll learn will also prove generally useful for other analytical Excel tasks.

Readings

A Guide to Creating Dashboards People Love to Use

  • it is a white paper from Juice Analytics

  • available in Downloads for this session

  • you can download a poster based on the white paper at at their White papers, Guides and More page. You can find a number of other very relevant white papers at that page as well (and I stuck the poster in the Downloads too).

Downloads

Activities

Dashboard design concepts

Start by reading the white paper by Juice Analytics: Guide_to_Dashboard_Design.pdf. A poster summary is in: JuiceDashboardPoster.pdf. This white paper is a distillation of dashboarding concepts and best practices from different sources including Stephen Few, author of “Information Dashboard Design: The effective communication of data”.

An outline of the white paper is:

Part 1: Foundation

Start with a Strong Foundation

Part 1 of our guide should help you map where you are going before you start throwing charts on a page.

  • How is the dashboard going to add value to my organization?

  • What type of dashboard am I creating?

  • Who is the audience of the dashboard and what are their needs?

  • What is the central thought-line of my dashboard story?

  • What are the key metrics that will focus users on actionable information?

Part 2: Structure

Add Meaning through Structure

Part 2 of our guide gives you the building blocks for constructing your dashboard. Like constructing a house, we wanted to focus on framing the structure before worrying about painting the walls and installing the kitchen appliances.

  • Form: In what format is the dashboard delivered?

  • Structure: How is the dashboard laid out to help users understand the big picture?

  • Design principles: What are the fundamental objectives that will guide your design decisions?

  • Functionality: What capabilities will the dashboard include to help users understand and interact with the information?

Part 3: Information Design

Finish with Precise Presentation

Part 3 of our guide has been about giving you practical advice for laying out your dashboard and presenting the information in charts and tables.

  • How to organize the dashboard page for a clean, stylish layout

  • Choosing and using colors to your advantage

  • A simple font framework for attractive text

  • Picking the right chart for your data

  • Designing easy to understand charts

  • Tips for advanced visualizations and functionality

Excel techniques for dashboarding

There are numerous techniques that (some of which we’ve covered in class already) are useful when using Excel to construct dashboards. The chandoo.org website has a terrific collection of dashboard related tutorials, templates, and examples.

https://chandoo.org/wp/excel-dashboards/

I’ve chosen a few different ones and created annotated examples to help explain how they work.

Some techniques for displaying KPIs

In the folder \chandoo_kpi you’ll find an overview document (in both html and Markdown format) as well as a series of six progressively more detailed spreadsheets. Use the overview to explore them in order. Let’s look at the final product.

Slicers

Slicers are useful for providing a simple way to filter pivot tables and other Excel constructs. In the \chandoo_slicers folder you’ll find an overview document and three Excel files demonstrating a range of slicer capabilities and techniques.

Call Center Dashboard

Finally, we’ll explore a call center dashboard that implements a number of useful Excel techniques.

https://chandoo.org/wp/design-customer-service-dashboard/

This tutorial walks through the entire design and construction process for a call center dashboard. It’s a four part series and is well worth going through. Many nice techniques used.

In the \chandoo_customer_service folder you’ll find an overview document as well as the associated Excel file.

In one of the tutorials, little red and green arrow symbols are displayed in a cell. Here’s a short video I made explaining the general strategy for using special symbols within formulas to get this effect.

In Part 4, a conditionally formatted bar (horizontal) chart is created that includes a vertical line series. This is tricky. Here’s a screencast that shows how to create this particular combo chart. The key is the Select Data item on the Chart Design ribbon lets you have greater control of how series are oriented than you get from simply trying to format the series by right-click | Change Chart Type for Series.

Explore (OPTIONAL)

Excel wizardry with Named Formulas, INDEX() and SUMPRODUCT()

Range names are really named formulas. For the spreadsheet files, download them and open in Excel (i.e. don’t use Google Sheets). If they open in Sheets, just use File | Download.