Hyperlink Legends to Highlight a Series

In a recent post Jon presented a way to dynamically hover over the chart legend to highlight a data series. Jon’s method is very smart, as it really shows the capabilities of the event rich Excel chart programing model.  The downside of this approach is that it requires you do code some VBA and only works with chart sheets. The chart events also work in theory for embedded charts, but you have to activate the chart, in order to make Excel handle your events, which is one click you want to avoid.

So I picked up Jon’s idea and tried to combine it with ParamLink. In  The Missing Link I introduced the (free) ParamLink Add-In. It implements a new hyperlink formula ParamLink(). When the hyperlink is followed the formula can set cell values or define names.

image

 

Continue reading “Hyperlink Legends to Highlight a Series”

Excel Dashboard Competition: Bank Dashboard

This blog post is the first in a series of blog post that features the winners of the 2008 Excel dashboard competition.

“A dashboard is a visual display of the most important information needed to achieve one or more objectives; consolidated and arranged on a single screen so the information can be monitored at a glance.”

Stephen Few, Information Dashboard Design (2006)

The dashboards were judged on the clarity and effectiveness of their design, particularly

  • Clean and clear organization
  • Effective table and chart design
  • A single-screen display, properly designed for the web, screen or print outs

Furthermore we honored the technical aspects of the dashboard, did it use effective (Excel) techniques for

  • The Dashboard layout
  • Data management, pulling data from a database or data warehouses
  • Data logic and calculation : YTD figures, variances, etc….
  • Dashboard delivery: Sharing the dashboard via PDF, the web or as an Excel Workbook

Today we will review the winning entry, Wades Stokes Bank Dashboard:

image


Continue reading “Excel Dashboard Competition: Bank Dashboard”

The Dashboard Squint Test

Before we go and review the 2008 Excel Dashboard Competition Winners I have to make you familiar with  the Dashboard Squint Test.

Software usability experts and web designer use a quite effective way to assess the organization of a web page or a user interface, the so called Squint Test. You squint your eyes and make an assessment on the overall layout, of elements that stand out, the visual balance and other characteristics of an effective user interface.

SquintTestScreen0001

 
This test can be easily extended and applied to dashboards. Squint your eyes and assess the overall layout.

SquintTestScreen0002


Continue reading “The Dashboard Squint Test”

Excel Dashboard and Visualization Boot Camp

Here the announcement of the Excel Dashboard Boot Camp hosted by the two experts Jon Peltier and Mike Alexander:

Microsoft MVPs Jon Peltier (Peltier Technical Services) and Mike Alexander (DataPig Technologies) are joining together to bring you our first annual Excel Dashboard and Visualization Boot Camp!

Continue reading “Excel Dashboard and Visualization Boot Camp”

Microsoft, Pimp Down My Ribbon

There has been a lot of criticism regarding Excel 2007, particularly about the new chart engine, the UI inconsistencies and the Ribbon. Jorge yesterday concluded that Excel 2007 is Useless and Jon wrote a comprehensive analysis about Changes to Charting in Excel 2007 where he expressed his concerns regarding the new Charting. In 2006 Stephen Few reviewed Excel 2007 charting and concluded Excel 2007 charting Preview of an Opportunity Missed. Charley Kyd did a survey about How Do Business Users Like the Ribbon and came back with the result that about 56% of all respondents had a negative opinion about the Ribbon

Continue reading “Microsoft, Pimp Down My Ribbon”

The Missing Link (Part 1)

Every good discipline needs a missing link. Evolutionary biology had a missing link between humans and the ‘lower’ animals. Physics has a missing link between quantum mechanics and general relativity. The Information Visualization community discovered the Missing Link Between Information Visualization and Art.

Now we discovered the missing link in Excel Data Visualization.

As we can see in Wades winning Bank Dashboard we can greatly increase the amount of information that can be included in a dashboard by using sparklines in an overview table

image

 

Continue reading “The Missing Link (Part 1)”

In-Cell Variance Charts

In financial report we are constantly comparing the actual numbers with our projections using variances.

A quick reminder

Absolute variance = Actual – Budget

Relative variance in % = (Actual – Budget) / Budget

Doing this over a report of even 10 lines requires row by row number comparison, and is not something which can be easily & quickly scanned. Showing the variances in a classical Excel charts is problematic, as you constantly have to go back and forth between the table and the chart to scan exact numbers and the variance tend in the chart.

image

 

 

In-Cell variance charts are a compact, data rich and efficient alternative.

image

 

To create in-cell variance charts you have basically three alternatives:

1. In-Cell Chart Using the REPT Function

Rolf Hichert presents a couple of years ago on his web site (German) a character based approach. The method got quite popular when it was first presented on the  Juice Blog. The main idea is to create the bars repeating a character using the REPT function. The function REPT(“●”,5) returns “●●●●●”. The disadvantage of this approach is that you have to set up all the formulas and that don’t have a continuous scale. E.g. to show 15% you either show “●” or “●●”, but there is no way creating fractions of a character. One of the advantages of the rept approach is that the bars are automatically aligned in the grid. When you increase the row height, bars are still perfectly aligned with the numbers.

image

 

Click here to download the Excel file

2. In-Cell Chart Using MicroCharts

Another smart way to overcome the setup and scale problems of REPT is using MicroCharts. Charts are created from MicroCharts fonts including bars, line segments and pies. A chart is represented as text that is formatted with the MicroCharts fonts. Similar to the rept fuction charts, you can utilize all the rich Excel capabilities for MicroCharts that you would for normal text!:

  • Text alignment
  • Text orientation
  • Font size
  • Conditional formatting
  • Automatic alignment when the row height changes

image

 

 

Unlike the rept function MicroCharts support continues scales value axis. The chart formatting is very similar to the options with standard Excel charts:

image

 

Hitesh Patel Winner of 2008 Excel Dashboard Competition used the Micro Bar Charts to visualize variances over areas and territories in a very cleanly presented table in his Pharmaceutical Sales Dashboard .

image

 

3. In-Cell Charts Aligning a regular Excel Chart in the Grid

A third approach is to use regular Excel bar charts:

image

 

 

The trick is to set chart area to transparent and to manually align them to the grid. The main problem with this method is that the setup of the chart can be very tedious. You will have to fiddle quite a while till the bars align perfrectly with the grid, and each time you change the row or column height width you have to readjust the chart area:

image

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:”Times New Roman”;
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}

Update 06/28/2008: Jon added some insights how to handle variable rows and fourth approach for incell charts using graphical objects:

One problem arises when the number of rows covered by the chart is variable. When you have an update, you need to rely on VBA code to realign the chart, and this is not always reliable. To counter this (and to apply some “interesting” formatting requested by the client, I developed some VBA code that read values from each row and built chart using a series of rectangles and other shapes. These were aligned appropriately with the grid more simply and reliably than a chart would have been. A sample of this is shown in In Cell Charting with Shapes.

I ran into additional issues with another client who insisted that his worksheets be viewed at a zoom that filled the entire width of the screen. This has all kinds of negative issues, especially with charts. (And on my widescreen laptop, it needed a zoom of around 150%, so I had minimal rows visible. But a client is a client.) So I built some additional graphs based on Excel shapes, as illustrated in In Cell Bar Charts.

You could therefore add a fourth approach, though it isn’t accessible without code, and that is “Graphs using Graphical Objects”. This is after all how we all started with graphing data in second grade, with paper and crayons and perhaps a straightedge.

Extra tip: To further enrich your variance report with some historical context, provide the historical performance in an extra column as a sparkline:

image

 

Graphical Table – Abortion Data 1980 – 2003

Here the history of the Abortion data set

1) Jorge used some U.S. Census Bureau data (original Excel file) to visualize the abortion ratio as a small multiples chart,

2) In Small Multiples – Abortion Data 1980-2003, I have written about Jorge’s panel chart. I like the panel (small multiples) chart, but felt that it can be improved.

3) Jon Peltier posted on Re: Abortion Ratios 1980-2003 and Interactive Multiple Line Chart some nice Excel techniques to create interactive charts to analyze the Abortion data set.

Here is a different view on the data set as a Graphical Table using MicroCharts. The Graphical Table is a hybrid between the panel (small multiples) chart and a plain table of numbers. It nicely integrates the numbers with small charts, so-called sparklines, in a table. The sparklines show you the trend of the abortions over the years and the in-cell bar charts give you a feel for the distribution over the age groups:

 

Small Multiples – Abortion Data 1980-2003

Jorge used some U.S. Census Bureau data (original Excel file) to visualize the abortion ratio as a small multiples chart.

I like this chart as I am a big fan of small multiples displays. They help us to understand the nature of multi-dimensional data.

Jorge asked for suggestions to improve his chart and I came up with these:

  • The abortion ratio is calculated as the number of pregnancies that end in an abortion per 1000 pregnancies. I would put the definition of the ratio in the title or a footnote (Number of abortions per 1,000 live births). Most places where I have seen this data presented tend to do it in this way too, although my preference would be for a % format.
  • I think that the data for Race, Marital Status and Age Group are actually 3 different stories. It took me several scans of the whole chart set to realize this. I would add some white space between the Age group charts, the Race and Martial Status charts, to make it clear that are comparing different variables.
  • Jorge shows an integrated line chart for Marital State and for Race but a small multiples chart for Age Groups. For a more consistent chart reading I would use small multiples charts for all variables.
  • The axes of the Percent Distribution bars are not labeled, and have no scale. This coupled with varying meaning of the color encoding is confusing. The percentage distribution data for the Age < 15 lies around the 1% level and so cannot be seen on this scale even though data does exist. We also cannot encode the way this measure also varies with time over the same period. In fact we miss the fact that for women over 25 years the percentage distribution is actually increasing.
  • I’m not sure if you need to show the all age groups in the Age group charts as light Grey lines. I know that you want the reader to compare the current age group in the context of the other age groups but this exactly what the small multiples are for anyway.

I like Jorge’s idea of integrating the abortion ratio and abortion percentages, but I found having another group of small multiples provides a more consistent view. If we don’t have the percentage distribution numbers then we can easily draw the wrong conclusions about the data.

Here my small multiples chart for the Abortion by Age Groups:

image

 

Update 6/22/2008: Jon Peltier posted on Re: Abortion Ratios 1980-2003 and Interactive Multiple Line Chart some nice Excel techniques to create interactive charts to analyze the Abortion data set.

2008 Excel Dashboard Competition Winners

After much deliberation and debate, we are pleased to announce the winners of the 2008 Excel Dashboard Competition. We were impressed by many of the entries, and thanks to all of you who entered. It’s good to see MicroCharts being put to effective use, and adding value in such a variety of business scenarios and sectors. We had entrants from a broad range of industries including Banking, Insurance, Healthcare, Manufacturing, Oil and Gas and Pharmaceuticals.

The winners are:

1) Wade Stokes – Bank Dashboard

 

 

 

 

 

 

 

 

 

 

Displaying many disparate Banking Key Performance Indicators, and designed as the basis for the Management review of business performance, it truly achieves More Information per Pixel.

2) Jim Uden – Outpatient Surgery Center Dashboard

 

 

 

 

 

 

 

 

Developed for Meridian Surgical Partners, as a one page snapshot for the review and presentation of partnership level business operations and trends.  Jim also includes probably the best associated description of dashboard content and the thought processes involved which we’ve seen.

3) Hitesh Patel – Pharmaceutical Sales Dashboard

 

 

 

 

 

 

 

 

 

Developed by Hitesh Patel and Mike Askew of Data Intelligence, for Bristol Myers Squibb. A key report for the Regional Sales Managers, containing the information required to run the business in terms of cash, growth, share, and competitive performance.

Congratulations to all 3 of our winners. Have a look at our competition page for screenshots and some background on the winning entries. Over the next few weeks we’ll be analyzing the entries in more depth at http://blog.xlcubed.com. We’ll overview each, cover some of the techniques used, and hopefully suggest some further improvements.