Free computer Tutorials
|HOME||Stay at Home and Learn|
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:
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:
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.
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.