Excel Pivot Table Tutorial

Pivot Table Essentials

In this section, we're going to create a basic pivot table and look at some of the basic features that are available to you in a basic pivot table. 

Pivot tables allow you to summarize data quickly and effectively. 

Creating a Pivot Table

Let's start by taking the information below and the table and turning it into a pivot table. First step, just click anywhere in the table and then you're going to convert this table of information into an Excel object called the table. 

There are several advantages to using a table as the source data for your pivot table. 

One of those advantages is that you can add information at the bottom of your table and it will be automatically included in the source data for the pivot table, making it easier to bring that information in. We'll take a look at this towards the end. 

So let's create the table. You can go to the insert tab. And over in the tables group, you'll see table. All it takes is one click and you bring up the create table dialog box. Your table has headers and that option is already checked. So al-l you would need to do at this point is simply click “OK.”

Keyboard Shortcut

I'm going to click cancel because I want to use another method. You can use a keyboard shortcut. It's a very natural keyboard shortcut because that keyboard shortcut is control T as in table. You bring up the same exact dialog box. 

And now all you have to do is press, enter or click “OK.” The table is now created. I'm not going to worry so much about the formatting of the table since we're gonna create a pivot table anyway. 

Summarize 

Now over on the table design tab, I can see there is an option called summarize with pivot table that lets me know that the table is actually perfect for data source for my pivot table. So I'm going to choose summarize with pivot table. This brings up another dialog box to create pivot table dialog box. 

My range is selected and by default, I'm going to create the pivot table on a new worksheet. I'll press, enter or click. Okay. And I created that pivot table. I'm going to go to the bottom of the sheet and rename it to pivot one that way I'm not confused when it comes to identifying the sheet that contains the pivot table. 

So now we have this diagram and right above it says to build a report, choose fields from the pivot table field list. So even if you don't know what to do, you're getting instructions to tell you exactly where to start. So the pivot table field list is where you're going to need to go. If I look over to the right. I see pivot table fields and then I also see a list of fields.

This image lets me know that I'll probably need to check some boxes. And by doing so, I will create potentially a table of information. 

So I'm gonna start by clicking the checkbox for location. Instantly what happens is I get a column, a unique list of locations in a column for the row labels.

So that's automatically one benefit if you want to create a summary. You'll need a unique list of values, something that was not available to us in the original table where we had multiple and multiple occurrences of locations. Now we're ultimately going to fill in all four boxes. So one down, three to go. 

When it comes to values, the kind of field that you'll bring into values are numerical values. So the appropriate field to bring into the values section is salary. Now, all I need to do is just click the checkbox and anything numerical will automatically go to values. Now I have a pivot table that's actually useful. It's showing me the sum of salaries for all the locations. 

Formatting Pivot Tables

What I might want to do is change the formatting, so I'll right click anywhere on any value in that column and choose number format. Then I'll choose currency.

I can choose to remove the decimals before I click. OK. And then I'll click. OK, so when you right click to format numerical values, you want to make sure to choose number format and not format cells. 

Format cells will only format the cells you have selected, whereas no format will format the entire field. OK, now let's take a look at columns and filters. 

Now for these areas, you'll need to click and drag the field into that area. Just clicking on the checkbox will not automatically bring the field into these areas of your pivot table. So for columns, I want to be able to choose a field that acts as a category field. 

This field shouldn't have a lot of values. It should be able to categorize the information that I have in the pivot table. So I'm going to click on department and I'm going to drag it over into the columns section already. This is now providing a useful, multi-dimensional look at all my values. 

I can see salary by location as well as by department. I can see the total for location as well as the departments and where they intersect with each other. Now, what I might not like about this table is that there are blank cells. 

I want to make this pivot table look more uniform. So that can be adjusted with a pivot table option. All I need to do is right click choose pivot table options and the option I'm looking for is for empty cells show and I'll click in the box and simply type 0 I'll click OK. 

And now I have a more uniform look. So we filled in three of the areas. We have one more filters. Again, just like with columns, the kind of field that you'll bring into filters are fields that only have a few values, maybe two to five values. Profit sharing is going to meet that requirement. I'm going to click on profit sharing and move it into filters. Notice nothing happens to the pivot table right away. 

That's because all the information underneath the filter is going to change when you actually choose an option for filtering your information. In this case, I'll click the dropdown and select. Yes, if I select yes. All the information below will reflect information for people who said yes to profit sharing. And if I click the dropdown and choose no, the information will now reflect salaries for everyone who said no to profit sharing. Now, if I want to see everybody, I'll click the dropdown and I'll choose all. OK. That is a basic pivot table. 

We filled in information for rows, values, columns and filters. If you want to remove any of the fields, all you have to do is uncheck the box for the field you want to remove. So, for instance, I'm going to take away profit sharing. 

Now, another way that you can remove a field is by simply clicking on the feel and dragging it into the sheet and it disappears. I'm going to take the apartment and move it into filters. Then I'm going to take name and move it into rows. I want to filter the department so I only see people in the administration department. 

So I have a nice sized list of employees in the administration department. What we're gonna look at next is what happens when you update information. I'm going to go over to pivot tables and go towards the bottom of the table. I'm looking for Stephen Smith. I want to increase his salary by $10000. I'm going back to the pivot table to see his salary update, as you'll notice, his salary did not update. 

That is because whenever you make changes to the original data source, you need to go over to the pivot table, analyze tab and click refresh. 

This will update the information so as you're working with pivot tables. This is part of the process.

You will always need to go to the pivot table, analyze tab and click refresh to bring in new information or any changes you've made in the original data. Now I'm going to go to pivot tables. I'm gonna go towards the bottom. And let's pretend that we're going to add in a new employee.

That new employee's name is Joe Johnson. So I'm gonna type Joe Johnson I'll press tab. 

Joe works in the administration department. He makes 55000. He works in Atlanta and he says no to profit sharing. Now, I'll just use format painter just to copy the format that we already have there. So I want to now go back to the pivot table and see if Joe Johnson has been added. As you can see, Joe Johnson is not added to the administration department. I have to go to pivot table analyze and click refresh. And now his information is reflected. 

Recap

So that is a basic pivot table and some of the basic features that are available to you when you create a pivot table.