 Hi, this is Dr. Don, and I'm back with part three of this series on Excel pivot tables. In the last video, we cleaned up our data and created this blank pivot table. Recall in the first video in part one, I said you could use a pivot table to select records for analysis, such as you need to do when you run hypothesis tests. But here we're going to build a report. So we begin by identifying the variables or fields we want in the report. This report is about the average salary in each of the offices for four specific departments. And we need to break down the salaries by gender, but we also need to be able to select only full-time employees to include in the report. Pivot tables make this relatively easy because you can just drag and drop and see what the report looks like as you go. And you not have to worry about messing up your original data. Let's start with the offices. I'm going to go up here in the pivot table fields area. I'm going to find offices, which is cities actually, highlight that with a left click, hold down the left mouse button, and drag that field, that variable down into the rows area. And now I look over here in my report area and you can see that's headed in the right direction. I have each of the offices listed there in a column. So now I want to build out the rest of my report, and I want to get salaries in here. Well, how do I get salaries in the body of my table? I'm going to go over here and I'm going to find the salary variable again, click it to select it, hold down the left mouse button. I'm going to drag that into the columns and let go. And whoops, that's not going to work. It's showing each of the salaries for these 9,000 employees across the top. So let's try something else. We're going to take that variable and we're going to drag it down into the summation values area and let it drop there. And now we see that we have the sum of the current annual salaries. When you're building a pivot table, Excel will look at your variable and determine whether it's a categorical, a label, a name, or like gender, or a quantitative variable. If it's quantitative, it will try to perform an operation and it defaults to the sum. Now we change that by clicking on the value in the table to select it and then right click, open up this dialogue and look for value field settings. And you can see that there's the default, which is the sum. We want average. Now there are a number of things you can use this value field settings for. I'll show you another one in just a second. Right now I'm going to click average and click okay. And now we've got the average salaries in these offices. But that doesn't look like dollars. That looks like a lot of decimals there. So I want to format that. Now when you're working with pivot tables, you don't want to use the formatting up here in the number area because every time you update your pivot table, you'll lose that formatting. What you need to do is again, click to select a cell, right click, bring up the value field settings. And now I'm going to go down to number format. And I want to click currency to get this into American dollars and click okay and then click okay again. And now my averages are in dollars, which is what I wanted. Next I want to show the genders, male and female. So I'm going to go over here into gender and I've got a choice there. I can put it in as a row and have it shown that way. But I really like having it shown the other way so I can put my departments in the column. So I'm going to go over here, I'm sorry, I already rose into the column and put gender there. And now I've got male and female gender here in this particular table. And very briefly I want to point out to you something that you should be aware of. When you're building a report, you can use filters to filter your data. For example, if I wanted to filter the offices, I could drag these cities up here into the filters. But that takes away the cities down here in my table. You can't have a variable both as a filter and as a row, as a filter, as in a column. So you have another option fortunately. If you look here in the table, you can see column labels, if I click that little funnel, it will show me that I've actually got three types of the gender variable. I've got selected for the male and female. There's another there. I'm going to click that blank and open that. And it shows us that we've got some records that don't have a gender indicated. And that's normal. When you have a lot of records, in this case again over 9,000, it's not unusual for whoever key is a daddy in to forget the key a cell. So we're going to leave the ones that don't have an indicated gender out of our analysis. But you should be aware that I'm going to go back up here to my column labels. And I'm going to uncheck blank, just as I just showed the male and female salaries. Now I need to include the counts. Now how do I get the counts in this table? The way I like to do it, I'm going to use something that is unique. Now the names you would think would be unique, but there may be two employees with the exact same name, but there won't be employees with the same employee ID. So I'm going to select that and drag it down into my values field area. Now you can see that it's given me a column called the sum of employee ID. Now the reason it's labeled that as sum is because the ID is a number and it's trying to count those. I'm sorry, to add those up. We're going to change that. I'm going to click there, bring up the value till settings, and I'm going to change it to count and click OK. Now I've got the headcounts for each office for males and females. You can see we've got the 9,200 plus records minus the 30 or so that are blank of gender. We need departments. So I'm going to go back up here and click on department and drag it down into my table under the city. And now we've got the cities and the departments organized that way. And you can play it around with that. I'm going to drag city below department. Now you can see we've got an arrange by departments by city. I prefer the first way, so I'm going to reverse that again to put city above that. We need to filter out those offices that we don't want, but how do we do that? Remember, we can't have the variable both in a row or column and a filter area. So what we need to do is recognize, again, that we've got these row labels here. And I'm going to click in this dropdown, giving the default of the offices. I'm going to click cancel that. But if I click down here in a department and then go to the row labels, now it's got all of the departments there and I can filter out the ones I want. I'm going to deselect the select all. And I'm going to go down here. I need administration. I need controller. I need finance. And I need finance, HR. I've got the four and click OK. Now I've just got those four departments that I need. And now I've got pretty much everything I need for my table to present to my boss. Except remember, she wanted to only have the full-time employees. Now when I get that, I'm going to use the filter this time. That is the assignment category. I'm going to drag it down here as a filter. And you can see it's got all. If I click the dropdown, there's regular and part time. She wanted regular. I'm going to select that so it will filter out the records. And you can see that we've got 1,917 people in these offices in the departments that we're interested in. We've got the salaries across here and the headcounts as well. And that's pretty much the report we need. All we've got to do now is to format it to make it look the way we want. And I'll show you that in the next video.