In Episode 1194, we take a look at how to add labels to your sparklines.
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel.
Download a new two minute video every workday to learn one of the 377 tips from the book!
...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel.
Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, I've been writing about Excel 2010 a lot, and one of the cool new features in Excel 2010 are the Sparklines.
Now professor Tufte invented sparklines, wrote about them in his book called “Beautiful Evidence”, and sparklines are very small, word-size charts, click OK, alright, so we get some sparklines there.
Now I found that you kind of really have to adjust sparklines a little bit.
The first thing I like to do, is I like to make the sparklines larger, so like a height of around 55 or so, and the other thing we find, is that spark lines each have their own scale.
So here I want to compare these four different manufacturing lines, I really need to make sure that they all have the same scale.
So I go into Sparkline Tools, Design, and then Axis, and I could say Same for All Sparklines for both minimum and maximum, but I'm a bit of a control freak.
So I want to set a custom value for the minimum, I'm going to set that to be 90, and then go back and for the maximum custom value, and let's say a 110.
That way I have absolute control over the min and max, and I can also do this really cool labeling trick.
110, Alt+Enter Alt+Enter Alt+Enter, 90.
Alright, now here's what I'm going to do, I'm going to make that a little bit smaller, so it fits.
I'm going to right-justify, and now I actually have a little label over there that shows me the scale from 90-110.
The other thing I'd like to do, you know what, let's change these to spark columns.
So I'll, come here and say that we’ll make it a column.
I would actually like to put a label along the horizontal axis, now check this out, this is pretty cool.
I'm going to do 7, Alt+Enter, 7 o'clock, 8, Alt+Enter, 9, Alt+Enter, 10, 11, 12, 1, 2, so there's our times, alright, and we'll choose that.
Now I'm going to go into Format Cells, Alignment, we're going to change that to be sideways text, and then horizontal, we’re going to say Center and vertical we're going to say Top.
Alright, now see it's way, way too big, so I'm going to go a lot smaller, and then I'm going to make the columns wider, so that way things pretty much just about line up.
Takes a little bit of tweaking here to get this to work.
7-8-9-10-11-12-1-2, each column now has a number directly underneath it, isn't that cool?
OK so, we now have that data, I need to see what the column width is, 16.71.
We’ll repeat that for the other columns, and then I can copy those labels across, and we now effectively have labels for each individual thing.
Also if there's room, you can actually get rid of these titles up here and move them right into the box.
So line 1, we can say “Hey, we want that top-aligned and center-aligned, make it a little bit smaller” and it'll work.
So you're actually adding labels right to the individual sparklines, I think it makes it much better to have some labels there above and below.
Now Microsoft doesn't do that, but you know, easy enough, and just a little bit of time there to go through and show that, so some cool sparkline tricks.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, I've been writing about Excel 2010 a lot, and one of the cool new features in Excel 2010 are the Sparklines.
Now professor Tufte invented sparklines, wrote about them in his book called “Beautiful Evidence”, and sparklines are very small, word-size charts, click OK, alright, so we get some sparklines there.
Now I found that you kind of really have to adjust sparklines a little bit.
The first thing I like to do, is I like to make the sparklines larger, so like a height of around 55 or so, and the other thing we find, is that spark lines each have their own scale.
So here I want to compare these four different manufacturing lines, I really need to make sure that they all have the same scale.
So I go into Sparkline Tools, Design, and then Axis, and I could say Same for All Sparklines for both minimum and maximum, but I'm a bit of a control freak.
So I want to set a custom value for the minimum, I'm going to set that to be 90, and then go back and for the maximum custom value, and let's say a 110.
That way I have absolute control over the min and max, and I can also do this really cool labeling trick.
110, Alt+Enter Alt+Enter Alt+Enter, 90.
Alright, now here's what I'm going to do, I'm going to make that a little bit smaller, so it fits.
I'm going to right-justify, and now I actually have a little label over there that shows me the scale from 90-110.
The other thing I'd like to do, you know what, let's change these to spark columns.
So I'll, come here and say that we’ll make it a column.
I would actually like to put a label along the horizontal axis, now check this out, this is pretty cool.
I'm going to do 7, Alt+Enter, 7 o'clock, 8, Alt+Enter, 9, Alt+Enter, 10, 11, 12, 1, 2, so there's our times, alright, and we'll choose that.
Now I'm going to go into Format Cells, Alignment, we're going to change that to be sideways text, and then horizontal, we’re going to say Center and vertical we're going to say Top.
Alright, now see it's way, way too big, so I'm going to go a lot smaller, and then I'm going to make the columns wider, so that way things pretty much just about line up.
Takes a little bit of tweaking here to get this to work.
7-8-9-10-11-12-1-2, each column now has a number directly underneath it, isn't that cool?
OK so, we now have that data, I need to see what the column width is, 16.71.
We’ll repeat that for the other columns, and then I can copy those labels across, and we now effectively have labels for each individual thing.
Also if there's room, you can actually get rid of these titles up here and move them right into the box.
So line 1, we can say “Hey, we want that top-aligned and center-aligned, make it a little bit smaller” and it'll work.
So you're actually adding labels right to the individual sparklines, I think it makes it much better to have some labels there above and below.
Now Microsoft doesn't do that, but you know, easy enough, and just a little bit of time there to go through and show that, so some cool sparkline tricks.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
