So, here's the file that's up on the course site, and you'll see that each row corresponds to a different observation or different case. And in this case, each observation is a different movie. And we have our categorical variables that we had talked about earlier. We have: Is it an adaptation? Yes or no. Is it based on a screenplay? What kind? Which studio? The genre, the movie rating. We also have information on the gross, how much revenue did it bring in. Well, if we go under the Insert tab and I'm using, for demonstration purposes, I'm using a copy of Excel 2013. So we're using Excel 2013. Your Excel ribbon may look a little bit different depending on the version of Excel that you're using, but for quite a few iterations of Excel, Pivot Tables are located in the same place under the Insert tab and that first option that we have is going to be the Pivot Table. So I'm just gonna click on the Pivot Table option, and what you'll see is, you know, Excel tries to be smart. And if your data is contiguous, that all of the columns that contain data are touching each other and you don't have any blank rows in there, Excel is going to do a reasonable job at guessing what data do you want to create the pivot table based on. In fact, what it did here was it automatically highlighted that table for us. Now, so you can check to see where it says select a table or a range where the radio button is currently selected. You can check there to see, all right, well, did it highlight the appropriate data for us? If it did, you can go ahead and hit OK. If not, what I'm going to do is click on the small icon to the right of that field, and that's gonna allow me the flexibility to choose the data myself. And so in the event that Excel gets it wrong for you, I'm gonna navigate up to the top-left of this sheet and, you know, one way that you can get there relatively quickly would be the Control Home, then I'm gonna hit, I'm gonna hold down Shift, and I'm gonna try to navigate to the lower right corner of the data. Right? And so, if I hold down the Shift button while I'm hitting the Control and End, that's gonna take me down to the lower right corner. So these are just some shortcuts as far, rather than having to use the arrow keys myself or type anything in, Control Home will take you to the upper left of a contiguous block of data; Control End will take you to the lower right; and if you're holding down your Shift key while you do that, it's going to highlight all of those regions. Of course, you could do this with a mouse or trackpad, though you're gonna have to be careful in terms of holding things down and highlighting the proper region. So, once I've highlighted that region, I'm just gonna hit Enter and it's gonna bring me back to the dialog box to create the Pivot Table. I'm gonna leave the default option set as where do I want this Pivot Table to be placed. I'm gonna have Excel create a new worksheet for me. All right. And that's all we've done now as far as creating that Pivot Table. So you'll notice now, in columns A, B, and C, you see the Pivot Table builder, so it's listed as PivotTable1. And then, if you've done that on the far-right of your screen, you're gonna see where we have. So, if we look to the left of the screen, we see our Pivot Table in columns A, B, and C. If I click within that Pivot Table builder, it's gonna pull up for me on the right of the screen the different fields or the different columns in the data that we had. It's also going to have rows and columns for me, where I can now create the Pivot Tables that I'm actually interested in. And so, if we want to produce one of the tables that we've looked at already, let's say we want to focus on studios. So I'm gonna click and hold, and I'm gonna drag studios into the rows. And notice now, it's more than the four that we had been looking at because there were many more studios in the data. If I only want to focus on a few of those, what I'm gonna do is, where we have our row labels listed in cell A3, I'm gonna click on that down arrow. And that down arrow is going to allow me to apply filters. So right now, all studios are selected. I'm going to uncheck that select all option. I'm just gonna check off a couple of them, so I'll check off 20th Century Fox. I'll check off Buena Vista. Let's check off DreamWorks, and we'll throw one more in. So, since we're going to give you a different set of studios than we'd looked up before, the numbers are gonna be a little different from the tables we've just seen. But now, here are four studios. So we see I filtered only to look at those four studios , so if those are the four that are of interest to me. And now, you know, let's look at, let's say, genre. You know we've looked at movie ratings. What about genres? Coming from the studios, I'm gonna click and hold genre, drag it down to that column field in the Pivot Table builder, and now you'll see that the pivot table has expanded, it's now populated with the different genres that are present in our data. And what we now have to do is fill in this values field. Right? So what we'd looked at previously for values were just counts or percentages. We can also use that value field to pull in additional information. So let's say I want to pull in dollar amounts; I want to pull in revenue figures. All right. So I clicked and dragged that gross column down to that values field, and you'll see, you know, on our screen, it's a little condensed but if I hover over that label, it says count of gross. So this is not saying that the Pivot Table is reporting the dollar amount, it's reporting the number of observations that meet a particular criteria. So, to give you an example, what are the criteria? Well, 20th Century Fox as the studio, and action as the genre, there are 13 observations in our data that correspond to a 20th Century Fox movie and the action genre. DreamWorks, action movies, there are five observations in our data. So whenever the value variable says count, it's not doing anything with the specific value of that variable. It's just counting up how many observation meet a particular criteria. All right. So again, we can look at how many movies from a studio, in this case, come from a particular genre. So these four studios made 279 movies. Action movies make up 34 of those. 20th Century Fox produced 81 of those. What if I want to look at the percentage? Well, instead of just reporting that raw number, let's take a look if I click on this down arrow, I want to look at the value field settings. And this is where we can change the values that are reported. So the way that the data is currently being summarized is by taking the count. We're gonna come back here and change that in a little bit because we want to take a look at how much money these movies made. But how is that value reported? Well, let's click on Show Value As, and right now it says there's no calculation performed. But instead of just reporting the number or the count of observations, if I do a percentage of the Grand Total, in this case, all of these values will be divided by the 279. All right, so each cell is going to correspond to a percentage of that 279. So let's take a look at that first. All right. So just like we looked at previously with regards to studios and ratings, now we're looking at this with regards to studios and the genres. The movies that are being tabulated in this table make up 100 percent: 29 percent coming from 20th Century Fox, 12.2 percent coming from action movies, 30.8 percent coming from comedies. Now this is saying that it's the percentage of all movies that are being looked at in this table. What if I wanted this broken down by studio? What kind of movies do the different studios produce? Well, let's go back into the value field settings, Show Value As, and instead of percentage of the Grand Total, let's look at it as a percentage of the row total. And so what this is going to do is it's gonna take all of the observations in a given row, add them up, and say OK, well, that's how many movies came from a particular studio. I'm gonna divide each observation by the corresponding row total. So now, you'll see that in the column K where it says Grand Total, everything's 100 percent, right? And let's focus on row five. Let's focus on 20th Century Fox for a second. This is the breakdown of movies released by 20th Century Fox. 16 percent of 20th Century Fox movies were actions. About 15 percent were labeled as adventure. Close to 30 percent labeled in the comedy genre. In the romantic comedy genre, about 11 percent. In the thriller/suspense genre, about 14 percent. All right. Well we can use these figures to see: are these studios producing similar distributions of movies? Are some studios focusing on some movies? Are some studios focusing on other types of movies? So looking at 20th Century Fox, I might say, well, how does the movies released by that studio compare to Universal? Do we see places where there are big differences? They both seem to be releasing about 11 percent in the romantic comedy genre. The one aberration across these four studios seems to be Buena Vista, not too many romantic comedies coming from Buena Vista. Yeah. If we look at, and if I'm focusing on 20th Century and Universal, similar numbers for action, little bit of a difference in adventure, both making around 25, 30 percent in terms of comedy. Universal seems to make a lot more genres, more than 20th Century Fox, in fact, more than any of these studios that we're looking at in this particular table. All right. So, we can create the counts, we can create percentages, whether it's based on the Grand Total or the row total or the column total. We can also use Pivot Tables to summarize the data, right? So we wanted to look at revenue numbers. Well, let's go back into that value field setting, and on that first tab, Summarize Values By, let's take a look not at the count, not at how many observations there are, but for those movies that fall into a particular studio and genre classification, what's the average gross? And for the Show Value As, we're gonna have no calculation performed because we just want to look at the average gross for movies from a particular studio of a particular genre. And we may want to format these so that it's a little bit easier recognizing that we're looking at dollar amounts, so we might want to highlight them. Click on the dollar formatting. I'm gonna expand this table so we can actually see what these numbers are, and to do that, you can hover your mouse between any two columns until you get that vertical slash and the arrows. Double-click, that'll expand the width of the columns so that we can see those values. All right. Now this is giving us again, it's giving us the average. So, action movies from 20th Century Fox brought in, on average, 89 million dollars. Adventure movies from Fox brought in, on average, 133 million dollars. What this is not taking into account for us is how much variation were there from movie to movie. You know, one blockbuster movie that did exceptionally well could make up for movies that had mediocre performance. So that's why we don't want to just focus on averages; we might also want to look at standard deviations or measures of dispersion.
