Excel 2010 new features – Sparklines

As discussed previously, we have some marketing information about what we will be seeing in the next release of the Office system, but not really a great deal of technical information. The Excel team are starting to blog a bit more now that the Technical Preview is underway; their 10,000 foot view is a good starting point to find out what’s coming, or you can read the press release. On the official Office 2010 site there is a video by Albert Chew, Product Manager for Office, which shows off some of the new features of Excel 2010 (sorry, no direct link to the video available, it’s linked in the menu on the left of that page). On the Microsoft PressPass videos page there is an Excel 2010 demo video which you can also download (16MB wmv file). This covers two new features – sparklines (from the start) and slicers (from about 1min 55 into the video)

As more information emerges, I’ll write in more detail about some of the new features. Today let’s have a look at probably the most eagerly awaited extension to Excel’s data visualisation capabilities – sparklines.

Sparklines in Excel 2010

Sparklines are very dense microcharts used to display simple information, usually showing historical values to give context to the current data. The term was coined by information visualisation guru Edward Tufte and discussed in a whole chapter in his book Beautiful Evidence, which describes sparklines as “intense, simple, word-sized graphics”.

Examples might include past share values, commodity prices, exchange rates, or internal business key performance indicators. Lines may have key points highlighted (high value, low value, last value), show a trendline or normal band, but otherwise will be deliberately uncluttered to aid easy interpretation.

Typical sparkline sales versus targetThe example to the right shows the previous 12 months sales and target, with the highest and lowest sales figures highlighted. Some people might prefer to show the figures to the right of the sparkline as they relates to the final data point and therefore the right hand end of the plotted line, but this is a matter of personal preference. Although this example is show here in quite a large screenshot, the trend is very clear at much smaller sizes too.

While lines are by far the most common choice for sparklines, especially to show changes over time, other formats may be found – columns to show breakdown of a total by category for comparison, for example. Another popular use is to track success and failure, such as wins and losses for a sports team, a technique described well by this article on sparklines at Bissantz, the creators of SparkMaker, an add-in for Excel.

Now Excel 2010 does sparklines straight out of the box, no add-ins required, which is a testament to how popular these kind of dense data visualisations have become, especially in the BI and business dashboard community. Of course, you could already do this with add-ins or just a lot of patience to customise normal charts to suit the restricted size of a typical sparkline. The way these are shown in the videos is that they are much easier to apply to a range of cells rather than having to build then copy and paste for multiple rows, adjusting data ranges and so on. So you build one in a cell, choosing the data range that it relates to, and then you can use the usual “fill down” technique by dragging a cell handle to create more sparklines for subsequent rows of similar data. Various formatting tools can then be used to quickly change colours, show or hide markers and highlight highs or lows, first and last point and so on. Whether these sparklines can be made to be dynamic to accommodate new columns or rows of data (using dynamic named ranges for the source, for example) is not clear at this stage, but hopefully this is covered. Currently the three formats which are built-in are lines, columns and win/loss blocks. Thankfully no-one tried to build a spark-pie-chart!

Sparklines are good news, if used well

These could be a really powerful addition to many pivot tables or other reports. Let’s just hope their convenience is matched by sensible controls so that first-time users do not end up with pretty but meaningless charts. The screenshot of sparklines shown on the Microsoft PressPass site is a fairly poor example – the lines and bars show the same data (acceptable from the point of view of a demo shot showing both formats are supported), and they actually chart all the visible data. Part of the point of a sparkline is to show additional data in a very dense way – for example you might display the current value of some measure, and a line to show the past twelve months so you can see what has happened to get to that position. Showing monthly figures as well defeats the point of the density possible here, and the principle of being able to visualise the information available rather than wading through figures to try and interpret the patterns.

Related news – data bars are being improved

I haven’t seen many details, but the data bars and some other conditional formats introduced in Excel 2007 are being improved in this release. A key feature being mentioned is the possibility to show negative values with data bars. For those of you unfamiliar with the strange implementation in 2007, the lowest value was displayed as the shortest bar, highest as longest. So if you had a mix of positive and negative numbers, a zero value would appear as a bar of an intermediate length, and there was no really useful meaning for the lengths of the bars. For a set of all negatives, the highest value (nearest to zero) had the longest bar, and the lengths do not have a proper relationship to one another, as seen below.

Click for larger image of unhelpful Data Bars in Excel 2007

In this example I have highlighted some figures – two figures for the variation between actual sales and target in monetary terms (2,280 and –2,280) and two for the variation in percentage terms (9.0% and –9.0%). Notice that the lengths of the bars for these pairs of figures have absolutely no relationship to one another. A shortfall of sales by 2,280 should surely be shown using the same length of bar as an overachievement by the same amount? As for April – sales target was met exactly, this is a completely neutral achievement but the bar uses a substantial amount of “data ink”. This amount also varies depending on the magnitude of the highest and lowest values in the series!

Hopefully the new implementation for negatives in data bars in Excel 2010 will give us a zero-length bar for zero, and bars extending left for negative and right for positive (optionally with colour coding to highlight negatives even further). Additionally, cleaning up the bars to lose the graduated fade-out which makes the ends unclear, and allowing the shortest bar to have zero length rather than a small minimum size would be better (especially so that a zero value is encoded as a zero-bar).

Next time – data slicers

In my next post I’ll take a look at the new data slicers feature to try and work out what it does and if it really introduces new capability or is just a new way of presenting some existing tools like data filters.

About ukcrmguru
I'm an MVP for Dynamics CRM, consultant, Microsoft Certified Trainer and self-confessed geek. I also lead the UK CRM User group when I'm not too busy with all that.

3 Responses to Excel 2010 new features – Sparklines

  1. I do like the sparklines option you describe. For a single cell I have been using the rept function which repeats a character N number of times based on a cell.

    =REPT(“:”,B3)

    Is there a way of using this on a range of cells or similar other than wait for 2010 sparklines?

  2. Rachel Garrett says:

    Greetings!

    In your example of a sparkline showing sales vs. target, was that created in Excel 2010?

    Thanks,
    Rachel

    • Adam Vero says:

      No, I wrote this article in July 2009, and although I was on the Technical Preview programme for Office 2010, that was not until later that year so I had not had my hands on the real product at that point.
      The term “sparkline” was already widely used in the Business Intelligence / Data Visualisation community, and I often used to demonstrate the idea of using a minimalist, stripped down chart next to, and a similar size to, related data, especially in the context of management dashboards. The chart in this article was such a “micro-chart”, produced in Excel 2007. Take a chart, strip away all the bits you don’t need in this context such as legend, axis values etc and make it really small, and you are well on the way!

%d bloggers like this: