As envisioned by Professor Tufte, sparklines could have a bit of shading to show the acceptable ranges. While this is not necessarily built into Excel 2010, you can achieve the same effect. Episode #1195 show you how
Transcript of the video:
MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
On yesterday I showed you some cool sparkline tricks, I want to show you one more that I think is pretty cool.
We had sparklines yesterday, I’m going to change spark column, I'm going to change these two lines and add markers, so we can see the individual markers now.
One thing that a professor Tufte wrote about in his book “Beautiful Evidence” is he would actually add a bit of shading to the line that would show the acceptable values.
So let's say here that the acceptable tolerance was from 95-105, I want to add some gray shading.
Now Microsoft doesn't let you do this with sparklines necessarily, but we can do it just using some clever Excel here.
So I'm going to take the original data, and I'm going to save it, I'm just going to copy it and paste it, and then up here I'm going to change this data to all be 95, and then this data all be 105.
That's going to be the guidelines for my tolerance, I want to draw shading between those two, and the best way that I found to do this is just to actually change the data right in the sparkline.
So that way I can see it, now I'm going to go back here to Insert, Shapes, and I'm going to add a rectangle, alright.
You know, before I do that, I'm going to increase the zoom here a little bit, so I can actually make this out a little bit better.
OK, so Insert, Shapes, we're going to choose a rectangle, and I'm going to just draw this in.
Initially going all the way across, try and get the bottom at the right spot, and then adjust it, so that way the top is just lining up with that line.
Now, a few tweaks we want to do here, I want to get rid of the outline, so Shape Outline, No Outline, Shape, Fill, I'm going to choose yellow color alright.
And now that I got rid of that outline, I kind of want to tweak it one more time, just to make sure that it's going right through the dots at the top, and right through those dots at the bottom.
OK, now I can hear you say “Well hey, you obscured all the lines, we can't see anything.” Alright, so we have to come in here to format that, I'm going to use the Dialogue Launcher here.
And check this out, Transparency, go up to about 75% or so, and now we can see right through that shading, click Close, alright, so now we've got the shading at the right spot.
Final step is to take the real data that we replaced with that fake data, and put it up here.
And so now we can see that for line 1 they were within tolerance the whole day, line 2 started out within tolerance, but then it just went really bad.
Line 3 was slightly out of tolerance, but then stayed in.
And line 4 – wow, they had one point that was out of tolerance, but clearly something's going wrong there on that.
Adding the shading, I think, is a great improvement.
Again, not my idea, I got this from the “Beautiful Evidence” book.
Although I guess what is my idea is using the Insert Rectangle, and the low transparency to actually bring that home to Excel, and allow us to do that in Excel 2010.
Well hey, I 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.
On yesterday I showed you some cool sparkline tricks, I want to show you one more that I think is pretty cool.
We had sparklines yesterday, I’m going to change spark column, I'm going to change these two lines and add markers, so we can see the individual markers now.
One thing that a professor Tufte wrote about in his book “Beautiful Evidence” is he would actually add a bit of shading to the line that would show the acceptable values.
So let's say here that the acceptable tolerance was from 95-105, I want to add some gray shading.
Now Microsoft doesn't let you do this with sparklines necessarily, but we can do it just using some clever Excel here.
So I'm going to take the original data, and I'm going to save it, I'm just going to copy it and paste it, and then up here I'm going to change this data to all be 95, and then this data all be 105.
That's going to be the guidelines for my tolerance, I want to draw shading between those two, and the best way that I found to do this is just to actually change the data right in the sparkline.
So that way I can see it, now I'm going to go back here to Insert, Shapes, and I'm going to add a rectangle, alright.
You know, before I do that, I'm going to increase the zoom here a little bit, so I can actually make this out a little bit better.
OK, so Insert, Shapes, we're going to choose a rectangle, and I'm going to just draw this in.
Initially going all the way across, try and get the bottom at the right spot, and then adjust it, so that way the top is just lining up with that line.
Now, a few tweaks we want to do here, I want to get rid of the outline, so Shape Outline, No Outline, Shape, Fill, I'm going to choose yellow color alright.
And now that I got rid of that outline, I kind of want to tweak it one more time, just to make sure that it's going right through the dots at the top, and right through those dots at the bottom.
OK, now I can hear you say “Well hey, you obscured all the lines, we can't see anything.” Alright, so we have to come in here to format that, I'm going to use the Dialogue Launcher here.
And check this out, Transparency, go up to about 75% or so, and now we can see right through that shading, click Close, alright, so now we've got the shading at the right spot.
Final step is to take the real data that we replaced with that fake data, and put it up here.
And so now we can see that for line 1 they were within tolerance the whole day, line 2 started out within tolerance, but then it just went really bad.
Line 3 was slightly out of tolerance, but then stayed in.
And line 4 – wow, they had one point that was out of tolerance, but clearly something's going wrong there on that.
Adding the shading, I think, is a great improvement.
Again, not my idea, I got this from the “Beautiful Evidence” book.
Although I guess what is my idea is using the Insert Rectangle, and the low transparency to actually bring that home to Excel, and allow us to do that in Excel 2010.
Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
