So, here is 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, it's been located in, 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 going to click on the pivot table option, and what you'll see is 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's 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 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 going to allow me the flexibility to choose the data myself. And so in the event that Excel gets it wrong for you, I'm going to navigate up to the top left of the sheet,and one way that you can get there relatively quickly would be the Control, Home. Now I'm going to hit, I'm going to hold down Shift, and I'm going to try to navigate to the lower right corner of the data. And so if I hold down the Shift button while I'm hitting the Control and End, that's going to 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 continuous 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 track pad, though you're going to 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 going to hit Enter, and it's going to bring me back to the Dialogue box to create the pivot table. I'm going to leave the default option set as, Where Do I Want This Pivot Table To Be Placed. I'm going to 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, see the Pivot Table Builder, so it's listed as Pivot Table 1. And then if you've done that on the far right of your screen, you're going to 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 going to 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 going to click and hold, and I'm going to 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 going to do is where we have our row labels listed, in cell A3, I'm going to 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 going to check a couple of them, so I'll check 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 looked at before, the numbers are going to 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, let's look at, let's say genre. And we've looked at movie ratings, what about genres coming from these studios? I'm going to 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. So what we've looked up 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'm going to, I clicked and dragged that Gross column down to that Values field. And you'll see on our screen it's a little condensed but if I hover 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 5 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 observations 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 4 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 going to come back here and change in 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 my, all of these values will be divided by the 279, 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%. 29% coming from 20th Century Fox. 12.2% coming from action movies. 30.8% 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 going to take all of the observations in a given row, add them up, and say okay well that's how many movies came from a particular studio. I'm going to 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 a 100%. And let's focus on row 5. Let's focus on 20th Century Fox for a second. This is the breakdown of movies released by 20th Century Fox. 16% of 20th Century Fox movies were Actions. About 15% were labeled as Adventure. Close to 30% labeled in the Comedy Genre. In the Romantic Comedy Genre, about 11%. In the Thriller Suspense Genre, about 14%, 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 seemed to be releasing about 11% in the Romantic Comedy Genre. The one aberration, of course, these four studios seems to be Buena Vista. Not too many romantic comedies coming from Buena Vista. If we look at, and if I'm focusing on 20th Century and Universal, similar numbers for Action. A little bit of a difference in Adventure. Both making around 25-30% 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. So we can create the accounts. 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. ALl 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 going to 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 going to 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. 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? 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.
