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.
Tips
How-to
News
Videos
Stories
In this blog post I’ll walk through formatting sparklines in Excel 2010 using sample sparklines from the book store demo file:
The above sparklines are displaying the net profit by month for the three book stores in 2008. The net profit number alone can tell you which stores are doing well, but the sparklines help identify trends behind the number: Seattle and Houston both dipped in the summer, in general their profits climbed as the year went on, and Richmond didn’t do well until the last month.
Let’s take look at the formatting options for these sparklines. Selecting the sparklines gives you a sparkline contextual tab in the ribbon, similar to selecting a chart or pivot table. Below is a zoomed in picture of the two chunks that have most of the formatting options, show/hide and style:
Sparklines have a gallery similar to charting and tables where you can quickly choose a color scheme. Some of the unique options for sparklines are on the show/hide chunk where you can enable points. Checking a point option will turn on or highlight that point for each sparkline. So if you turn on high points, for a line sparkline you will get a point on each sparkline that is the high point. For column and win/loss the point that is the high point will be formatted differently. If there is a tie for high point, both will be highlighted. Negative points will turn on or highlight the negative points in the sparkline, if we turn them on with these sparklines we get:
The sparklines become more noticeable, emphasizing that Richmond only had one positive month throughout the year. The gallery will also update to reflect the options checked in the show / hide chunk.
You can also choose colors from a color picker for all the points and sparkline color. Next to the gallery are two dropdowns that will fly out color pickers and some other options. For this example I’ll change the sparkline color to a gray and the negative points to a brighter red:
Formatting options are stored as you switch the type of sparkline, so picking a gray column sparkline and switching to a line sparkline will give you a gray sparkline. Also note that the markers option is disabled. This is due to the fact that a column sparkline type is selected, and the markers option only applies to line sparklines. There is also a show axis option in the axis menu that applies to all sparkline types.
You can create all kinds of looks for the sparklines by mixing colors, points, and axis:
It’s important to note that the formatting is group based, and in the example above the sparklines are all in the same group so setting a single color / point option sets it for all of them. If you wanted to make one of the sparklines a different format it would have to be removed from the group first.
For the next post I’ll get into the axis dropdown and talk about the options there. Stay tuned!
Comments: (11) Collapse
Trying to customize the template "revenue forecast" in Excel. The template comes with 4 tabs, one is read me (which provides little assistance), a revenue forecast, calculations, and dashboard. the revenue forecast tab comes with 41 lines, I added another 40, and can't figure out how to manipulate the DSUM to include my new lines of data in the calculation tab. Went to IS Department, they couldn't figure it out. Anyone have an idea, or where is the best place to post this information? Or, know a place I can find the answer?
fjhawley@comcast.net
I love sparklines! Ane I am sure I will use them often, once Excel 2010 is my standard Excel.
Two questions: You mention "THE" book store sample file. Can it be downloaded somewhere?
You talk about a "group" of cells. Is this a new feature? Can you please tell us more about that?
A pity that there's no conditional formatting to help add business context to the sparklines. One more consideration for v2.0 I suppose.
Are you going to explain, at some point, how you add the "target" line I see on your line sparklines?
fjhawley: Load the Name Manager (ctrl+F3), select the named range called "DB", click Edit, and change the last value in the "Refers to:" box from 41 to 81 (or whatever the right row number is for you). In the future, if you need to add more data, select the sheet row that contains the last row of data and perform a row insert. That should automatically adjust the named range for you.
Ute: I'll see if I can post the sample file. You will need Excel 2010 to view the sparklines.
Groups were covered in the previous sparklines post: blogs.msdn.com/.../adding-some-spark-to-your-spreadsheets.aspx
Colin: the "target" line is the "Show Axis" option that was mentioned in the post.
Joseph, thanks for the clarification. I wouldn't have guessed because despite the fact that I've installed the Preview on three separate machines, the axis has yet to show up after I enable the option.
Joseph the "target line" mentioned by Colin refers to say an Average Line or a Quartile Line etc.
I don't think the Axis Option lets you create such line
Sam
Colin: I believe you've stepped on a known bug in the Beta release. If I remember correctly, the Show Axis option currently only works if toggled via the OM.
sam: You are correct. The "target line" you describe is not a feature we have in this release. Interesting idea though. I'll pass it along to the right folks.
The VBA command to enable the Axis to show is:
Range("A3").SparklineGroups(1).Axes.Horizontal.Axis.Color.Color = RGB(0,0,0)
Where "A3" is whatever range your sparkline is in. Naturally you can adjust your RGB values to whatever you like.
I love all of these data visualization options, but unfortunately there's no easy way to display them in Word. I often create mixed documents where I'd like to insert a table from Excel, but the data bars, sparklines, etc. does not carry over. Instead I have to copy and paste as a linked object--an awkward throwback to the days of Win 3.1 OLE. (Word, at least of 2007, has a nasty tendency to resize objects and distort their proportions.)
Andrew has hit the nail on the head - you need to set the colour of the axis. Once it has been set you can turn the display on or off via the GUI or the OM. As far as my testing goes this happily survives changing the type (line, column, win/loss).
I am disappointed that the color schemes in sparklines fail to make good use of Edward Tufte's principle of least effective difference. Tufte's illustrations often show how low color saturation for much of the data, with higher, contrasting saturation for key data points, can help display very large amounts of data. For example, for a line type sparkline, color the line about 60%gray, with the high point red (not fully saturated) and the low point blue. The lack of color on the line makes the points stand out. The different color of high and low makes them easy to distinguish.
In many of the styles, the lines have all sorts of colors, and the high and low points do not have enough difference. In only a couple of the styles are the high and low points colored differently.
I strongly encourage you to put Tufte-esque examples in the style set, and put them first. It may not follow Microsoft standards on the color schemes, but that's the point, no? Yes!
Can we create and store our own styles? With VBA perhaps?
Also, in the column format, the columns run together when the column width is too small. I would like adjustable column widths. Compare, for example, the column implementation in Bissantz Sparkmaker.
Comments: (loading) Collapse