Extending the Pivot Table

- This lesson follows on from the previous one.
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 scores:

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.