How to Create a Pivot Table in Microsoft Excel
In the previous part, you learned what a Pivot Table is. In this part, you create a Pivot Table for
yourself, using the data you downloaded. (If you haven't yet downloaded the spreadsheet, click here:
Download the Pivot Table data )
Creating a Pivot Table in Excel
The Pivot Table is constructed using a Wizard. To create yours, do the following:
Click inside cell A2 on the spreadsheet you downloaded above
From Excel's menu bar, click on Data
From the menu that drops down, click on PivotTable and PivotChart Report
The Pivot Table wizard starts up
As the Wizard says, this is step one of three. There's nothing much for us to do in step one because
the options we want are selected: "Microsoft Excel list or database" and "Pivot Table". With these
options chosen, click the Next button at the bottom.
The Wizard moves on to Step Two. It looks like this:
The Range of cells that Excel will include in our Pivot Table is A1 to D37. (You can change this if you
wanted.) Because we clicked in cell A2 to begin with, Excel has taken that as the first Row of Data.
Excel uses the labels from Row 1 as Headings. Excel will use these for our drop down boxes and
Click the Next button on Step Two. Step Three of the Wizard appears. It's a little more complicated,
We'll accept the default position for the location of the Pivot Table - New worksheet. The button we're
after is Layout. So click the Layout button to see a quite complicated dialogue box. This one:
The Field Buttons the Wizard is talking about are those four on the right: Month, Subject, Student
and Score. The idea is that you click on a button. Hold down your left mouse button and drag to an
area on the left. We're going to drag one button to the Column area, one to the Row area, and one to
the Data area.
In the Row area, we'll put Month; in the Column area, we'll put Subject, and in the Data area we'll put
Score. We'll do something with the Student button after the Pivot Table has been constructed.
So do the following:
Click on Month
Hold down your left mouse button
With the mouse button held down, drag the mouse pointer over to the Row area
Let go of the button when it's there
A button will appear in the Row area
The images below show the process in action
Hold Down the left Mouse button and drag
Drag the mouse pointer over to the Row area
Let go of the left mouse button when the pointer is over Row
When you have the Month button in place, drag the Subject button to the Column area, and the
Score button to the Data area. Your dialogue box will then look like this:
Click OK when your dialogue box looks like the one above. You will be taken back to Step Three of
the Wizard. Click the Finish button and you're done. You'll then have a spreadsheet that looks like this
If you don't see the Pivot Table toolbar, click on View > Toolbars > Pivot Table.
I'm sure you'll agree - our Pivot Table is coming along nicely. In the next part, you'll learn how to
manually add a button to a Pivot Table.
Microsoft Excel Pivot Tables - Part Two
We're now going to put the Students button on the Pivot Table. So do the following:
Locate the Student button on the Pivot Table toolbar, as in the image below:
Hold down your left mouse button on the Student button
Keep the left mouse button held down
Drag the Student button to the top of the Pivot table, where it says "Drag Page Fields Here."
Let go of the left mouse button
Excel adds the Student field to the pivot table
The two images below show the process in action
Drag the Student button to the top of the Pivot table
Release the left mouse button and Excel adds the Student Field
We're almost there, now. Only a couple more things left to do. First, take a look at the scores. What
the Pivot table is doing is adding all the scores up. That's because of cell A3. Notice that it says "Sum
of Score". We don't want it to do that. An Average is much better for our purposes.
To change the Scores to Averages, do this:
On the Pivot Table toolbar, click on the Pivot Table button
A menu pops up like the one below:
To change the Scores to Averages, click on Field Settings
The following dialogue box appears:
There are not too many functions to choose from in the Summarize by list, but Average is on there. So
click on Average, and then click OK. The scores will change on the spreadsheet.
Some of the scores in the Grand Total Row and Grand Total column will be a bit long. But you can
format the numbers to in the usual way.
So highlight the Grand Total row
From the Excel Menu Bar, click on Format
From the drop down menu, click on Cells
When the dialogue box appears, select the Number tab strip
Format to 1 decimal place
Do the same for the Grand Total row
Your Pivot Table should now be looking like the one below:
We can now take a look at those drop down boxes. We'll start with the Student box.
At the moment, the Student box says All. Click the black down arrow to see the list of students.
Our two Students are listed there. Click on Elisa, then click the OK button. Notice how your
spreadsheet has changed. It should now only be showing you Elisa's results. Click the black down
arrow in cell B1 again, and click on Mary. Then click the OK button. Your spreadsheet will change to
show only Mary's results.
Try clicking the black down arrow of Subject, in cell B3. You should see this:
All the Subjects have ticks in them. Click on a tick and it will disappear. Try un-ticking a few of the
subjects. Then click the OK button to see what happens.
The Month list in cell A4 shows a similar list with ticks in them. Un-tick a month and see the results
when you click OK.
You can add comments to pivot tables. The one below shows a Comment about Elisa's English
Another thing you can do is change the type of Pivot Table Report. From the Pivot Table toolbar, click
the Pivot Table button. From the menu that pops up, select Format Report. Click on any of the formats
you like then click OK to see what happens. If you don't like what you see, click Edit > Undo
AutoFormat to get back to your Pivot Table.