Tip:
Highlight text to annotate it
X
Hello. In this video I’m going to show you how to make a simple pivot table from a list of data inside of Microsoft Excel.
Here we have a fictious list of employees who are employed by a fictious company called Child’s Play INC.
In this list we are gathering information about these individuals.
We have their position, department, division as well as their salary, start date and birth date.
When you’re working with a list of information, the columns are called fields and the rows are called records.
In an Excel list there are three different kinds of fields or three different kinds of columns.
One is called a category field, which is a field you can group by.
For example, position, department and division are all category fields.
We can group everyone by the toys division, the games division, or the teaching aides division.
Or if we are talking about the department we can group all of the art people together, or the marketing people together, and all of the admin people together.
The second kind of field you can have is a data field.
An example of a data field would be salary.
A data field is anything you can add, subtract, multiply, or divide.
In our case we’re using the salary field as our data field.
Finally there are arbitrary fields.
Those are fields are not data or category fields.
For example, first name, last name and employee ID are arbitrary fields.
We’re going to build a pivot table that organizes this list of information by department and position as well as summarize the salaries in those groups.
I’m going to click in the upper left hand corner of my list, and then go to the top, under Insert, click on Pivot Table.
A dialog box will appear called Create Pivot Table.
It will select the entire list of data for you automatically.
In this case you’re going from cells A4 to I118.
Make sure there are no blank rows or blank columns in your list before you create your pivot table.
If you do have a blank row or blank column in your list the pivot wizard will only use the data up to your blank row or your blank column.
We’re also going to place this on a new worksheet.
I click “OK” and I get my new worksheet and you will see the pivot table frame in my new worksheet.
There is also a pivot table field list on the right as well.
All of the fields that are in our list are listed in the pivot table field.
Next I’m going to grab onto department field and drag it over to my pivot table frame into the section where it says “Drop Column Fields Here.”
You can see once you do that, the columns are labeled with different departments.
Now I’m going to drag Position over to “Drop Row Fields Here,” and you will see a listing of different positions.
I'll go ahead and drag that over here and you're not going to see a listing of different positions.
Then I’m going to drag Salary into my data area, and you will see how much each department spends by position.
If you want to change this pivot table around, it is very easy to do.
For example, let’s day we want out departments to be our row headings and our divisions to be our column headings.
First off I’m going to uncheck the items in the pivot table field list on the right of the screen.
This will take you back to the normal pivot table frame.
Now I’m going to drag department over to row, and division over to column, and add salary to the data area.
You see I’ve created another pivot table.
So that’s how you can create Pivot Tables in Microsoft Excel.
In other videos we’re going to talk about some of the other options you can work with in this pivot table as well as how to format it.