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 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 Rowarea
• 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 Columnarea, 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.

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.