Sparklines are a great way to fit a trend into a small space. Episode recap:
Sparklines are word-sized charts
See the book Beautiful Evidence by Edward Tufte
Use Sparklines to fit data in a small space
Build a second pivot table from the original data
Put the data for the sparklines in the pivot table
Remove the grand totals from the pivot table
Select the numbers for the sparklines
Choose Insert, Sparklines, Column
Select where you want the sparklines to go
Make the column wider to make the sparklines larger
You can also make the row height taller
You can even (gasp) merge cells to make the sparklines wider
Change the color of the high and low point
If you want the sparklines to be scaled together, change the axis minimum and maximum
Sparklines are word-sized charts
See the book Beautiful Evidence by Edward Tufte
Use Sparklines to fit data in a small space
Build a second pivot table from the original data
Put the data for the sparklines in the pivot table
Remove the grand totals from the pivot table
Select the numbers for the sparklines
Choose Insert, Sparklines, Column
Select where you want the sparklines to go
Make the column wider to make the sparklines larger
You can also make the row height taller
You can even (gasp) merge cells to make the sparklines wider
Change the color of the high and low point
If you want the sparklines to be scaled together, change the axis minimum and maximum
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2010 -- Pivot Sparklines So hey, I'm podcasting this entire book.
Click that "i", on the top right hand corner to subscribe to the playlist.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we're talking about, we're continuing to build this dashboard.
So far we've used some Slicers, we've used a pivot chart, and now I want to use a new feature in Excel 2010 called a Sparkline.
A Sparkline, as defined by Professor Edward Tufte in his book Beautiful Evidence, is data intense word size chart.
Alright, so I want to show here, your trend by sales rep here.
Now we're going to go back to our original data.
I'm going to insert a Pivot Table, an existing worksheet and let's go to our dashboard but not here in the dashboard that everyone sees, off to the right where no one will see it.
So we'll just come down below the other one.
Leave some room for some extra growth there and say that our Pivot Table will go there.
All right, now down the left hand side I want to show the sales rep name and across the top I want to show the year and then we sum the value, alright?
So what we have here is a nice trendline.
I don't want the grand total at the end, it will screw things up, so we remove the Grand Total.
Grand Total at the bottom, actually want to get rid of THAT.
So remove THAT Grant Total again.
I'm right clicking there.
Alright, so now we have our Pivot Table but see it's taking up a lot of space and we can put this information in a tiny tiny bit of space.
So I select the data that has my numbers I want to insert.
There's three different types: Lines, Columns and Win/Loss.
Either the line or the column would work here.
I'm going to use a column.
All right, this is, where do you want the Sparklines to be placed?
Well, they're gonna go back up here.
Choose five rows.
Okay, alright, now first off, a couple things with the Sparklines.
They are small, right?
So in order to make them a little bit larger, I like to make the columns, wider.
Alright maybe like that.
From these defaults Sparklines I always go in and say I want the high point and the low point to be different colors.
So we'll make the markers just in general.
We'll have them be a dark gray and then I want to call out the low point.
I'll use orange for that and then the high point, I'll use blue for that.
Alright so now we can see the lowest and the highest value for each of these.
Of course to see the people, just a simple little formula here and it's down in P71.
Copy that down and make this wide enough format column AutoFit.
ALT, O, C and maybe this is a little bit too large right now.
Okay so, now in a very small space, basically two cells, we're showing a 10-year trend and we can see that John Henning, he used to be the superstar and he's falling off.
Linda here has been increasing.
Recently John, for some reason, peaked two years ago and then kind of fell off since then.
All right, so, this gives you a great view, small space, a little bit of formatting.
Now the one thing you have to understand with Sparklines is that these are all scaled independently of each other.
So if someone is selling twice as much the columns are still going to fill the size of the entire, the height, of the entire cell.
If you don't want it like that, then we would come here to Axis and we have to do two things.
Where I say the minimum Axis is the Same for All Spark lines and then Axis, Maximum Axis, is the same for all Sparklines.
All right, so, a little bit different there.
All right, now, this is cool we're continuing to build our dashboard.
We have a pivot chart here.
We have Slicers, but here's the problem.
These Slicers are not controlling THIS Pivot Table.
Well, the data that this Pivot Table is based on.
That will be in tomorrow's episode.
All right, again everything we're talking about in August and September threes podcast is in this book.
Click that link at the top right hand corner to buy the book.
Ok recap for this episode: Sparklines is defined by Edward Tufte, our word size charts.
Check out his book: Beautiful Evidence for lots of examples.
Use spark lines to fit lots of data in a small space.
So I built a second Pivot Table from the original data.
Put the data for the Sparklines in the Pivot Table.
Take out the grand totals from the grant, the Pivot Table.
Select the numbers for the Sparklines.
Insert Sparklines column just like where you want the Sparklines to go because I had five rows here, I had to choose five rows here.
Make the column wider, to make the Sparkline larger or make the road taller or both or, huh?
Merge Cells!
You've never heard me tell you to Merge Cells but it is a way to make the Sparklines wider.
I changed the color of the high and low points you can see where people peaked and then if you want the Sparklines to be scaled together, if you want the Sparklines to be scaled together, change the axis min and max to different settings there.
All right, well hey, I want to thank you for stopping by.
We'll see you next time for another netcast
Click that "i", on the top right hand corner to subscribe to the playlist.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we're talking about, we're continuing to build this dashboard.
So far we've used some Slicers, we've used a pivot chart, and now I want to use a new feature in Excel 2010 called a Sparkline.
A Sparkline, as defined by Professor Edward Tufte in his book Beautiful Evidence, is data intense word size chart.
Alright, so I want to show here, your trend by sales rep here.
Now we're going to go back to our original data.
I'm going to insert a Pivot Table, an existing worksheet and let's go to our dashboard but not here in the dashboard that everyone sees, off to the right where no one will see it.
So we'll just come down below the other one.
Leave some room for some extra growth there and say that our Pivot Table will go there.
All right, now down the left hand side I want to show the sales rep name and across the top I want to show the year and then we sum the value, alright?
So what we have here is a nice trendline.
I don't want the grand total at the end, it will screw things up, so we remove the Grand Total.
Grand Total at the bottom, actually want to get rid of THAT.
So remove THAT Grant Total again.
I'm right clicking there.
Alright, so now we have our Pivot Table but see it's taking up a lot of space and we can put this information in a tiny tiny bit of space.
So I select the data that has my numbers I want to insert.
There's three different types: Lines, Columns and Win/Loss.
Either the line or the column would work here.
I'm going to use a column.
All right, this is, where do you want the Sparklines to be placed?
Well, they're gonna go back up here.
Choose five rows.
Okay, alright, now first off, a couple things with the Sparklines.
They are small, right?
So in order to make them a little bit larger, I like to make the columns, wider.
Alright maybe like that.
From these defaults Sparklines I always go in and say I want the high point and the low point to be different colors.
So we'll make the markers just in general.
We'll have them be a dark gray and then I want to call out the low point.
I'll use orange for that and then the high point, I'll use blue for that.
Alright so now we can see the lowest and the highest value for each of these.
Of course to see the people, just a simple little formula here and it's down in P71.
Copy that down and make this wide enough format column AutoFit.
ALT, O, C and maybe this is a little bit too large right now.
Okay so, now in a very small space, basically two cells, we're showing a 10-year trend and we can see that John Henning, he used to be the superstar and he's falling off.
Linda here has been increasing.
Recently John, for some reason, peaked two years ago and then kind of fell off since then.
All right, so, this gives you a great view, small space, a little bit of formatting.
Now the one thing you have to understand with Sparklines is that these are all scaled independently of each other.
So if someone is selling twice as much the columns are still going to fill the size of the entire, the height, of the entire cell.
If you don't want it like that, then we would come here to Axis and we have to do two things.
Where I say the minimum Axis is the Same for All Spark lines and then Axis, Maximum Axis, is the same for all Sparklines.
All right, so, a little bit different there.
All right, now, this is cool we're continuing to build our dashboard.
We have a pivot chart here.
We have Slicers, but here's the problem.
These Slicers are not controlling THIS Pivot Table.
Well, the data that this Pivot Table is based on.
That will be in tomorrow's episode.
All right, again everything we're talking about in August and September threes podcast is in this book.
Click that link at the top right hand corner to buy the book.
Ok recap for this episode: Sparklines is defined by Edward Tufte, our word size charts.
Check out his book: Beautiful Evidence for lots of examples.
Use spark lines to fit lots of data in a small space.
So I built a second Pivot Table from the original data.
Put the data for the Sparklines in the Pivot Table.
Take out the grand totals from the grant, the Pivot Table.
Select the numbers for the Sparklines.
Insert Sparklines column just like where you want the Sparklines to go because I had five rows here, I had to choose five rows here.
Make the column wider, to make the Sparkline larger or make the road taller or both or, huh?
Merge Cells!
You've never heard me tell you to Merge Cells but it is a way to make the Sparklines wider.
I changed the color of the high and low points you can see where people peaked and then if you want the Sparklines to be scaled together, if you want the Sparklines to be scaled together, change the axis min and max to different settings there.
All right, well hey, I want to thank you for stopping by.
We'll see you next time for another netcast
