Editor at large
AccountingWEB
Save content
Have you found this content useful? Use the button above to save it to your profile.
Editor at large
AccountingWEB
Save content
Have you found this content useful? Use the button above to save it to your profile.
The new version of Excel isn’t likely to contain many surprises, but Simon Hurst has delved more deeply into what’s coming. First up, he investigates what’s possible with the new Sparklines feature.
ExcelZone has already introduced some of the new features in the technical preview of Excel 2010. To provide some more information on what the new features can do and how they could be used, this article looks takes a step-by-step approach to using these forthcoming additions to Excel.
We’ll start with a fairly basic pivot table based on the Microsoft Access sample database Northwind. Here we’ve linked to the Invoices data in Northwind and created a simple table of salesperson performance over a particular year. The year and month fields are created by using the ‘Order Date’ field as a column heading, then right-clicking on any one date and choosing the appropriate options from the grouping dialog:
Sparklines
We’ll start off by looking at the Sparklines feature. It has been pointed out that “Sparklines” is a slightly misleading term for the collection of three chart types included in the Sparklines group of the Insert tab.
The simple line chart is indeed a sparkline whereas the column and win/loss charts are other types of in-cell charts. We’ll start off by looking at the original Sparkline concept. Here we’ve selected the cells in which we want to place our sparklines and then chosen the Line option from the Sparklines group on the Insert tab. We then specify the Data Range that our sparklines are to be based upon (of course, placing our sparklines immediately to the right of a pivot table that might expand over them, or change position, may not be a great idea):
This is what our default sparklines look like:
Excel 2010 includes a decent set of tools for working with our in-cell charts. If we select one of the group of sparklines, the Sparkline Tools conceptual tab will be displayed. In our example we have used the Edit Data option to control how empty cells are dealt with – we have chosen to treat them as zeros. We have then chosen to display markers for the High Point(s) and Low Point(s) of each chart, and selected the Marker Colour:
We now have a reasonable indication of the trend of each salesperson’s sales, but if we compare different salesperson’s chart then, as we can see from the Nancy Davolio and Steven Buchanan lines for example, all the sparklines are plotted using their own automatic scale, so it looks as though Steven’s high point is the same as Nancy’s, whereas her highest sales month was actually over three times as much as Steven’s. In this example, we have placed a second set of sparklines to the right of the originals, but used the Axis option from the Sparkline Tools tab to set the charts to use the Same Vertical Axis Minimum and Maximum values for all charts, giving a more meaningful comparison of the different levels of sales as well as the trend:
You can copy sparklines form one group of cells to another, but you do need to be careful with absolute/relative references - by default the Data Range will be relative.
Good as the editing tools are, it would have been nice to also be able to specify the value at which the horizontal axis crosses. This would have made it really easy to set a ‘target’ value and show sales above target compared to sales below target – maybe using the Column in-cell chart, or even the simplified Win/Loss chart. It’s easy to swap between the different chart types using the ‘Type’ group of the Sparkline Tools tab:
Given the lack of any negative sales values, the Win/Loss chart is, perhaps, of limited use in this case. The marker choices and colours have been retained from the original sparklines charts.
Next week: Slicers - exploring uses of the new data filtering tool.
You might also be interested in
Editor at large
AccountingWEB
John Stokdyk sadly passed away in June 2023. He had been with the site since 1999, rising from news editor to editor in chief, global editor and head of insight. As a roving editor, he investigated the profession's use of technology around the world. He devoted his spare time to technology history and an oddball collection of stringed...
Read more from John Stokdyk