Pivot Tables - Part Two

<div dir="ltr" style="text-align: left;" trbidi="on"> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> 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.</div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Here's the Pivot Table so far:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Your Excel 2007 Pivot Table so far" height="161" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableHalf.gif" width="504" /></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The numbers have all been added up. But we want averages, instead. To change the formula, click on <b>Sum of Score</b> under the <b>Values</b> field area:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img height="331" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableFieldList9.gif" width="196" /></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> You'll see the following menu:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Field Settings for the Pivot Table" height="256" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableFieldList10.gif" width="174" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Select, <b>Field Settings</b> (or <b>Value Field Settings</b> in Excel 2010 and 2013). You'll then see the following dialogue box:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Data Field Settings" height="320" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableSumAverageDB.gif" width="354" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Change the Formula from Sum to <b>Average</b>, and then click OK. Your Average formula won't be formatted to any decimal places. So highlight you data. On the <b>Home</b> tab in Excel, locate the <b>Number</b> panel. Format your Averages so that it has no decimal places. Your Pivot Table will then look like this:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="177" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableHalf2.gif" width="634" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Almost there!</div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> 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.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> In the new version of the Pivot Table below, we have renamed these cells. We've also centred the data.</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img height="160" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableHalf3.gif" width="569" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Only one thing left to do - spruce up the table by adding a bit of colour.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Click anywhere on your Pivot Table to highlight it. Now look at the Ribbon at the top of Excel . You'll notice a <b>Design</b> menu. Click on this to see the various design options.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The <b>Pivot Table Style Options</b> panel is interesting.</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Pivot Table Style Options in Excel 2007" height="85" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableRibbonDesign.gif" width="234" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Select <b>Banded Rows</b> and see what happens. Now click <b>Banded Columns</b>.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> 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:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="A finished Pivot Table in Excel 2007" height="141" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableEnd3.gif" width="544" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> And here's the original:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The Original Pivot Table" height="221" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableEnd.gif" width="606" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> There's a lot more you can do with Pivot Tables, but we hope that this introduction has whetted your appetite! But click the dropdown boxes on your Pivot table and play around with them. Change the values you see on the various lists for Student, Subject, and Month.</div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> In the next section, you'll learn how to reference other Excel worksheets.</div> </div>

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...

Read more »