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.

Dashboard seminar - mark your calendars

I'm sure most readers of my blog will know Jon Peltier's business,  and his excellent Excel/charting blog: Peltier Technical Services. Jon and I are teaming up to present a day-long training session on Excel Charting and Dashboarding. This training will take place on Wednesday, June 2, 2010, at the DoubleTree Hotel in Westborough, MA, in the Route 495 corridor near the Mass Pike.

The training balances Jon’s highly technical expertise in Excel charting and deep understanding of VBA, with my detailed understanding of dashboard design and the business decisions that drive these projects.

Tuition for the all-day, hands-on program is $300, with discounts for early registration. This fee includes meals and breaks during the day plus all course materials. Participants need to bring their own laptops.

For more details, or to register for the class, visit the Excel Charting and Dashboarding home page.

Program Outline

Morning Session

Dashboards 1 – Alex

• Introduction
• Data Quality
• Dashboard Design
• Dashboard Elements

Advanced Excel Charting 1 – Jon

• Appropriate Chart Data
• Chart Types
• Formatting
• Combination Charts
• Dynamic and Interactive Charts

Afternoon Session

Advanced Excel Charting 2 – Jon

• Conditional Chart Formatting
• Error Bars
• Custom Chart Types You Didn’t Think Excel Could Make

Dashboards 2 – Alex

• Excel as a Dashboard Platform
• Dealing with Excel Versions
• Sparkline Add-ins
• Wrap Up

Program Details

Wednesday, June 2, 2010
8 am to 5 pm

DoubleTree Hotel
5400 Computer Drive
Westborough, MA 01581

Tuition includes conference materials, food, beverages
- $300 base price
– $250 before April 15
– $275 before May 8

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..

Excel 2010: Sparklines, not too shabby

There's been some excitement in the data visualization world about Excel 2010's sparkline implementation (we're an easily excited bunch), and less excitement about a patent application that Microsoft has applied for concerning the sparklines (lots of prior art). Anyway, I've played with them a little - they are pretty robust and for a first implementation, far better than their first attempt at in-cell bar charts in Excel 2007.








There are your standard sparklines (if there is such as a thing), columns, markers on the sparklines, such as high, low, first, last, and a win/loss variant. They are missing banding showing a desirable range of values, but the options for controlling the axes are nice. Something which is very nice is that when you create a single sparkline, and then copy the cell down it treats it as a formula and adjusts the data, but the sparklines are then treated as a group, making editing and axes manipulation very easy.

It will certainly be nice to not have to include macros to allow clients to view sparklines that you've created, but there is still plenty of room for the excellent free and paid sparkline add-ins that exist today.

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.

Designing for Color Blind Users

We Are Color Blind recently discussed this pie chart that Gizmodo used in an article. While also noting the awfulness of the 3D pie chart, We Are Color Blind make some great points about designing for the 7% of males (and much fewer females) that are color blind. They have a great tool where you can upload images or a URL and it will show you what the result would be for a color blind person.

It's not easy though - when you have three or more series to show, you reach for blue/red/green combinations first (and in Excel 2007 these are the default colors for charts now). I'm still working to a 'perfect' (there isn't one) color table that avoids the problem colors, but still is as readable as possible for everyone.

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