Compare Multiple Lists with a Pivot Table

welcome to Excel campus my name is John and in this video I'm going to show you how to compare lists of names with a pivot table so in this example we have lists from the past three years of names of people that volunteered in an event and we basically want to run a report to see who volunteered over the last three years which of these people volunteered in the event all three years which these people are new to the event this year who volunteered last year but not this year so we basically want to create a report that answers all these questions and we can do this with this pivot table type report that's displayed here and this basically is just showing a one in the columns where this particular person volunteered each year so in this video I'm going to show you how to create this report and it's very simple and easy to create with a pivot table so this workbook contains three sheets and each of these sheets contains the names of the volunteers for the last three years and by the way you can download this workbook to follow along just click the link below the video to download this workbook so what I'm the first step is we need to take each of these lists and copy them into one long list that will be the source data for our pivot table so to do that the first thing I'm going to do is just make a copy of this worksheet and to do that I'm just going to left-click and hold on the worksheet down here and I'm going to drag it over to the right and then I'm going to hold down the ctrl key on the keyboard and you'll see a little plus symbol that appears in the tab icon there and I'm just going to now let go of the left left mouse button and that will create a copy of that worksheet so that's just a quick simple way to create a copy of a worksheet just ctrl key and then left click and drag so now I'm going to use this as my starting point and we're going to call this sheet the data sheet so I'll rename that and we already have our 2012 list right here so the first thing I'm going to do in column B is add a new column of data and I'm going to label it year and then here in column B we're going to add the year 2012 so I'm going to type 2012 here and column B and another quick little shortcut is you see that I already selected the cells here in column B that I want to place the let the number 2012 in so if I hold the ctrl key and press the Enter key on the keyboard that will enter that 2012 in each of the cells in the selected range there so that's just a quick little shortcut ctrl enter when you're entering a formula or values and that will copy it down to all the cells that are selected so now I'm going to go over to the 2013 sheet and again I'm going to copy this 2013 data and use control C on the keyboard go back to my data sheet and I'll paste it right here directly under the 2012 data and again I need to label these 2013 and another shortcut I can use is ctrl shift end on the keyboard ctrl shift end on the keyboard that will select all the cells from the selected cell down to the bottom and now I'm going to enter 2013 oops 2013 there and ctrl enter and that'll enter 2013 and all these rows here so now we're basically identifying each of these rows in our long list with the year of where that person volunteered so this is just saying that these people volunteered in 2013 these people up here volunteered in 2012 and now we just need to enter the 2014 data so I'm going to select these cells and a keyboard shortcut for that as if I select the first cell up at the top and use control shift down arrow that'll select all the cells there in that range and then again I'm going to go over to my data sheet go to the bottom here and just paste those with control V and same thing here I'm going to use ctrl shift end and then enter 2014 and ctrl enter and that'll enter all these rows with the 2014 to identify all these names as being people that volunteered in 2014 so now we have this long list of data that contains all of our names for all three years and another column that identifies which year those people volunteered in and this will become the source data for our pivot table so now I'm going to enter or create a pivot table in this workbook and to do that I just go up to the insert tab on the ribbon up here and click the pivot table button and you don't have to select all the the entire range before you create that pivot table if you just select one cell inside your data range Excel is pretty good at figuring out which data range you're talking about and wanting to create a pivot table on so it it automatically figures that out and selects the range for you so you can see that the range has been selected and then all we need to do is press the ok button here and that will create a new sheet with the pivot table in it and I'm going to move this pivot table fields list over here a little bit so we can see what we're doing get it all in one screen so now we just want to create our pivot table and the first thing I'm going to do is take the name field here and drag it into the rows area of the pivot table and that will list all the names from our source data in the pivot table here in the rows area and you'll notice this is a consolidated list and what that means is that if there was a name that's listed more than once in our source data well the pivot table will automatically consolidate that for us and only show the unique values within the list so if we go back over to the data sheet and we can see for example here I can see that this a sure maze is listed twice here in the datasheet well back in the pivot table that name will only be listed once because the pivot table automatically consolidates that data for us so now the next step is we're going to take the year field and drag it into the columns area of the pivot table so now I have my years right here in the columns area of the pivot table there's 2012 2013 2014 and you can see we're starting to build a grid for this report so the next step is to take the name field again and I'm going to drag that into the values area of the pivot table and this is going to create a calculation of the count of names so basically this is counting how many names are appear in each of the years here so if we look at for example a sure maze here this is just saying in this cell here that his name appears once in 2012 and his name also appears once in 2013 and once in 2014 and that's in our data list so this is just a report that can show you exactly where these people have volunteered which year they volunteered in and it'll allow you to answer some of those questions we started out with so now we could easily see that if there's a three in the grand total column which just sums up all these three numbers here that means that person has volunteered all three years and we could also figure out who volunteered last year but hasn't volunteered this year so we can easily see here that bee that Colton volunteered last year but since there's a blank for 2014 that means that he did not volunteer this year or not yet at least so maybe we want to call him up and see if he can make it this year so this pivot table will basically allow us to answer some business related questions about our event I hope you found this useful please leave a comment below the video with any questions and also subscribe to my free email newsletter and I'll also send you a free bonus so lots of cool free stuff check it out and I will see you soon thanks for watching

Channel: Excel Campus - Jon

Views: 118,810

Rating: 4.8562446 out of 5

Keywords: Excel, Pivot Table

Id: Qx5ZyYP9BQY

Length: 8min 14sec (494 seconds)

Published: Wed Jul 09 2014