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 data.
Click the Next button on Step Two. Step Three of the Wizard appears. It's a little more complicated, this time.
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 one:
If you don't see the Pivot Table toolbar, click on View > Toolbars > Pivot Table.
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.
No comments:
Post a Comment