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

Drag the Student button to the top of the Pivot table

Release the left mouse button and Excel adds the Student Field

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:

Field Settings

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.

Click the black down arrow

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:

The list of subjects

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 scores:

A comment has been added

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.

And that wraps up this short introduction on Pivot Tables. But they are a good way to summarise long lists of data. In the next section, we take a look at Excel Forms, and see how to download data from a web page straight into Excel.

Move on to the next section -->

<--Back to the Excel Contents Page