Creating and Navigating Basic Pivot Tables in Excel

Pivot Table One, The Essentials. In this section, we're going to create a basic Pivot Table and examine some of the essential features that are available to you in a basic Pivot Table. Pivot Tables allow you to summarize data quickly and effectively.

Let's start by taking the information below in the table and turning it into a Pivot Table. The first step: just click anywhere in the table, and then you're going to convert this table of information into an Excel object called a 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 all you would need to do at this point is simply click OK.

I'm going to 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 going to create a Pivot Table anyway.

Now over on the Table Design tab, I can see there is an option called Summarize with PivotTable. That lets me know that the table is actually perfect as a data source for my Pivot Table. So I'm going to choose Summarize with PivotTable.

This brings up another dialog box, the Create PivotTable 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 OK, and I've 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. All right, so now we have this diagram, and right above it, it says to build a report, choose fields from the PivotTable 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 PivotTable Field List is where you're going to need to go. If I look over to the right, I see PivotTable 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 going to 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 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 value 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.

All right, 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. 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 Number Format will format the entire field.

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 column 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 PivotTable option. All I need to do is right-click, choose PivotTable Options, and the option I'm looking for is For empty cells show. I'll click in the box and simply type zero. I'll click OK and now I have a more uniform look.

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. And if I want to see everybody, I'll click the dropdown and I'll choose All. Okay, 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 field and dragging it into the sheet and it disappears. I'm going to take Department 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 size list of employees in the Administration department. What we're going to 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 $10,000.

Okay, I'm going back to the Pivot Table to see his salary update. As you'll notice, his salary did not update and that is because whenever you make changes to the original data source, you need to go over to the PivotTable 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 PivotTable 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 going to 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 going to type Joe Johnson.

I'll press TAB. Joe works in the Administration department. He makes $55,000.

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 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 PivotTable Analyze and click Refresh and now his information is reflected. So that is a basic Pivot Table and some of the basic features that are available to you when you create a Pivot Table.