Sparklines in Excel

I'm very happy to see the inclusion of sparklines in Excel 2010.  Question: if you have a pivot table showing aggregate data, is there any way to automatically display a sparkline in each cell of the pivot table to give the gist of the summary cells?  As far as I can tell, right now, I'd have to go to each cell, drill-down, choose insert sparkline, pick the drilled-down data and then place the sparkline in the cell in the pivot table.  It would be great when creating a pivot table if I could see both the aggregate values and a sparkline giving me the context behind themm.  Any advice greatly appreciated.

Thanks,

Rhett

@T. Valko: en.wikipedia.org/.../Biff_(disambiguation)

Great to see all this buzz about coming Sparklines.

I agree w/ Jon, it's a good start, but when it comes to minimalist tools, every little detail counts or you you might end up with something just ... useless.

Variance chart (--> Horizontal bar properly implemented...) Bullet chart and especially Scales are a must for any decent dashboard and should be included from the beginning.

For those dealing with large series, a Boxplot in a cell is extremely powerful. Maybe for a next step though.

Jon, is there a way for me to test XL 2010, and give my feedback on the new Sparklines.

I experimented a lot and got a lot of feedback with my buggy version, and I think it would be worth sharing it with the Excel Team.

Sam -

The charts got the double click because we yelled and screamed and whined and cried about losing this feature in charts, and they caved. Actually, I think it was more like, yeah, that was a dumb decision. I don't think anyone griped so much about shapes, and anyway, shapes are pert of Office, while charts are part of Excel, and Office are the <a href="businesstoolsblog.com/.../">evil easter bunnies</a> who brought us The Ribbon.

Rhett -

Hey that's a good idea, and no, I don't think this would be possible. Sparklines need a formula, and you can't add your own content to a pivot table. But it would be a great feature to add to pivot tables.

I’ll pick at some of the comments above:

1-Nice start... long way to go though.

Sam: There are definitely more enhancements we want to do, you and some other folks call out a lot of them in the comments here.

2: Win-loss - Is there a reason why draws aren't considered (win-loss-draw)? Draws are often indicated by the absence of a bar.

Sam: Yes, the Win/Loss works like a 100% stacked column, so there are draws.

3: Solid fill for "Data Bars" and Zero Length Bar for Zero Values make Data bars usable and becomes a Sparkline

Sam: Not sure I follow how it’s a sparkline?  A vertical one across many cells?

4: If you have a pivot table showing aggregate data, is there any way to automatically display a sparkline in each cell of the pivot table to give the gist of the summary cells?

Sam:  Not automatically :-/  this is something we really want to add.

"It would be great when creating a pivot table if I could see both the aggregate values and a sparkline giving me the context behind themm.  Any advice greatly appreciated."

"Sam:  Not automatically :-/  this is something we really want to add." ...one of these days.

If you really need this feature in your lifetime, then you can try XLCubed.

I just saw a live demo of Sparklines in a room with 12K ppl...and it got major applause. pretty cool stuff. go team excel!

Just a quick comment regarding terminology. The term "sparkline" is being used too liberally in this discussion. Tufte didn't invent small graphs, he invented a particular type of small, high-resolution graph that displays time-series values without axis lines or scales, called a "sparkline." As he states in "Beautiful Evidence": "Small graphical images have collaborated with text and tables for centuries." A bullet graph is not a sparkline, nor are data bars. Placing a graphic in an spreadsheet cell does not make it a sparkline. You won't find any mention of spreadsheet cells in Tufte's description of sparkline (nor any interest in spreadsheet software whatsoever). To avoid confusion, which is easily created and easily spread, I recommend we use our terms more precisely.

Thanks,

Steve

Stephen, good point. What's confusing is that the term sparkline is being used for stuff that aren't lines at all (e.g. Column Sparkline). The free tool "Sparklines for Excel" and Bissantz SparkMaker are guilty of the same thing . BonaVista uses the term "MicroCharts," where the Sparkline is one of the microcharts. ConnectCode refers to their SparkCode Professional software as Sparkline software but they don't describe all the charts they produce as Sparklines. ConnectCode also has a free version called TinyGraphs, where the Sparkline is described as one of the graphs.

The moral of the terminology issue you've raised suggests that the Ribbon group should be renamed to something generic like "In-Cell Charts," where the Line option just happens to approximate a Sparkline.

I think the term "in-cell charts" is a bit awkward, and it might be confused with the retro all-text spreadsheet charts built up with characters using REPT().

What's wrong with "Sparkcharts"? Sparklines and sparkbars are subsets of sparkcharts, and in fact the W-L charts are subsets of sparkbars.

It's doubtful that "in-cell charts" could be confused with the REPT() stuff. First of all, REPT() for charting purposes is not a function that's obvious to a majority of users, and secondly, there's never been any official terminology or feature named "in-cell charts" in Excel - so at the end of the day, who's being confused?

For the 99% of users that have never even been exposed to to the term "sparkline," what meaning does "sparkchart" convey? For these folks, "In-cell charts" is descriptive, and straight to the point. However, given that the feature is being hyped so much, the marketing folks at Microsoft would probably prefer a fancier name.

I came here to add the comment that the In-Cell line charts (what you call sparklines) needed to have an option for a "normal" or target range (e.g. +/- 3 sigma).  I see now that others realize the same thing.

I also want to declare strong agreement with Colin's first comments on bullet charts and box plots at the very top.

Part of my job is creating effective dashboards for a Fortune 500 company, and Excel Services has been my tool of choice for over a year now.  But there is still much to be desired in the way of charting and visualization.

Will sparklines be supported in Excel Services 2010?

Jay: Yes, Excel Services 2010 will be able to render sparklines created in Excel 2010.

At first I am sorry if my following comment might not relate to this post. The reason is that I don't see any post about using Edit Data tool that supports users to manipulate sparkline with empty cells. My problem is that when I set type of a sparkline as Column or Win/Los,  "Show empty cells as gaps" and "Show  empty cells as zero" options both make non-effect.

I ask because it is noticable that the option "Connect data points with line" is disable for Column and Win/Los sparkline type. It means the two options that enable should support something.

Someone, please help!