And we'll dive right in. Welcome back. So I did open up my practice file here and we're taking a look at a data set. So here's just some company payroll. Here we have some headers with the employee ID number. The employee we have several divisions California new Jersey, Georgia and Washington. We have a few departments as well sales development staff and research..
We have the date of hire for each employee. The amount of hours that they're logging per pay period, their hourly rate. And a formula over here to calculate the gross pay. We also have a status column here indicating whether they are full time or part time. By using the if function. So if I were to present this data to you and ask you to present this data in about 30 minutes or so to our team, well, naturally you would first kind of take a look at the data set. It's always a good idea to do that. We can see that we have 94 records here..
And we need to anticipate some questions that may be asked. So some of the questions that may be asked is well what is the sum of the gross pay. What's the gross pay by department. Maybe gross pay by division. And something like what is the average hourly rate? Well, as you can see, there's a lot of other questions that we can ask here. And so if we were to try and answer these questions, what are some of the things that we can do. Well we might use some sorting. We may do some filtering. We may need to use some functions right. Like the sum function, the average function..
We may need to have some basic calculations in here as well that we can use. So there's a lot of different things that we would need to do here. We would need to go ahead and just kind of move things around and kind of reshape our data here as well. So for the sum of gross pay, we could, you know, enter the sum function and then go ahead and grab all the gross pay. But think about this. This is not something that we want to be doing while we're presenting this data. We want this data to be, you know, readily available with just a simple click of a button..
Well, let's take a look at a fully functional pivot table, and let's see what we can gather from this. Here. So here's a fully functional pivot table. This is based on our data set. And as you can see the pivot table is in a condensed form. And if this is your first time looking at a pivot table this may be overwhelming. So I'll go ahead and annotate some of the things here. We can see that we have some totals down here in the bottom. So this is the total for California, new Jersey, Georgia and Washington..
Over here is the grand total. So this is the sum of all of the gross pay for all of our divisions, all of our departments there as well. So if I were to ask you what what's the sum of the gross pay for Washington, we can say the sum of the gross pay for Washington is 18,155, 52. Well, what about something else? What if we wanted to grab the gross pay for a department such as the development department? Well, then we'll take a look at the column over here to the right. And we can see that this is the development sum of gross pay for the development team. So we can see it's kind of three dimensional here. We're getting a lot of nice information in here as well..
So we can already answer some questions. Some have gross pay gross pay by division gross pay by department as well. Now if I wanted to answer some deeper questions here, we can see that the total gross pay, the highest gross pay is attributed to the sales department. So now what I can do is I can go ahead and expand the sales department. Now I can get a breakdown of full time staff inside of the sales team, 15 for 6590 as opposed to part time 10,009, 6281..
So this is the pivot table condenses all the information for us. Notice we don't see any formulas in the front of the bar. So everything is happening in the background for us. We can see it has a nice layout. It's visually appealing. It's more efficient to work with. And if I wanted to just kind of move things around, I can simply just if I just want to focus on maybe the gross pay by department, I can do that. Now I can have a nice condensed view just to focus on what I want to use here as well..
So think about it. Go ahead and write it down. What do you consider to be a major benefit of working with a pivot table? Take a few minutes, write down your answers, come right back and we'll actually insert our pivot table. Welcome back. So let's go ahead and get this table or this data rather ready for a pivot table. So the question we want to ask is is this data ready for a pivot table. So we are working with a list. So we do have our 94 records here. We go down to row 98..
Posts Related:
- Power BI Dashboard Challenge: Executive Dashboards that Connect Company Systems, by Poorni G.
- Birds HD PNG Files Free Download Sheri Sk
- Web Automation - How to import web data using VBA
And we can see that this is sorted by division. So California and then Georgia and then new Jersey and then Washington here as well.
So the first thing to ask is remember when we working with a list. We want to make sure that we have a couple of items here. We want to make sure that we have meaningful and unique headers. We also want to make sure that we have. Unique labels. So in this case our labels are the employee ID number. And their unique..And they're unique to the entire record. And then we have complete records. Now remember a record because this is a column based table. We have our record is a full row of data here as well. So we already have unique and meaningful headers. They describe the data below. So for example the gross pay we expect to see gross pays. And no dates or text for the employee. We have the names in here as well..
So we're good to go. All right. The next thing we do have unique labels. So we can check off these first two items here as well. The next one we want to make sure that we have complete records and we have no blanks. Now this is a really big deal because when we create a pivot table, when you have blanks on your data set, they will actually show up as blanks on your pivot table. It will say blank, plus it will be incomplete. So for example, what I do is I use the count blank function..
This is a good first step. This tells me how many blanks I have in my data set. I have the function right here at. A simple function would tell Excel to count the month blanks from the range A4 to j 98. So if I were working with this data set and I delete a couple of cells here, it tells me right away that I have two blank cells, right. So right here we can see it's telling us that. So that's a good first step to make sure. So once I see that.
I can simply just go ahead and rectify those blanks. And as I rectify them it goes away. So we want to make sure that we have zero blank cells in our data set. The other thing to do is you want to check formatting, especially dates. Now if we take a look at the date of hire field. We can see that these look like dates to us. But if we take a look at the numbers command group, this tells us that this is not using the date format. This is a custom format..
Now this is a big deal because a lot of our data is time. As you know, it's based on time or dependent on time. Quarterly reports, monthly reports, so forth and so on. If you do not have a properly formatted date field, you will not get the benefits of the date tools that are available in your pivot table. So I'm going to go ahead and fix these dates. I'll go ahead and highlight all of them. And I'll just come back up to the numbers command group here. And I'll use the short date format. And we are good to go..
Right. Notice one of the things that we'll kind of discuss this later in the course. But one of the things that we can always ask about our data is how we can how can we improve the data so that we have more reporting features? Notice in column J that I have included a simple if function to determine whether or not the employee is a full time or part time employee. So if they're working less than or in this case greater than or equal to 40 than their full time Leicester equal to, then they are part time. So this is a great way to just improve the reporting, because now we have a status field that we can go ahead and.
Run data analysis on as well. So those are just some simple cleanup techniques. We want to make sure that this checklist is complete. Now that we have a nice list here. Let's go ahead and insert our pivot table. There are several ways to insert a pivot table. We'll explore some of the other options as we go through the course. But for now let's go ahead and do this the traditional way. We'll need to click on the insert tab..
And when we're on the insert tab we can see that to the far left in the tables command group we have pivot table. We also have recommended pivot tables. We're going to go ahead and click on Pivot Table here. Now the first step is just to select the data that you want to add to your pivot table. I don't have to select the entire data set. If I just click anywhere inside. In this case I'm just clicking on cell C6. Excel is going to automatically select all the data until it gets to a blank column.
Or a blank row. So it's automatically going to select all of this data for us.
As a matter of fact it uses the Regional command. So if I press Ctrl A, notice how it selects all of my data until it gets to a blank row or a blank column as well. So I'll go ahead and click anywhere inside of my data set. I'll click back on C6 here, and on the insert command I'll click on Pivot Table. When I click on Pivot Table A presented with this dialog box,.It's just asking me where is the data that I want to add. I can already see it's already selected all of my data for me. And I do want to place this on a new worksheet. You can place your pivot table on an existing sheet for example one of these sheets down here. The only problem is when your pivot table touches other data like text, you get some errors as well. So most of the time we want to go ahead and insert our pivot table on a new worksheet. I'll go ahead and do that. I'll go ahead and press okay. And then we'll take a look at the components of a pivot table..
So I just inserted my pivot table here and I'm just going to reset this here. There we go. So immediately we see two things on the far left. Here is our actual pivot table. It's blank right now. We don't have anything to work with. So that's the first thing. We're getting a little Q here. It says to build a report choose fields from the pivot table field list. Well that's over here. So that's the second component. We have our pivot table fields right here..
And then finally down here at the bottom we have four areas for filtering our data for displaying a field. And the rows for displaying a field in the columns and for displaying an overall value, which is the the big ticket item here as well. We also have two contextual tabs. So we have one that's called pivot table. Analyze. And we have one that's called design..
So right now I want the pivot table analyze. And from here we can do things like insert a pivot chart. We can refresh the table. We can change it to connect it to another data source. We can insert a slicer a timeline right here. We can rename the pivot table as well. We have access to the pivot table options. So this pivot table analyze is going to give us all the tools that we need to manage this pivot table..
We also have a design tab which is right here. And just like with tables charts we have styles that are available. And there's other layouts that we can choose from. And then there's other kind of sub features that we can turn off or on. We'll visit these later on in the course. But for now let's just go ahead and rename our pivot table. So to do that I can either click on Pivot Table Analyze. And I can go ahead and type the name right here. And that box right clicking on the pivot table gives you access to a lot of these commands that are available under the pivot table..
Analyze as well. Let's go ahead and rename the table. I'll click right in here. And I'll just call this payroll. Press enter. And there we go. So it is called payroll. Now over here on the far right there's two things I like to do before I start building my pivot table. Notice that the fields are not in alphabetical order. They appear in the order of the data set. So what I want to do is I want to go ahead and sort them A to Z..
So if I click on the gear icon here there's actually two things I want to do here. I want to go ahead and sort my fields A to Z. I also want to change the view to side by side. Right now it's using stacked. I don't want to do that. So I'll go ahead and sort A to Z first. There we go. But as you can see, I can't really see all of my fields. I have to scroll through. So I'm going to change the view to side by side is going going to give me a.
Taller window where I can see all of my fields. And over here I can see my four area boxes as well. So I'll change this to side by side, and then I'll be ready to start building my pivot table. So here's side by side. So this makes it more efficient if I have a lot of fields. I can see them here. And I still have access to my areas here as well. So it's really up to you. Go ahead and toggle back and forth either stacked.
Or side by side. I'll stick with side by side. Go ahead and play around with both and then come right back. Welcome back. Now that we have the basic setup here, let's go ahead and start building our pivot table. Now to build our pivot table. It's a matter of taking a particular field and dragging them to one of these actual boxes here. So I like to start with the values box first. And so what's the big ticket item here that we're focused on..
Well it's the gross pay. So I'll go ahead and drag the gross pay weight into this box. Or I can simply check it off. So either one will allow me to do that. I'll do both. I'll go ahead and drag the gross pay to the values box. And as you can see, let me zoom in just a little bit here. We can see we already have our answer. So the sum of gross pay is 84,306 and some decimals there as well. So it automatically runs the calculation for us..
It's already summarized that column. To get rid of that I can either uncheck it or drag it back. This time I'll drag it away and our pivot table is back to normal. So I'll go ahead and click on the checkbox here. And because it's a numeric value it automatically places it into the values box as well. So that gives us our answer. Well we do want to play around with the gross pay here, but let's take a look at something else. What if I click on the employee number..
Well let's see what the pivot table gives us here. So it shows all the employee numbers and the rows. Doesn't really help me. Let me go ahead and drag that to the values. And what it does is it inserts the count function for us because this is a alphanumeric character. It's giving us the count of employee numbers. Well why would we want to do that? Well, remember the employee numbers are unique..
So we have 94 unique employee numbers because it's using the count function here. What I can do now is figure out how many employees I have by division. So if I click on division here. Look at what it does. So it places the division into the rows for me. And now I get some nice information. So I have 15 employees and California 27 and Georgia.
30, new Jersey and 22 in Washington. Right. What about the buy department? If I click on department here, it also places the department in the rows. Well, I'll put the department in the columns instead. So look at how many questions we can answer just by having the employee number in the values department in the column and division in the rows. I can answer a very specific question, such as how many employees.
Do we have on our staff team in California? Three right here. So we can isolate the data. As you can see, the pivot table is taking care of all that for us. We don't need to enter any formulas or any logical functions like that as well. So I'll go ahead and reset the table. I'll go ahead and clear this out here. Right. I'll go ahead and put the gross pay back in here again..
All right. So by default it gives me the sum of the gross pay. But let's take a look and see how many other ways we can express the gross pay. I'll click on the drop down here. And here I have what's called value field settings. I'll click on here. Here's my value field Settings dialog. And now what I can do is I can change it. I can summarize the value by count and also by average. Well let's try the count and let's see what happens. So don't summarize that. Just count it for me. I'll press okay..
And look at that. We also get 94 because we have 94 records to work with. So this is another way to kind of give us the. Number of employees by division. Right. So we can always change that value the way that it's being expressed. Let's try one more. I'll go ahead and grab the average here. Right. And so the average gross pay is eight 9687..
What about average gross pay by division. Well the average gross pay in California 853 average gross pay and Georgia, 936. So those are just some of the basics of how we can build a table, and how we can change the way that the value is being displayed for us, as well. Go ahead and play around with the different values here. Click on the value field settings. Go ahead and use these different summaries here and familiarize yourself with them and come right back..
Welcome back. So we can already see how intuitive our pivot table is just by simply clicking on a field. We can have the pivot table built for us as we interact with our fields here as well. Well, let's go ahead and answer some of those specific questions that we asked earlier. What is the sum of gross pay? Gross pay by department and also gross pay by division. So I'll go ahead and drag the gross pay back to the values box, and we'll focus on the sum of the gross pay here as well. All right. So right now it's giving us the answer..
But I don't really like the way this looks. I don't have the currency or accounting format in here. So let's go ahead and apply the accounting format. I'll click on the dropdown here and I'll click on Value Field settings. We were here before. This time what I want to do is I want to go ahead and click on the number format down here on the bottom left. It's going to give me the format options that I simply just want to choose. The accounting format. So I'll click on number format here. And here is the accounting..
So we can either choose accounting or currency. I like to use accounting because it's kind of spaces out the dollar sign and the number. We can always change it if we want to. So I'll choose accounting and I'll press okay. And I can also press okay as well. Notice right here if we want to change the name that's being displayed right here, we can type in our custom name right here. So instead of saying sum of gross pay we could say something else here as well. Right. So maybe what I'll say here is maybe I'll say pay.
Per period. But I'll go ahead and press. Okay. So we can see the name has changed. More meaningful for me at least. And now we can read the number more correctly. So as we continue to build a table, all of our cells with numbers will have the accounting format. Okay. So we have the pay per period here. Well now we're concerned about the division. So let's go ahead and click on the division. Let's put the division into the rows..
And then we get a simple condensed list. Here we can see the pay per period for each of our divisions here. We can see new Jersey has the highest gross pay. Well what if I put that into the columns. So I can see it's in the columns here. Looks a little different. So placing it in the columns gives us a different view. I'll go ahead and leave it there. And I'll go ahead and add the department to the rows. So now that I have the division and the columns and the department in the rows, this is a nice view..
And we can answer a lot of questions here. So what is our move over here. So what is our grand total 84 306 61. Here's our total for Washington new Jersey, Georgia, California. And then over here we have our totals for the development department, research, sales and staff. So look at all these different questions that we can answer here. And all we did so far.
Was we placed the department in the rows Division and the columns. And the gross pay into the values as well. Now I can switch these if I want to. So for example, what if I want the department to be in the columns and the division to be in the rows? I can switch them and the pivot table will just make the adjustments for me. So I'll go ahead and move the division to the rows and I'll drag that the department to the column here as well. So this is just another view. We still get our totals. But now our subtotals down here on the bottom.
There are for our departments. And the totals on the right column for our divisions. So it's just another way to display the data. So it's your pivot table. You're going to go ahead and display the information in any form that you want. Now one thing that we can do as well is we can put the division and the department in the rows. So if I drag the department here into the rows. Now this is pretty cool. Now because we have two fields in one of these areas we have a hierarchy..
So because division is on the top here division is going to be a level one item. And my department is going to be a level two item. And we see that over here as well. We can see that we have our California is the level one item. And then we have the level two items down here. So development research and sales. So it's indented. So it kind of just organizes the data for us..
So we did not have to do that. The pivot table took care of that for us. Last thing to mention here we have different buttons. So you'll notice that we have a button here that allows us to either expand or collapse this list. So if I click here I can go ahead and collapse all of them. And this gives us a simple view. And now this is on demand. So if someone says well why is new Jersey so much. We can expand new Jersey and we can show the breakdown by department here as well..
