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:

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:

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.

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.

Move on to Pivot Tables Part Two -->

<--Back to the Excel Contents Page