You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
In this blog post I’ll walk through some of the ways you can use sparklines in Excel 2010 using sample sparklines from the book store demo file:
In the above example we have sales for each year, the cost of sales, and net profit for that year all divided by the category of book. One of the things you can do with sparklines in Excel 2010 is to stick them in cells above or below each other and have the points line up. Doing this will allow you to see trends for multiple fields in a related way.
Here I’ve taken the table, hooked up sparklines to it, and broke it out into a dashboard like sheet. This conveys the trend of sales and profit for each year for past 10 years. The sales are represented by the line sparkline, and the profit is the win / loss sparkline below it. You can see in some places, like romance, that even though sales have fallen, we still turned a profit … and the opposite is true for sports.
Axis options can make a big difference here. Right now the axis for the sparkline group is set to automatic, so each sparkline is showing the trend for its data, if we make the sparklines share a single axis, you can also tell which category has more sales than others:
In this example, mystery has traditionally had a lot more sales than history, but now they are about the same. To create something like this isn’t too hard in Excel:
Here you can see that the line sparklines and win / loss sparklines are just one cell above the other and their points line up. One other thing to note here is that I’ve added conditional formatting after the end of the win / loss sparkline. There are upcoming blog posts on the new conditional formatting features, so more details to come on that. You can vary the design of this a lot, and format the cells around and the sparkline cell itself to show highs and lows:
In the first example above, the number is in the cell with the sparkline. Since sparklines in Excel sit in the cell like data bars and icon sets, numbers can exist with them in the same cell. In the second example they are in the cells next to the sparkline, color coded to match the high and low point … and the last example shows the original table with the sparklines embedded in line with the data.
And on that note I’ll wrap up the sparkline post for today, still a couple more coming this week, stay tuned!
Comments: (10) Collapse
A more excellent example of a useless dashboard I would have been hard pressed to come up with!
Sales up, profits down in some years in some categories; sales down, profits up in other years or other categories.
If the intended audience for these particular lovely sparklines were meant to MANAGE this mythical bookstore, these sparklines provide no useful information. The most obvious question that would occur to me if I were the manager would be whether promotions or 20% off sales were the cause of higher sales with lower profits or vice versa. You're not going to get that out of a sparkline.
So the intent is to show sparklines can provide negligible information elegantly?
Sparklines may be efficient ways of providing simple time series information. If so, the key word is simple. Once you show multiple stacked sparklines, you complicate things. Sparklines may be (almost certainly are) much less efficient that other types of charts for presenting complex information clearly.
Harlan, I had a similar initial reaction while reading this post, but the way to look at this is that the sparklines tell you what's happened but not why. Given the knowledge of the what, you could then dig deeper into the why. For convenience, hyperlinks to more detailed data would have been useful.
One problem is that if you have several sparklines, it would be rather cumbersome to have to analyze them all in the way I describe. Alas, sparklines don't provide sufficient additional information (targets, normal variation range highlighting and so on) to give you a sense of what data needs the most attention.
The up/down arrows don't provide any additional information to the analysis - the sparklines already provide the trend information.
I understand what you two are saying, but I think you have to take a realisitic approach about what the purpose of presenting data this way actually is. If you were actually managing this mythical bookstore you would be looking at a much more granular level than this.
Often I need to recap certain data for those who only want a high level overview of a particular set of information. Given that, I can produce the flat Excel sheet for review, but I would rather be able to use this type of visual. Depending on the the knowledge level of the audience about the underlying drivers of the results, more in-depth analysis might be launched but this type of tool would be helpful showing the "what" rather than the "why."
The purpose of sparklines in this context is to provide a quick visual to a page of black and white text. I don't think this kind of report is detailed enough to make any major conclusions or decisions, but it serves as an overview, so you know where to look for more details.
As a quick follow-up, it should be noted that major sparkline tools (free and commercial) provide the option to add additional context to a sparkline without adding confusion or clutter. The additional context helps you to determine at a glance whether additional digging is required. Even for executive level folks, the additional context reduces the number of (why?) questions they need to ask about the results they're looking at.
I think Sparklines are great at showing trends over time vs. tables which tend to emphasize or are biased towards the most recent info/data.
I think they will be great to someone who already knows the whys but wants to communicate the info in a clean way (or bring context to a table) and is there to provide the answers should management want to know the whys.
As someone who complained about the charting engine before (Excel 2007), I think this is a good step forward.
The charting engine is apparently faster, I hope there will be a post about that and other enhancements.
OK, OK, I may have overreacted, but someone please tell me what business purpose is served by reviewing sales trends uncorrelated with profitability trends? IOW, wouldn't profits be the most important trend line to monitor?
I know the purpose of the blog post is to show how to do certain things with Sparklines, but it helps when the examples are actually relevant. Showing sales in greater detail than profits is just so bassackwards that it deserves comment.
It's also telling that there was no set of Sparklines for books of all types in total. Anyone who thinks different book departments' sales are independent and therefore amenable to separate analysis hasn't gone to a bookstore with their wife and kids.
I guess no one has told the Excel development team that correlation analysis has been one of the most important and most valuable areas of statistical business analysis in the past two decades. Then again, Microsoft doesn't exactly market Excel as a BI tool, so maybe this is just an extremely apt example.
"OK, OK, I may have overreacted, "
LMAO! ROTFL!
"Showing sales in greater detail than profits is just so bassackwards that it deserves comment."
No argument here. I don't think it was a good example of using a win-loss "in-cell" chart. The use of binary indicators (win-loss, up-down, etc.) for measures like revenue and profit is problematic because these indicators provide no context at all to the analysis (e.g. am I up/down a buck or 1M bucks? How is my profit trending with sales? etc)
"I guess no one has told the Excel development team that correlation analysis has been one of the most important and most valuable areas of statistical business analysis in the past two decades."
They have been told - by many experts in the field. They just haven't gotten around to doing anything about it yet.
"Then again, Microsoft doesn't exactly market Excel as a BI tool, so maybe this is just an extremely apt example."
Actually, it's just the opposite. Excel (2010) is being promoted heavily by Microsoft as a front-line BI tool.
Front-line as in over-stuffed grid control used for end user display or as in the part of the analytical toolset where end users can massage, possibly beyond the point of irrelevance, the back-end BI tools' results?
So are Sparkies being played up as BI tools?
Harlan, my remark on Excel as a front-line BI tool was based on a comment made by Joseph in another thread and past statements I've read on the Microsoft BI blog. I sincerely look forward to learning more about Microsoft's positioning of Excel for BI.
Comments: (loading) Collapse