A case study from my PASS webinar - taking Billboard Hot 100 data through Power Pivot and then creating a Win Loss Sparkline.
Table of Contents
0:50 Creating a static lookup table using a pivot table
1:50 Filling Pivot Table blank cells with -1 for Win/Loss functionality
2:45 Converting pivot table to static values
3:15 Data in Power Pivot
3:55 Killing Compact Layout in favor of Tabular Layout
4:10 Adding a decade slicer for filtering
4:20 Preserve column widths in a pivot table
4:40 Formatting a Slicer
5:00 Sorting top values to top of pivot table
5:55 Selecting multiple decades from the slicer
6:10 Filtering to top 10 items in a pivot table
6:40 Which artists had staying power?
7:05 Building the presentation layer above the pivot table
7:40 Building 120 columns of year data using a formula
8:35 Why VLOOKUP would be slow
9:00 Using MATCH instead of VLOOKUP
9:50 Using INDEX, with an explanation of $F4 and B$2 mixed references
11:45 Win/Loss is looking for positive or negative
12:00 Inserting Sparkline
12:15 Small sparkline versus big sparkline
12:55 Dragging slicer to a new position of the worksheet
13:20 Plug for Sirius 40's on 4 Channel
13:35 Why you should add labels to sparklines
13:55 MOD formula to assist in identifying decade locations
14:50 Using a Line from the Insert Shapes
15:20 Making the line a dashed line
15:35 Making copies of the first line using Ctrl+Shift+Drag
16:20 Using Textbox for Decade Labels
17:15 Using fonts smaller than 8 in Excel
18:15 Replacing the temporary MOD formulas
18:30 Changing the color in the sparkline
18:45 Using Title Style
19:00 Elvis Presley discussion
19:10 Adding a Year Slicer from the Lookup table
19:25 Why you should build Slicer from lookup table
20:10 Looking for One-Hit Wonders
20:20 Shaun Cassidy discussion
20:30 Drilling down to see detail on one artist
21:00 Hiding gridlines, formula bar, headings in Excel
Table of Contents
0:50 Creating a static lookup table using a pivot table
1:50 Filling Pivot Table blank cells with -1 for Win/Loss functionality
2:45 Converting pivot table to static values
3:15 Data in Power Pivot
3:55 Killing Compact Layout in favor of Tabular Layout
4:10 Adding a decade slicer for filtering
4:20 Preserve column widths in a pivot table
4:40 Formatting a Slicer
5:00 Sorting top values to top of pivot table
5:55 Selecting multiple decades from the slicer
6:10 Filtering to top 10 items in a pivot table
6:40 Which artists had staying power?
7:05 Building the presentation layer above the pivot table
7:40 Building 120 columns of year data using a formula
8:35 Why VLOOKUP would be slow
9:00 Using MATCH instead of VLOOKUP
9:50 Using INDEX, with an explanation of $F4 and B$2 mixed references
11:45 Win/Loss is looking for positive or negative
12:00 Inserting Sparkline
12:15 Small sparkline versus big sparkline
12:55 Dragging slicer to a new position of the worksheet
13:20 Plug for Sirius 40's on 4 Channel
13:35 Why you should add labels to sparklines
13:55 MOD formula to assist in identifying decade locations
14:50 Using a Line from the Insert Shapes
15:20 Making the line a dashed line
15:35 Making copies of the first line using Ctrl+Shift+Drag
16:20 Using Textbox for Decade Labels
17:15 Using fonts smaller than 8 in Excel
18:15 Replacing the temporary MOD formulas
18:30 Changing the color in the sparkline
18:45 Using Title Style
19:00 Elvis Presley discussion
19:10 Adding a Year Slicer from the Lookup table
19:25 Why you should build Slicer from lookup table
20:10 Looking for One-Hit Wonders
20:20 Shaun Cassidy discussion
20:30 Drilling down to see detail on one artist
21:00 Hiding gridlines, formula bar, headings in Excel
Transcript of the video:
The MrExcel podcast is sponsored by the Power Excel seminar coming to Miami, Florida, Alabama.
MrExcel podcast episode 1930 show 120 years in a win-loss sparkline.
Hey, welcome back to the MrExcel net cast.
I'm Bill Jelen. Today I'm making up for yesterday on October 16th, I was doing a seminar for the past group, a webinar.
And at the end, I showed this great little example in right in the middle, about a few minutes in, I build an index formula with a wrong number of dollar signs and everything just didn't work at that point.
We're running out of time.
So I'm making up for today.
I'm showing that trick again.
What I have here is I have 120 years with a billboard hot 100 data going back to 1890.
I want to create a nice visualization based on a pivot table, but then what we're going to present is not the pivot table at all.
We're going to present a sparkline a win-loss sparkline.
One of those new features was introduced in Excel 2010.
Now the first thing I have to do, I do have this data in power pivot, but at first, I need to create a lookup table that's going to use for the win-loss line.
And that's just going to be a table that is going to be created with the pivot table, but then it doesn't need to stay a pivot table.
So in this particular case, I'm not going to use power pivot.
I'm just going to create a regular old pivot table click.
OK.
I want to put artists down the left-hand side and then going across the top.
I want to have the years.
And then the number of weeks that artist had a song in the chart, and if you had two or three songs on the chart, it would be cumulative.
So, you know, if there was a song that was on the chart for all 52 weeks, and then a second song, I was on the chart for 10 weeks.
That would be 62.
All right.
So as we look here, it doesn't look like there's anything over in our pivot table, you know, 38 special course had no hits back in the 1890s, nor neither did 10,000 maniacs.
You would have to scroll a way to the right in order to see those start to come up with data, but there is data there.
And then we also have all of these blank cells, and I usually make fun of Excel and the blank cells, because I would prefer to have zeros there.
Why don't they just put zeros there?
But in this particular case, it's actually nice that we have to go into pivot table options and specify as a Euro because the win-loss spark line shows the up marker for positive numbers and it needs a negative number to show the down markers.
And this actually works out really, really well.
I'm going to put a minus one in there for empty cells.
And the beautiful thing about that setting is it's just displaying a minus one.
They're not adding into the totals.
So you can imagine, let's say there's a one hit wonder who was on the chart for 20 weeks in 1980.
And then we have 119 other years where they weren't on the chart.
That's 119 minus ones.
And 120, the total, that's still going to be 20.
Those minus ones that are showing up are not actually being added into the total.
Alright, so there's our lookup table.
It's 120 columns wide.
And again, I don't need this to stay as a pivot table.
So I'm simply going to copy and paste as values.
And now we use that pivot table as a means to an end.
Most of the time when I'm creating pivot tables, it's just a way to get a result.
And now I have that result.
I no longer need to this to stay as a pivot table.
I can simply just leave it here as a lookup table.
All right.
So now let's go into power pivot or I have the same data.
And in this case, I only have two tables.
The original data actually had five tables.
It was a lot larger.
I was worried during the webinar that this old, old laptop that I use would start to strain under all of the records.
So I tried to simplify the dataset down.
So that way we wouldn't you know, have the webinar, audio starts to go bad when the computer was giving so many CPU's to power pivot.
So from here, we're going to create a pivot table.
It's okay to go on a new worksheet and click.
Okay.
And again, we want to have artists going down the left-hand side and the number of weeks.
Now, what I want to show here is I want to show the top 10 artists for any particular decade point.
You know, I hate this word row labels here.
I'm going to go back to the design tab and change that to tabular form that where I get an actual heading.
All right.
And then we'll make it a little bit less narrow.
We will analyse insert slicer, by the way, if you're an Excel 2010, it's called options, analyse laser or insert slicer, and it'll be based on decade.
All.
Alright, I'll new slicers up here in the middle of the screen.
Isn't that annoying?
They, they keep changing.
The column was there's a setting to turn that off.
I'm going to right.
Click the pivot table, pivot table options and uncheck autofit column list on update.
Now I can make this however wide I want it to be, and it will stay that wide.
All right.
So our slicer shows up here.
It's always a vertical slicer.
I can make that a little bit better.
Choose the slicer.
Now let's go with seven columns and resize like that.
All right.
So now it's nice and small choose some color.
All right.
Good.
All right.
So this is a list of all of the artists alphabetically.
I want to see the top artists at the top.
Yes.
We could go into this dropdown more sort options.
So they want a sort descending based on some weeks, but starting an Excel 2010, when you just come here and sort the data.
When I click ZtoA, that used to do a one-time sort back in Excel, 2003, but now when you do that, it automatically goes in and sets up the rule that I just talked about.
So it goes into more sort options.
It chooses descending.
It uses some of weeks, but a great shortcut key, just, you know, with a single click of the ZtoA button to set up that rule.
Now here's how this is going to work out right now.
It's showing us all artists from high to low, and you can see if I choose people the 1950s, you're seeing older acts, Elvis Presley, Frank Sinatra, 1960s, the Beatles, 1970s, Elton John Elvis Presley.
Where's Michael Jackson.
He should show up there pretty soon.
There we go, Elton John, and you can even choose a couple of decades.
So if I want to see everything from like the 1970s to the 1990s, you'll get the total of those three right now.
I don't need to show the whole list.
I want to show just the top 10.
So we're going to go to more sort options.
So we're going to go to the value filters and the top 10 filter and say top 10 items.
This is by the way, very amazing dialog box.
You can do top or bottom.
You can ask for the top 80% that you know enough to get you 5,000 chart weeks.
In this case, top ten is elbow defaults.
There are perfect.
All right.
So there is my list of the top 10.
All right.
Even this is a cool chart, but I promised you a, a win-loss sparkline.
And so that's what we're going to do.
And the win-loss sparkline, I don't want to just show that decade.
I want to show all years that where we can see who really had staying power, who were the artists who got on the chart?
You know, they were in the top 10 in that decade, but then manage the stay on the chart for years or decades after that.
And so while this pivot table is going to continue to live down here I'm actually going to build the actual presentation layer way up above this area.
So I'm going to insert a bunch of rows.
I'm going to do all I are there and we'll create a nice little heading up here.
So this is staying power of top artists in the billboard hot 100.
All right.
And what we're going to do is we're going to have a column for artists, and then here column B is going to be where the actual win-loss sparkline is going to go, you know, out to the right.
We're going to have all those years from 1890 going on out.
So we'll do equal 1890 plus one.
And I need to copy that out.
I think I have 122 years worth of data.
So I'm going to do control C and just hold down the shift key and start to hold down the right arrow.
And I'm watching you see that tool tip along the right hand side.
I'm watching until that gets up to, I think it's 121 control D alright, there we go.
Actually, I have one more year than that week and in 2012.
So there is the list of all of the years for the billboard data that I happen to have now, how are we going to figure out whether or not that particular artist is on the chart?
Well, first thing let's pull the artist's data up into our report.
So just a simple little formula there.
And did I get 10 X Springfield?
Perfect.
All right.
How are we going to do this?
Well, one very slow way to do it would be to do a V lookup, right?
This is the classic use for V lookup, but V lookup is a very expensive argument.
You can imagine we're going to have 122 V lookup going across times 10 rows.
So 1,220 V lookup looking into thousands of rows of data that is just going to calculate way, way too slow.
So instead of using V lookup, I'm going to use something called index and match first, the match.
And this is we're going to have a column called where, where it says, where is that artist in the list?
So equal match, go find Prince comma in our lookup table.
It's going to start here in cell A2 go down and, and I'm going to press F4 to put all of the dollar signs in comma zero, zero says we want an exact match.
And so Prince is on row 6,578.
Copy that down.
All right.
So now for every single artist we're going to see where that person is located.
And these are just as slow as a V lookup would be, but you see, I only have 10 of these instead of 1,220 of those.
All right.
So a huge time-saver to use one match.
What we're going to use here instead of the V lookup is a very fast function called index.
I'm going to be very careful when I'm build this because I need to make sure that I get the dollar signs exactly Right.
All right.
So we get to specify an array and then the row number within the array.
Well, the row number within the array is that value that's stored here in column F.
All right.
But I need to lock that down to always point to column app.
So right here, I'm going to press F4 not once, not twice, but three times, three times to lock it down to column F all right.
So that way, as I copy this across, you know, if I'm over here in column L it's still going to reach back to column F but the no dollar before the four is going to allow it to look in row five, row six, row seven, as I copy down.
All right.
And then the other part, the array we need to get just the data for the 1890s.
And I was careful here in the match to start in row two.
So I'm going to do the same thing.
Here we go to the 1890 data and start in row two like that, and I'll press control, shift down arrow to select the whole range.
So B2 to be eight, six, six, or seven, what do we want to freeze here?
Well, if I pressed that for once, it would freeze everything, the column and the row and I need this to be able to move across the column.
So I press that for one more time.
So two F fours freezes just the row, two dates, six, six, seven album, not the column.
All right.
So this is a great little formula here and that it's a mixing, both references, freezing the rows and a reference.
That's freezing the columns.
All right.
So we'll copy that.
I'm going to ride the range here.
Control right arrow, go down, control, shift, left, and control viz that copy that all the way across.
And I need to copy down 10 rows.
So I just double click and it looks at to the left.
All right.
So what we should have is a whole bunch of negative ones, but then out here in the modern era, we have some positive numbers and that's the beautiful thing about the win-loss sparkline it doesn't care what the positive number is.
Any positive number gets the up marker.
Any negative number gets the down.
Marker.
All right.
So we'll select our data and scroll back to the right under insert the win-loss.
We want the location to be these 10 cells right there, click.
Ok.
All right.
So that's all yet, as sparkline, as professor, Tufty defined it as a word sized chart, a word size chart, that's interesting a word size chart, but sometimes, especially when we're trying to show 120 years’ worth of data, I don't think that it's better to be small.
I mean, certainly if I made this column even smaller, it's going to be really hard to tell what's going on there.
All right.
So I'm actually a proponent here of making that really, really large like that.
Okay.
So now we can actually you know, start to see all of the various artists.
Now I need to find my slicer, which is going to be down here and over here, and we'll take that slicer and drag it up.
Just have to get near the edge of the Excel spreadsheet.
And it will scroll for you.
So these are the artists who were at the top in 1980, let's go to the 1940.
Alright.
And there we go to a bunch of people that my dad would've listened to.
It's funny.
I still, my dad is 92.
And when we're in the car, it's forties on four.
So I'm actually probably as familiar with all of these because of the constant playing of a series forties on four, as I am familiar with these that I grew up with.
All right.
So there's our win-loss Parkland now.
I'm also a fan of taking sparklines beyond what Microsoft would do.
I like to label my sparklines.
I like to add more information in there.
And so this next part I admit is rather tedious.
But I think it really adds a lot to the whole meaning of the spark line.
And I'm going to come out here to these beautiful formulas that I have, and I'm going to replace the formulas in the last row.
I want to put a formula here that lets me see when we are at the start of a decade.
All right.
So, in other words, is that year up there in row three?
Is it evenly divisible by 10, so equal the mod of that year, comma 10 is that equal to zero?
And what we're going to see is trues and false.
I actually need it to be ones or zeros.
I'm going to take that whole thing, put it in parentheses and multiply it by one, which converts the trues to ones and the false to zero.
So I'm going to copy that across.
And this is my great little trick here because when I come back into the spark line, you'll see that I have a marker that shows me the start position of every single decade.
All right.
And I use that as I'm creating my labels and lines.
So here on the insert tab, all kinds of different shapes that we can use.
It's amazing, but I just want to SIM simple little line here, so I'll choose the line and I'm going to draw a line right here at the start of the 19 hundred.
All right.
So I'm going to hold down the shift key that forces the line to be drawn straight in.
I'm going to go one line above; cause I'm going to have labels up there in row three.
All right.
So there's my line for whatever reason, the lines always started out as bloom.
They don't have to be blue.
I'm going to go to black.
I'm also going to get a shape, outline and add some dashes in there, dashes.
All Right.
So now we have our dash line at the start of the 19 hundred.
Now that I have that line in and it's a, you know, drawn perfectly straight.
It has the dashes.
I'm just going to copy that.
So I'm holding down the control, key, holding down the control, key and dragging to the right.
Actually, I'm going to hold down control and shift control and shift.
We'll make sure that it doesn't go up or down and drop it right at the beginning of the next decade.
Actually, as I start to look at this, I'm going to have to increase the zoom a little bit.
And get it positioned correctly.
All right, I'm going to keep doing that, but I'm going to pause the video.
No reason to make you sit here and watch me these various items as I create the decade lines.
All right, there we go.
Lines are drawn in.
Now we add need to add some labels for labels.
We can either type the labels up here in row one, but unfortunately, it's all one cell.
I could have gone with merged cells here and used 11 different columns and trying to get everything lined up.
I could switch this to quarrier new, which is the one, you know monospace font, that's on every computer and try and type everything in the right way.
But instead, I'm going to just fall back again here to the drawing toolbar and the text boxes.
So we'll very carefully draw a text box in here above the 1890s.
I want to make sure that a text box doesn't go on top of the, the dash line because it would cover it up.
So we'll put 1890s.
We need to make that font centred and probably a little bit smaller.
You know, in some cases you're going to need to go smaller than what they offer.
So even though this dropdown, usually when it goes down to eight, you're allowed to use this less than icon to make things really small, although that's probably too small.
So right there, we'll go with a 8.1.
Wonder how it will look with the bold.
There we go.
All right.
So we have the first one in and I want to be careful here that doesn't touch the edge of the cell.
All right.
Now that I have that one in again, it's just control drag to make copies and then edit.
And again, making sure that we can still see the dash line behind do we need outlines there?
No, I guess we don't.
I should have done that before I made a copy.
So shape, outline, no outline, shape, fill, no fill.
And that way, even if it does happen to, to copy over.
All right, so you get the general idea there.
I'm going to keep making copies of this.
We'll pause the video.
All right.
See, and then once I have those lines and labels, and then this temporary thing down here, I'm going to go a control, right?
Select the formulas just above control C come down control V.
And so that was just a temporary thing to provide a nice guide to get those lines in hired a few more things to change the sparkline colour.
We go to spark line tools.
The regular colour is just for the up, so that changes all the black things.
And then to change the negative points we'll go with a like a nice Gray there the headline or the title we'll use cell styles and go with title for that.
And let's see how it works.
So we can go, let's say 1950.
And you see who the top 10 artists were Elvis Presley first debuted in.
it looks like 1956 and then was on the chart almost every year up until about 1983 had to come back there and 2004, you know, and it would be, it would be even more interesting to add another slicer four years.
So within 1950, let's narrow it down to the, the 1951 or something like that.
So I go back to the pivot table insert slicer, and I'm going to use, see there's a year there in my fact table, and there's a year there and my lookup table, I'm going to use the one in my lookup table because there's less records here.
The slicer will actually be a little bit faster.
And again, ah, let's change that to be 10 columns.
If we can get to fit straight across maybe 10 columns.
There we go.
And in theory, as we choose a decade, each one of these years will only be a, there are only be 10 of them.
So that should work well, move that decade over there.
All right.
So let's try, let's see if we can find a sum, one hit wonders or something like that.
So there's the 1970s we'll choose 1975.
And we're looking for someone who has just a single dot.
All right.
So here it will keep going.
So right there, Sean Cassidy, Sean Cassidy, you had hits in just two years and then it wasn't on the chart anywhere else during the entire 120-year thing.
Now, one of the downsides of this sparkline based on the pivot table is there's really no good way to drill down now, you and I, because we created this note that the pivot table is still down below, so we can cruise down there, fine, Sean Cassie.
And I go over to the 54 and DoubleClick to create a report.
All right, there we go.
Yeah.
Three songs, 22 weeks with, to do run, run, Hey, Deain for eight weeks and that's rock and roll for 24 weeks.
And we can see the detail behind it would be would certainly be cooler if we could do that in the sparklines, but we can't.
All right.
So we're just about done a couple of things here just to make it look less like Excel, hide the formula bar, hide the headings, hide the grid lines.
Maybe we'll select all cells and apply some sort of a nice light background there.
If we go into Excel options, we can actually get rid of the sheet tabs and the scroll bar to hide those but interesting little analysis.
And while this is theoretically built on power pivot, I mean, it's built on power pivot, but what 80% of the video is talking about just things that we do in this presentation layer after the fact to make things look good.
And in fact, you never even see the pivot table on the screen.
Hi, well, Hey, I want to thank you for stopping by.
We'll see you next time for another net cast, from MrExcel.
MrExcel podcast episode 1930 show 120 years in a win-loss sparkline.
Hey, welcome back to the MrExcel net cast.
I'm Bill Jelen. Today I'm making up for yesterday on October 16th, I was doing a seminar for the past group, a webinar.
And at the end, I showed this great little example in right in the middle, about a few minutes in, I build an index formula with a wrong number of dollar signs and everything just didn't work at that point.
We're running out of time.
So I'm making up for today.
I'm showing that trick again.
What I have here is I have 120 years with a billboard hot 100 data going back to 1890.
I want to create a nice visualization based on a pivot table, but then what we're going to present is not the pivot table at all.
We're going to present a sparkline a win-loss sparkline.
One of those new features was introduced in Excel 2010.
Now the first thing I have to do, I do have this data in power pivot, but at first, I need to create a lookup table that's going to use for the win-loss line.
And that's just going to be a table that is going to be created with the pivot table, but then it doesn't need to stay a pivot table.
So in this particular case, I'm not going to use power pivot.
I'm just going to create a regular old pivot table click.
OK.
I want to put artists down the left-hand side and then going across the top.
I want to have the years.
And then the number of weeks that artist had a song in the chart, and if you had two or three songs on the chart, it would be cumulative.
So, you know, if there was a song that was on the chart for all 52 weeks, and then a second song, I was on the chart for 10 weeks.
That would be 62.
All right.
So as we look here, it doesn't look like there's anything over in our pivot table, you know, 38 special course had no hits back in the 1890s, nor neither did 10,000 maniacs.
You would have to scroll a way to the right in order to see those start to come up with data, but there is data there.
And then we also have all of these blank cells, and I usually make fun of Excel and the blank cells, because I would prefer to have zeros there.
Why don't they just put zeros there?
But in this particular case, it's actually nice that we have to go into pivot table options and specify as a Euro because the win-loss spark line shows the up marker for positive numbers and it needs a negative number to show the down markers.
And this actually works out really, really well.
I'm going to put a minus one in there for empty cells.
And the beautiful thing about that setting is it's just displaying a minus one.
They're not adding into the totals.
So you can imagine, let's say there's a one hit wonder who was on the chart for 20 weeks in 1980.
And then we have 119 other years where they weren't on the chart.
That's 119 minus ones.
And 120, the total, that's still going to be 20.
Those minus ones that are showing up are not actually being added into the total.
Alright, so there's our lookup table.
It's 120 columns wide.
And again, I don't need this to stay as a pivot table.
So I'm simply going to copy and paste as values.
And now we use that pivot table as a means to an end.
Most of the time when I'm creating pivot tables, it's just a way to get a result.
And now I have that result.
I no longer need to this to stay as a pivot table.
I can simply just leave it here as a lookup table.
All right.
So now let's go into power pivot or I have the same data.
And in this case, I only have two tables.
The original data actually had five tables.
It was a lot larger.
I was worried during the webinar that this old, old laptop that I use would start to strain under all of the records.
So I tried to simplify the dataset down.
So that way we wouldn't you know, have the webinar, audio starts to go bad when the computer was giving so many CPU's to power pivot.
So from here, we're going to create a pivot table.
It's okay to go on a new worksheet and click.
Okay.
And again, we want to have artists going down the left-hand side and the number of weeks.
Now, what I want to show here is I want to show the top 10 artists for any particular decade point.
You know, I hate this word row labels here.
I'm going to go back to the design tab and change that to tabular form that where I get an actual heading.
All right.
And then we'll make it a little bit less narrow.
We will analyse insert slicer, by the way, if you're an Excel 2010, it's called options, analyse laser or insert slicer, and it'll be based on decade.
All.
Alright, I'll new slicers up here in the middle of the screen.
Isn't that annoying?
They, they keep changing.
The column was there's a setting to turn that off.
I'm going to right.
Click the pivot table, pivot table options and uncheck autofit column list on update.
Now I can make this however wide I want it to be, and it will stay that wide.
All right.
So our slicer shows up here.
It's always a vertical slicer.
I can make that a little bit better.
Choose the slicer.
Now let's go with seven columns and resize like that.
All right.
So now it's nice and small choose some color.
All right.
Good.
All right.
So this is a list of all of the artists alphabetically.
I want to see the top artists at the top.
Yes.
We could go into this dropdown more sort options.
So they want a sort descending based on some weeks, but starting an Excel 2010, when you just come here and sort the data.
When I click ZtoA, that used to do a one-time sort back in Excel, 2003, but now when you do that, it automatically goes in and sets up the rule that I just talked about.
So it goes into more sort options.
It chooses descending.
It uses some of weeks, but a great shortcut key, just, you know, with a single click of the ZtoA button to set up that rule.
Now here's how this is going to work out right now.
It's showing us all artists from high to low, and you can see if I choose people the 1950s, you're seeing older acts, Elvis Presley, Frank Sinatra, 1960s, the Beatles, 1970s, Elton John Elvis Presley.
Where's Michael Jackson.
He should show up there pretty soon.
There we go, Elton John, and you can even choose a couple of decades.
So if I want to see everything from like the 1970s to the 1990s, you'll get the total of those three right now.
I don't need to show the whole list.
I want to show just the top 10.
So we're going to go to more sort options.
So we're going to go to the value filters and the top 10 filter and say top 10 items.
This is by the way, very amazing dialog box.
You can do top or bottom.
You can ask for the top 80% that you know enough to get you 5,000 chart weeks.
In this case, top ten is elbow defaults.
There are perfect.
All right.
So there is my list of the top 10.
All right.
Even this is a cool chart, but I promised you a, a win-loss sparkline.
And so that's what we're going to do.
And the win-loss sparkline, I don't want to just show that decade.
I want to show all years that where we can see who really had staying power, who were the artists who got on the chart?
You know, they were in the top 10 in that decade, but then manage the stay on the chart for years or decades after that.
And so while this pivot table is going to continue to live down here I'm actually going to build the actual presentation layer way up above this area.
So I'm going to insert a bunch of rows.
I'm going to do all I are there and we'll create a nice little heading up here.
So this is staying power of top artists in the billboard hot 100.
All right.
And what we're going to do is we're going to have a column for artists, and then here column B is going to be where the actual win-loss sparkline is going to go, you know, out to the right.
We're going to have all those years from 1890 going on out.
So we'll do equal 1890 plus one.
And I need to copy that out.
I think I have 122 years worth of data.
So I'm going to do control C and just hold down the shift key and start to hold down the right arrow.
And I'm watching you see that tool tip along the right hand side.
I'm watching until that gets up to, I think it's 121 control D alright, there we go.
Actually, I have one more year than that week and in 2012.
So there is the list of all of the years for the billboard data that I happen to have now, how are we going to figure out whether or not that particular artist is on the chart?
Well, first thing let's pull the artist's data up into our report.
So just a simple little formula there.
And did I get 10 X Springfield?
Perfect.
All right.
How are we going to do this?
Well, one very slow way to do it would be to do a V lookup, right?
This is the classic use for V lookup, but V lookup is a very expensive argument.
You can imagine we're going to have 122 V lookup going across times 10 rows.
So 1,220 V lookup looking into thousands of rows of data that is just going to calculate way, way too slow.
So instead of using V lookup, I'm going to use something called index and match first, the match.
And this is we're going to have a column called where, where it says, where is that artist in the list?
So equal match, go find Prince comma in our lookup table.
It's going to start here in cell A2 go down and, and I'm going to press F4 to put all of the dollar signs in comma zero, zero says we want an exact match.
And so Prince is on row 6,578.
Copy that down.
All right.
So now for every single artist we're going to see where that person is located.
And these are just as slow as a V lookup would be, but you see, I only have 10 of these instead of 1,220 of those.
All right.
So a huge time-saver to use one match.
What we're going to use here instead of the V lookup is a very fast function called index.
I'm going to be very careful when I'm build this because I need to make sure that I get the dollar signs exactly Right.
All right.
So we get to specify an array and then the row number within the array.
Well, the row number within the array is that value that's stored here in column F.
All right.
But I need to lock that down to always point to column app.
So right here, I'm going to press F4 not once, not twice, but three times, three times to lock it down to column F all right.
So that way, as I copy this across, you know, if I'm over here in column L it's still going to reach back to column F but the no dollar before the four is going to allow it to look in row five, row six, row seven, as I copy down.
All right.
And then the other part, the array we need to get just the data for the 1890s.
And I was careful here in the match to start in row two.
So I'm going to do the same thing.
Here we go to the 1890 data and start in row two like that, and I'll press control, shift down arrow to select the whole range.
So B2 to be eight, six, six, or seven, what do we want to freeze here?
Well, if I pressed that for once, it would freeze everything, the column and the row and I need this to be able to move across the column.
So I press that for one more time.
So two F fours freezes just the row, two dates, six, six, seven album, not the column.
All right.
So this is a great little formula here and that it's a mixing, both references, freezing the rows and a reference.
That's freezing the columns.
All right.
So we'll copy that.
I'm going to ride the range here.
Control right arrow, go down, control, shift, left, and control viz that copy that all the way across.
And I need to copy down 10 rows.
So I just double click and it looks at to the left.
All right.
So what we should have is a whole bunch of negative ones, but then out here in the modern era, we have some positive numbers and that's the beautiful thing about the win-loss sparkline it doesn't care what the positive number is.
Any positive number gets the up marker.
Any negative number gets the down.
Marker.
All right.
So we'll select our data and scroll back to the right under insert the win-loss.
We want the location to be these 10 cells right there, click.
Ok.
All right.
So that's all yet, as sparkline, as professor, Tufty defined it as a word sized chart, a word size chart, that's interesting a word size chart, but sometimes, especially when we're trying to show 120 years’ worth of data, I don't think that it's better to be small.
I mean, certainly if I made this column even smaller, it's going to be really hard to tell what's going on there.
All right.
So I'm actually a proponent here of making that really, really large like that.
Okay.
So now we can actually you know, start to see all of the various artists.
Now I need to find my slicer, which is going to be down here and over here, and we'll take that slicer and drag it up.
Just have to get near the edge of the Excel spreadsheet.
And it will scroll for you.
So these are the artists who were at the top in 1980, let's go to the 1940.
Alright.
And there we go to a bunch of people that my dad would've listened to.
It's funny.
I still, my dad is 92.
And when we're in the car, it's forties on four.
So I'm actually probably as familiar with all of these because of the constant playing of a series forties on four, as I am familiar with these that I grew up with.
All right.
So there's our win-loss Parkland now.
I'm also a fan of taking sparklines beyond what Microsoft would do.
I like to label my sparklines.
I like to add more information in there.
And so this next part I admit is rather tedious.
But I think it really adds a lot to the whole meaning of the spark line.
And I'm going to come out here to these beautiful formulas that I have, and I'm going to replace the formulas in the last row.
I want to put a formula here that lets me see when we are at the start of a decade.
All right.
So, in other words, is that year up there in row three?
Is it evenly divisible by 10, so equal the mod of that year, comma 10 is that equal to zero?
And what we're going to see is trues and false.
I actually need it to be ones or zeros.
I'm going to take that whole thing, put it in parentheses and multiply it by one, which converts the trues to ones and the false to zero.
So I'm going to copy that across.
And this is my great little trick here because when I come back into the spark line, you'll see that I have a marker that shows me the start position of every single decade.
All right.
And I use that as I'm creating my labels and lines.
So here on the insert tab, all kinds of different shapes that we can use.
It's amazing, but I just want to SIM simple little line here, so I'll choose the line and I'm going to draw a line right here at the start of the 19 hundred.
All right.
So I'm going to hold down the shift key that forces the line to be drawn straight in.
I'm going to go one line above; cause I'm going to have labels up there in row three.
All right.
So there's my line for whatever reason, the lines always started out as bloom.
They don't have to be blue.
I'm going to go to black.
I'm also going to get a shape, outline and add some dashes in there, dashes.
All Right.
So now we have our dash line at the start of the 19 hundred.
Now that I have that line in and it's a, you know, drawn perfectly straight.
It has the dashes.
I'm just going to copy that.
So I'm holding down the control, key, holding down the control, key and dragging to the right.
Actually, I'm going to hold down control and shift control and shift.
We'll make sure that it doesn't go up or down and drop it right at the beginning of the next decade.
Actually, as I start to look at this, I'm going to have to increase the zoom a little bit.
And get it positioned correctly.
All right, I'm going to keep doing that, but I'm going to pause the video.
No reason to make you sit here and watch me these various items as I create the decade lines.
All right, there we go.
Lines are drawn in.
Now we add need to add some labels for labels.
We can either type the labels up here in row one, but unfortunately, it's all one cell.
I could have gone with merged cells here and used 11 different columns and trying to get everything lined up.
I could switch this to quarrier new, which is the one, you know monospace font, that's on every computer and try and type everything in the right way.
But instead, I'm going to just fall back again here to the drawing toolbar and the text boxes.
So we'll very carefully draw a text box in here above the 1890s.
I want to make sure that a text box doesn't go on top of the, the dash line because it would cover it up.
So we'll put 1890s.
We need to make that font centred and probably a little bit smaller.
You know, in some cases you're going to need to go smaller than what they offer.
So even though this dropdown, usually when it goes down to eight, you're allowed to use this less than icon to make things really small, although that's probably too small.
So right there, we'll go with a 8.1.
Wonder how it will look with the bold.
There we go.
All right.
So we have the first one in and I want to be careful here that doesn't touch the edge of the cell.
All right.
Now that I have that one in again, it's just control drag to make copies and then edit.
And again, making sure that we can still see the dash line behind do we need outlines there?
No, I guess we don't.
I should have done that before I made a copy.
So shape, outline, no outline, shape, fill, no fill.
And that way, even if it does happen to, to copy over.
All right, so you get the general idea there.
I'm going to keep making copies of this.
We'll pause the video.
All right.
See, and then once I have those lines and labels, and then this temporary thing down here, I'm going to go a control, right?
Select the formulas just above control C come down control V.
And so that was just a temporary thing to provide a nice guide to get those lines in hired a few more things to change the sparkline colour.
We go to spark line tools.
The regular colour is just for the up, so that changes all the black things.
And then to change the negative points we'll go with a like a nice Gray there the headline or the title we'll use cell styles and go with title for that.
And let's see how it works.
So we can go, let's say 1950.
And you see who the top 10 artists were Elvis Presley first debuted in.
it looks like 1956 and then was on the chart almost every year up until about 1983 had to come back there and 2004, you know, and it would be, it would be even more interesting to add another slicer four years.
So within 1950, let's narrow it down to the, the 1951 or something like that.
So I go back to the pivot table insert slicer, and I'm going to use, see there's a year there in my fact table, and there's a year there and my lookup table, I'm going to use the one in my lookup table because there's less records here.
The slicer will actually be a little bit faster.
And again, ah, let's change that to be 10 columns.
If we can get to fit straight across maybe 10 columns.
There we go.
And in theory, as we choose a decade, each one of these years will only be a, there are only be 10 of them.
So that should work well, move that decade over there.
All right.
So let's try, let's see if we can find a sum, one hit wonders or something like that.
So there's the 1970s we'll choose 1975.
And we're looking for someone who has just a single dot.
All right.
So here it will keep going.
So right there, Sean Cassidy, Sean Cassidy, you had hits in just two years and then it wasn't on the chart anywhere else during the entire 120-year thing.
Now, one of the downsides of this sparkline based on the pivot table is there's really no good way to drill down now, you and I, because we created this note that the pivot table is still down below, so we can cruise down there, fine, Sean Cassie.
And I go over to the 54 and DoubleClick to create a report.
All right, there we go.
Yeah.
Three songs, 22 weeks with, to do run, run, Hey, Deain for eight weeks and that's rock and roll for 24 weeks.
And we can see the detail behind it would be would certainly be cooler if we could do that in the sparklines, but we can't.
All right.
So we're just about done a couple of things here just to make it look less like Excel, hide the formula bar, hide the headings, hide the grid lines.
Maybe we'll select all cells and apply some sort of a nice light background there.
If we go into Excel options, we can actually get rid of the sheet tabs and the scroll bar to hide those but interesting little analysis.
And while this is theoretically built on power pivot, I mean, it's built on power pivot, but what 80% of the video is talking about just things that we do in this presentation layer after the fact to make things look good.
And in fact, you never even see the pivot table on the screen.
Hi, well, Hey, I want to thank you for stopping by.
We'll see you next time for another net cast, from MrExcel.
