Pivot Tables in Excel

<div dir="ltr" style="text-align: left;" trbidi="on"> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> A Pivot Table is way to present information in a report format. The idea is that you can click drop down lists and change the data that is being displayed. For example, choose just one student from a drop down list and view only his or her scores. Pivot tables are a lot easier to grasp when you see them in action. Here's the one we're going to create in this section:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="A Pivot Table in Excel 2007" 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;"> Look at Row 4. This shows that the student is Elisa. If we click Elisa's drop down arrow, we'll see this:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="A Pivot Table Drop Down List" border="1" height="300" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableEnd2.gif" width="263" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Now we have another student to select (we'll only use two students, for this tutorial). We could untick Lisa, and tick Mary instead. Then her scores would display.</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The Subject and Month cells also have drop down lists. So we could view only January's scores, and just for Art and English, for example.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So this is a Pivot Table - a report that we can manipulate by selecting items from drop down lists. Let's make a start.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The first thing you need for a Pivot Table is some data to go in it. Instead of typing all the data out, you can simply grab ours. Go to this web page on our website and save the spreadsheet to your own hard drive:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Download the Data for the Pivot Table (Right click and select <b>Save Link/Target As</b>)</div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Once the spreadsheet is on your own computer, open it up. You should see this (If you get a warning across the top, click on Enable Editting):</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The Pivot Table Data in an Excel Spreadsheet (New window)</div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Highlight the data that will be going in to your Pivot Table (cells A1 to D37).On the Excel Ribon, click the <b>Insert</b> tab. From the Insert tab, locate the <b>Tables Panel</b>.</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Pivot Tables are on the Tables panel in Excel 2007" height="85" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableRibbon.gif" width="107" /></div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Pivot Tables panel in Excel 2013" height="92" src="http://www.homeandlearn.co.uk/excel2007/images/2013/section3/pivot_table_panel.gif" width="193" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> On the <b>Tables</b> panel click <b>Pivot Tables</b>. The <b>Create Pivot Tables</b> dialogue box appears:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The Create Pivot Table dialogue box" height="284" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableCreateDB.gif" width="511" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> In the dialogue box above, the data that we highlighted is in the <b>Table/Range</b> textbox. You can select different cells by clicking the icon to the right of the Table/Range textbox. You can also specify an external data source, such as a text file, for the data in your Pivot Table.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We've selected a <b>New Worksheet</b> as the place where the Pivot Table will be placed. Click OK.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> When you click OK, Excel presents you with a rather complex layout. The area on the right should look something like one of these below, depending on which version of Excel you have:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Pivot Table Field List" height="574" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableFieldList.gif" width="204" />     <img height="574" src="http://www.homeandlearn.co.uk/excel2007/images/2013/section3/pivot_table_DB.gif" width="225" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> It helps to have a look again at what we're trying to create. Here's the completed Pivot Table again:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Completed 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;"> Now take a look at the <b>Pivot Table Field List</b> image again, the one above the completed pivot table. It has tick boxes for Month, Subject, Student, and Score. These are column headings from the original spreadsheet data. We've put the Month in cell A7 on our Pivot Table, Subject is in cell B6, Student is in cell B4, and Score is the Average scores in cells C8 to G10. You'll see how it works, though.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The idea is that you tick a box in the Pivot Table Field List, and then drag it to the four areas below. Excel will take care of the rest.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So, tick all four boxes in the field list:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Tick all four items" border="1" height="221" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableFieldList2.gif" width="204" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Excel will create a basic (and messy) Pivot Table for you. But we're going to put our 4 fields into the 4 areas below. Here are the 4 areas we can drag to:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The four fields" border="1" height="334" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableFieldList3.gif" width="204" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> For the <b>Report Filter</b>, we want the name of a Student. For the <b>Column Labels</b>, we want the Subject, and for the <b>Row Labels</b>, we'll just have the Month. The <b>Values</b> will be the Average scores.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> If you look at the Field areas after you have ticked all four boxes, however, you may see something like this:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The items are in the wrong place" border="1" height="343" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableFieldList4.gif" width="204" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Month, Subject and Student have all been grouped under <b>Row Labels</b>. You can drag and drop these, though.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So click on <b>Student</b> in the <b>Row Labels</b> box. Hold down your left mouse button, and then drag it in to the <b>Report Filter</b> box. If you don't fancy dragging and dropping, simply click the Student item with your left button. From the menu that appears, select <b>Move to Report Filter</b>:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Click on Move to Report Filter" height="257" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableFieldList5.gif" width="174" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Your Field areas will then look like this:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The Student item has been moved to Report Filter" border="1" height="334" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableFieldList6.gif" width="201" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Move Subject from <b>Row Labels</b> to the <b>Column Labels</b> area:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Move to Column Labels" height="256" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableFieldList7.gif" width="174" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Your Field areas will then look like this:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="All four items have been moved" border="1" height="331" src="http://www.homeandlearn.co.uk/excel2007/images/2PivotTableFieldList8.gif" width="196" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The Pivot Table on your spreadsheet will look a lot different, too. It should be looking like this:</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 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Our Pivot Table is coming along, but the scores are all wrong, and it needs tidying up a bit. We'll continue this tutorial in the next part, below.</div> </div>

Pivot Tables in Excel

A Pivot Table is way to present information in a report format. The idea is that you can click drop down lists and change the data that is...

Read more »