Using Excel to Create Dashboards

Author Sander Viegers is an experienced designer in the Microsoft Office Design Group. Sander was part of the Microsoft Excel team that developed Excel 2007. His blog can be found at MSDN Blogs.

Maroon, Yellow, Blue and Grey

One thing you immediately notice when looking at the charts that people produce with Office 2003 (left side of the image 1) is that they all use the same colors: maroon, yellow, blue and grey. It is not too surprising when you realize that this is the default color scheme and you need to go through a dozen dialog boxes to change the colors and make the charts somewhat decent and professional looking. The charts you see on the right side of image 1 are found in recent publications like annual reports, newspapers and magazines. They are often created in graphical applications and use more subtle colors.

Before we started redesigning the Office 2007 charting experience, we did a lot of research to get a better understanding of where the pain points are with our customers and what they expect from charting capabilities. This research showed that the most dissatisfaction with charting came from customer’s thinking that charts do not look good. The research also showed that clearly communicating data is the most important thing to achieve with a chart. This means that a chart needs to show all the necessary information to the viewer, but minimize the redundant information. Dissatisfying charts are illustrated in the Image 2 below. The chart on the left does not tell us what the colors mean while the chart on the right tells us what the colors mean in two places.

Given these two data points, our most important design goals were to create an experience that allowed our users to easily create good-looking and meaningful charts.

A Process of Pencil Paper and PowerPoint

My favorite design tools are pencil and paper. These are the most efficient tools to explore different design solutions. In this case, through sketching we explored many different ways to create an experience that would easily allow people to create good looking and meaningful charts. The sketches as shown in Image 3 were discussed with the development team and used as an inspiration source for more ideas.

Some of the sketches were worked out in wireframe prototypes that were build in PowerPoint. This is a fast and easy way to create a conceptual prototype that people can comment on. We tested a series of wireframes as shown in Image 4. Experienced and non-experience users were asked what they thought about the designs. At the time we wanted to know how to introduce the concept of style. No one on the Excel team knew yet what a ‘style’ would mean. However, it appeared an effective way to enable users to easily create good looking charts thru things like color schemes. Similar to style, we found that structural variations were a good mechanism to help people to create meaningful charts. With wireframe images we also explored new interaction paradigms, where we tested the idea of taking over the whole screen for certain tasks (e.g. choosing a chart type). Testing the prototypes was extremely useful to help add direction to the deisgn; for instance this full-page concept of choosing a chart type did not work so well because users liked to keep the context of where they were working when choosing a design.