Great dashboards: dynamic charts in Excel 2007 and tables

I am working with a client to create dashboards to summarize data. Data is appended on a weekly basis, so any charts that show trending data must either be manually updated to include the new data (not workable), or the range somehow magically updated. As we are working in Excel 2007, I have used the excellent Table functionality as the ability use table references to automatically update formulas to include the new data works very nicely.

As it turns out, any chart that is based on data within a table, even if the table functionality was added to the data after the chart was drawn, causes the chart to automatically include the new data when the table expands.

When you're looking at the chart, this isn't obvious - the series names for the data don't reflect this - they just get automatically updated with the new cell references:

=SERIES("Series name",Sheet1!$A$69:$A$79,Sheet1!$K$69:$K$79,3 increases to

=SERIES("Series name",Sheet1!$A$69:$A$80,Sheet1!$K$69:$K$80,3 ) when a new row is added.

Even if you try to manually change the series references to a table reference:

=SERIES("Series name",Sheet1!$A$69:$A$80,Sheet1!TableName[TableColumn],3), Excel recognizes the table reference, but then renames the series back again anyway.

I find this kind of behavior annoying for two reasons - I should explicitly chose if I want a chart to expand a range when I have chosen data based on cell references. Secondly I spent a while trying to use the methods you needed to in Excel 2003 to create dynamic charts, as I assumed (I know..) that the chart was not going to update because of the use of absolute cell referencing.

If you want to know more about how you can create dynamic charts without table referencing, nip over to Jon Peltier's website.

If you're intrigued by these posts on dashboards, reporting, and charts in Excel, I have the perfect one-day training for you - Jon Peltier and I are hosting a Dashboards in Excel seminar on June 2nd. For more details, see here.

What our kids are learning

Being in the data business, I tend to critique most charts or visualizations I see. I am pleased to note that not only is my son's kindergarten already pushing an understanding of data, but that so far the charts have all been of the bar variety.

While of course one could labor on the excessively strong grid lines reducing the data-ink ratio, I think I'll let it slide this time.

I do hope that pie charts aren't being reserved for 1st grade as they are "more advanced"..

If your business has data that you would like to know more about, maybe you need some bar charts hand crafted by me (or my son) with crayons, or possibly very robust tools like Tableau Public.

Normalizing data: Haiti donations by country using Many Eyes

Two things to talk about in this post - I continue my ramblings about the online viz tool Many Eyes, and discuss how normalizing data can provide radically different insights into data.

The data set I'm using is the donations by countries (government and corporations, but not private) to earthquake relief in Haiti. I've seen a few charts around this showing how the US has provided the most funding, but when normalized per capita, Canada and other countries stand out. On a purely data level, and not to denigrate any country's assistance, is this normalization appropriate when the donation sums do not include donations by the public?

Instead it may be more appropriate to normalize by gross domestic product, especially as governments and corporations greatly influence GDP. However, even that's not straightforward as GDP is affected by exchange rate and does not reflect purchasing power within a country. So we could also normalize based on GDP expressed as purchasing power parity, where differences in cost of living are accounted for.

This yields grossly different results, as shown below. I've made all of the bars in each series relative to the largest in that series. Guyana's contribution of a million dollars is massive compared to its GDP expressed in either way, dwarfing other countries' equivalent contributions. This shows that normalization, which is often appropriate, should be chosen carefully and assessed fully when interpreting charts.



Now onto Many Eyes - I've kept the visualization local again, so apologies for those reading without java. I like the result - it was certainly quick to produce and you can play around a little with it. It's not perfect though - to get appropriate height bars meant messing around - I couldn't get a scale to appear on the y-axis. For simple data, especially text based, I think Many Eyes excels, but this would have worked better in Tableau Public.

What I would love to have done would be to have shown this information as cartograms where the area a country occupies on the map is relative to the data value, not land mass. This would have added a visual geographical spin on where donations were coming from, especially for those of us who may have forgotten where Guyana is exactly..

Pareto lines on bar charts - an Excel fudge

I found this aberration the other day on 148apps.biz. It's a pie chart of showing the categories of the apps available on the Apple website. I won't labor on why it fails, but the multiple slices, oblique view, lack of color blind sensitivity, and 0% pie pieces add up to a awkward chart.

While not 148apps fault, the choice of categories that Apple has made available makes the chart less usable - some should clearly be subcategories - strategy for example is probably a game category. 

A bar chart is a better choice - even better are bars with a pareto line showing that the top five categories account for x% of the total apps available. You can add a pareto line to a column chart relatively easily - add a new series (the cumulative percentages that you've calculated) to the column chart, change the chart type of just this series to line chart, and place it on a secondary axis.

However, you can't do this with bar charts as the line can't be plotted on a secondary axis when it's in this orientation.

Instead you are doomed to fudging a solution - plotting an XY line with the X coordinates matching the cumulative percentage, scaled up to match the current full scale (20,000), and Y coordinates that correspond to the position of the category labels. These Y coordinates are quite easy to calculate - if I have 20 labels, the Y position of the first point will be 19.5, the next 18.5, and so on. Copy and paste the new XY data in, change the chart type of this series to XY,  and ensure that you don't have to swap the X and Y column. Excel will add the data with a new Y axis - edit this and set the maximum value to the number of categories (20) - this  lines everything up, then delete this extra axis. I had to add the 0%, 25%, 50%, etc. to the bottom as text boxes.

Download the Excel file here. And yes, those are miniature iPhones making up the bars..

Where in the world (are pie charts popular)?

In my prior post I used data from Google Trends to look at how to summarize complex data. The data I pulled compared how often "pie chart" is searched for compared to "bar chart". In this data set you can also compare the relative search popularity by country. The data is normalized to the number of overall searches coming from that country.

While I was just using these terms to get a data set, this information is actually also interesting - Relative to all searches in a country, India's users are twice as likely to be searching for either pie chart or bar chart. Does this mean that India has more studious users while US users are occupied by searching for the latest Tiger Woods news? I don't know how language is accounted for - if the normalization just for searches in a single language this would explain the difference, as English language searches would more likely to be conducted by business users. This brings up an important point about knowing exactly how data is 'manipulated' prior to drawing any sweeping conclusions.

I've added a second series (green dot) that compares the relative popularity of bar vs. pie (pie chart popularity/bar chart popularity) within a country. Interestingly, "bar chart, is almost searched for as often as "pie chart" in India, the Philippines, etc. compared to the US, where "bar chart" is half the search volume of "pie chart". Go draw your own conclusions about that.

Visualizing data that obscures trends (pie charts are "better")

I've been playing around with Google trends for a while now. It's an incredibly powerful tool for market research, especially when you're looking at new markets, or how the popularity of a brand is changing.  You can look at how a particular search term has trended over five years, where the term is more popular (countries, cities), compare several search terms, and how news stories that bear relevance to the search terms may have impacted the search volume.

The search terms I chose to compare for this example were "pie chart" and "bar chart", partially in response to the friendly back and forwards that Chandoo and Jon Peltier have had recently concerning appropriate use of pie charts. My data adds nothing to their conversation apart from what the public considers 'search worthy', so apologies for the misleading blog title, but the data are perfect for demonstrating how you can better visualize data that have seasonal and other variations that hide overall trends.

Here are the data - relative search popularity of the two terms on a week to week basis since 2004. Some things come out immediately - people search for pie charts more than they search for bar charts. There seems to be seasonal variation within each year, and there seems to be a trend over the years. To investigate these trends further I summarized data by month/year/etc. by grouping the dates in pivot charts.

Here's the seasonal variation - taken by looking at the average search popularity by month of the year, across all five years. Both terms are searched for less in the summer (for the northern hemisphere, but the data are overwhelmingly dominated by users in this hemisphere), and at the end of the year. This would coincide with both school holidays and lower business search volume (as people take vacations). The lines suggest that "bar chart" is less affected during the summer than "pie chart". To confirm this, I've added another series that compares how the two terms vary compared to each other. If the bar for a month is lower than 1, "bar chart" has dropped less relative to its average popularity over the year when compared to "pie chart", so yes, even when you account for the fact that absolute height variation of "pie chart" is greater, "bar chart" does not experience as great a drop-off mid-year.

As a nod towards the bar-chart vs. pie chart argument, perhaps one could argue tongue-in-cheek that "pie chart" is searched for by school children more than business users as the drop-off is larger in the school summer vacation, so therefore bar charts must be more important to business.. No, probably not.

Before we move on though, a quick comment on what we've lost from the 'raw' data. From our summary chart you could conclude that the search volume is about the same in December as it is in the summer months. However, look at the top chart - search volume only drops in December in the last two weeks of the month, whereas search volume is lower for every week of July. For that reason, you must carefully consider the level of summary you chose, whether an average or sum is better for comparisons, and whether you should also show the raw data.

Next we have year to year variation. There is a dip in 2006 and 2007 (for which I have no explanation..) The pattern is the same for bar and pie searches. It is likely to be a reflection of real search volume, not a collection artifact, because two unrelated search terms do not show this dip. I wondered if "bar chart" was catching up to "pie chart", so the line compares the absolute difference. Interestingly, without the average line plotted across, it really looks like this line is dropping - "bar chart" is catching "pie chart" up, but it's misleading - because both the other lines are headed upwards, even a straight-ish line looks like it's dropping. The addition of the straight average line reveals that there is little, if any catching up being done.

Similarly to the prior chart, you have to be mindful of what you've lost in this summary. If you use an absolute count of searches, rather than an average then the results will be different to some degree (and not only because we are missing December 2009 data).

I did also plot these data by day of the month - searches are more slightly popular towards the beginning of a month, but I would attribute that to the two main US holidays, Thanksgiving and Christmas, both occurring the later parts of the month.

How else could you summarize these data?

Charting data with one disproportionally large value

Datavis tweeted about a fun chart he found comparing 'kills' between the top animals - I'm not sure the data are meant to be real or what 'kills' means, but the point is made that humans are by far the worst offenders. Part of the emphasis is that the human number is literally 'off the chart'.

It got me thinking about how to show data like these - where you  have one very large number and other much smaller numbers. There are two things that people may want to know- how much bigger the single point is than the others, and then how the others compare. I've thrown together a few ways you could do it:

Top left: default scaling - it certainly gets the point across that one value far eclipses the others, but you can't really compare the others, or put numbers to them.

Top right: log scale - often espoused as a solution, but frankly I can't stand it. Most people don't get log scales, and I find myself looking to the axis to compare values - I may as well just have the values in a table. It's also difficult to compare small differences between bars.

Middle left: breaking the axis - you can't really visually see how much bigger the human contribution is, but the point is made that it's much bigger, and you can still compare the smaller values. There is no way in Excel to do this automatically - you can limit the axis max of course, but there's no indication that the bar continues upwards.

Middle right: two charts - Arghhh, my eyes, a pie chart and some bevel effects. Actually I quite like this - pie charts are fine for comparing two values if you're not trying to read the exact comparison from them, though the numbers are needed. As for the bevel effect - I blame that on the excesses of Thanksgiving..

Bottom left: aspect ratio - simply enough, make the chart so long that you can compare the smaller values. Even on web only presentation this is a difficult one because of the real estate used up - that is, unless you use this space for other content.

Bottom right: just imply a continuation - similar to the broken axis, and similar to the original chart that Datavis tweeted, but with the actual value shown. Needless to say, this is not a default view in Excel..

Any comments on these, which is your favorite? How would you address this challenge for a dashboard vs. print publication?

Excel 2010 in-cell bar charts (are much better)

I've posted before on my opinions about Excel 2007's in-cell bar charts, and mentioned that Excel 2010's are supposed to be much better. Well, having got my hands on the beta version yesterday, I can say yes, yes, yes, they are much better. The gradient option is still there, but is not a default. Zero, and very low numbers compared to other numbers in the range have no bar height (see examples A and B below), AND there's no option to turn on the fake bar height.

Negative numbers in a range result in an axis being drawn, and the bar appearing the other side of the axis, in a different color (D). You have full control of colors of the axis and bars. But, wait, there's more. You can control the max and min of the chart - great if you have multiple in-cell bar chart ranges that you want to be able to compare.

You can't have the bars offset from the data as they are a conditional format, but you could just slap in a formula where you want the bar: "=cell where the data is", and then click the option to show just the bar, not the data (E). Even the gradient option is better, as there is a border by default allowing you to see where the end of the bar is (C). Finally, the resolution of the bar (i.e. when a bar height goes to zero), depends on the width of the cell (B). Great job Microsoft.

Is it just me? (software defaults)

I don't know how the wrong decisions about software defaults are made. Take the example on the left that happens to be from Excel 2007, but persists since the dawn of Excel. The bar chart is the default drawn when you choose the data shown.

To me it seems nonsensical that the categories top to bottom are in the opposite order to that of the data, and that the order of the series is reversed (the yes bar should be on top).

I can reverse the order by choosing reverse category order in the axis format menu, but that also switches the horizontal axis to be at the top - you need to format it again to move the axis back down. Perhaps they did a user-group study and I'm the odd one out - the expected behavior is that the order is reversed. If not though, was this a decision that was made (wrongly) by a single person or software team? Is it a bug that no-one ever noticed? Was it understood to be wrong in an earlier version but not corrected due to concerns about compatibility? Am I just too fussy to think that defaults should reflect what the majority would expect the default to be?

Old news

A few days ago I talked about the gradient effect on the Excel in-cell bar charts. Coincidently today I came across a post I made to the Dashboard Spy blog back in the heady days of May 2006 when Office 2007 was in beta release. I posted a quick review of some of the conditional formatting available, including criticizing the gradient effect, but liking the ability to shrink the charts to make effective sparklines.

I was clearly too blown away by Office 2007 (or too confused by the ribbon?) to notice that even in my example shown, the bar chart scale does not start at zero, giving a false impression of the variability of the data. Anyway, maybe I was one of the first to vocalize my dislike of the gradient...

On a related note, a commenter on another website in Sept 2006 gives this possible reason for Microsoft's decisions on the in-cell bar charts:

The default graphs were hideous at best, but now, thanks to a focus-group-tested and user-centric decision probably made by marketing drones without a brain: Microsoft Excel deliberately misrepresents data, because it turns out, users didn't like empty cells in bar-graphs; idiots :-)

Undeniably harsh and unfair, but the commentator has some points that I will touch on soon about user-groups and designing interactions.

CNN's Newspulse

CNN recently updated its homepage. I'm not a great fan of how it looks, but they did add 'Newspulse', a listing of the most popular stories. The screenshot is of the tool - they have a smaller add-in for the front page with the top five stores and the bar chart. I'm not sure what the 'popularity' measure is - page clicks I would presume, but it's an interesting slice and dice affair.

I'm not really sure I care about the data - the whole point of the web, as compared to television or radio, is that I get to chose what I consider important, not other people. And if it's popular, surely I've already seen it? Anyway, if you are going to do it then I'd make some changes.






I've just focussed on the charts - there are other changes I'd make to layout. I've kept the bar charts as comparing the relative popularity of stories is of interest, but I've de-emphasized them. I've added sparklines (a type of information graphic characterized by its small size and data density) which show how the popularity of a story has changed over the time period chosen. You can see when a story broke compared to others, how quickly it gained and lost momentum, and whether it's still on its way up, or is old news..

Excel in-cell bar charts

Often you don't need to create a big chart to get your point across. The concept of having bar charts in a cell has been around for a while, and Microsoft picked it up for Excel 2007.

As soon as I saw them in a beta version of Excel, I started hunting around for the property formatting to get rid of the gradient on the bars - what's the point of having a comparison if you can't actually easily see where the bar ends? Unfortunately there is no way to change this - lots of washed out color options, but no option for just a solid color.

I'm certainly not the first to point this out, or that the zero has a default thickness of 10% (!?) making it hard to distinguish between zero and low numbers and making comparisons misleading between very high numbers and low numbers. Equally the bars are scaled by default between the lowest and highest number, not zero to the highest number.

Excel 2010 fixes these issues - allowing for solid fill (though the gradient fill is still the default option), defaulting to scale the bars from 0, and adding a few nice features. I'm still surprised that they didn't get it right first time - it is so obviously wrong in the current version.

As 2010 isn't out yet, and it will take a while for many companies to upgrade, the best way to do in-cell bar charts is using a formula to create the bar. You can even get fancy and have multiple bars in one cell. These methods aren't perfect - you have to choose your font carefully to get a solid bar, and the resolution to show differences in bar heights is limited because you're using characters instead of a graphic - they work well though. The example shown uses the formula "=REPT("|",cell ref to left), | is the pipe symbol. The font is Script, bolded, and I've toned the bars down to a light grey and added conditional formatting to show values 35 or higher