Wednesday 9 November 2011

Microsoft Excel 2007 to 2010

Pivot Tables - Part Two

The reason why the scores from our Pivot Table are so strange is because Excel is using the wrong formula. It's using a Sum total when we want it to use an Average.
Here's the Pivot Table so far:
Your Excel 2007 Pivot Table so far
The numbers have all been added up. But we want averages, instead. To change the formula, click on Sum of Score under the Values field area:
You'll see the following menu:
Field Settings for the Pivot Table
Select, Field Settings (or Value Field Settings in Excel 2010). You'll then see the following dialogue box:
Data Field Settings
Change the Formula from Sum to Average, and then click OK. Your Average formula won't be formatted to any decimal places. So highlight you data. On the Home tab in Excel, locate the Number panel. Format your Averages so that it has no decimal places. Your Pivot Table will then look like this:
Almost there!
Look at cells A3, B3 and A4 above. These all have the not very descriptive names of Average of Score, Column Labels, and Row Labels. You can click inside of these cells and type your own headings, in exactly the same way as you would to enter text in a normal cell.
In the new version of the Pivot Table below, we have renamed these cells. We've also centred the data.
Only one thing left to do - spruce up the table by adding a bit of colour.
Click anywhere on your Pivot Table to highlight it. Now look at the Ribbon at the top of Excel . You'll notice a Design menu. Click on this to see the various design options.
The Pivot Table Style Options panel is interesting.
Pivot Table Style Options in Excel 2007
Select Banded Rows and see what happens. Now click Banded Columns.
Next to this panel, there are lots of Pivot Table Styles to choose from. Select one that catches your eye. Here's our finished Pivot Table again, only with a different Style:
A finished Pivot Table in Excel 2007
And here's the original:
The Original  Pivot Table
There's a lot more you can do with Pivot Tables, but we hope that this introduction has whetted your appetite!

No comments:

Post a Comment