View your grade book in a PivotTable with Microsoft Office Excel 2007

Learn how to see data patterns, trends, and use pivot tables with Mircrosoft Office Excel 2007.

View your grade book in a PivotTable with Microsoft Office Excel 2007

It's often difficult to see patterns, trends, and details in large databases filled with grades and other data. When you convert a database to a table, you can change how you view the data by clicking the arrows in the header row. Watch the video

Ways to view information

You can sort the data by different fields and filter out data that you don't need to see. With a PivotTable in Microsoft Office Excel 2007, you can go further by changing how the data is laid out. For example, you could create a PivotTable that shows grades by student lab team.

Sort your data to see patterns and trends with PivotTables in Office Excel 2007.

A PivotTable is composed of four areas: Row Labels, Column Labels, Values, and Report Filter. You create different views of your data by the ways in which you assign your data fields to these areas. For example, you could assign the Student names field to column labels, and the names would appear as column headings.

The best way to understand PivotTables is to experiment with them. To help you get started, we will create a PivotTable that shows a summary of final grades or scores for individual students, using a sample grade book table. To find out how to create a table like this, see Keep track of grades in Microsoft Office Excel 2007.

  1. Click a cell in your table (or a cell in a named range.) Then, on the Insert tab, in the Tables group, click the arrow under PivotTable, and click PivotTable. The dialog box shows the table or range you selected.
  2. Click OK, and Office Excel 2007 creates a new worksheet with an empty PivotTable. By default, the task pane on the right displays the PivotTable Field List that contains all of the fields in your database and four boxes (Report Filter, Column Labels, Row Labels, and Values).
  3. Drag Student names from the Field list to the Row Labels box. The student names appear in the PivotTable in a column.
  4. Drag the fields containing final scores (Total Average, Quiz/Lab, Tests, Homework, and Participation) one at a time from the Field list to the Values box. The column labels in the PivotTable become the names of the five fields preceded by "Sum of," and the field values appear below the column labels.
  5. Drag Values from the Column Labels box to below Student names in the Row Labels box. A set of scores displays under each student name.
  6. Drag Student names from Row Labels to Report Filter. Student names appear at the top of the table, and the five Values display summary values.
  7. Click the arrow next to Student names, select a name, and then click OK. The table displays the scores for the selected student.
  8. To change a field name in the PivotTable, click the cell and type a new name in the Formula Bar. You can modify formatting the same as with any other cell or range.

Teacher Tips

  • Create formulas fast.

    Office Excel 2007 has a number of functions your students can use to create fast formulas to analyze data. For instance, COUNT and COUNTIF. Have your students devise formulas using and comparing each function.

  • More effectively manage your grade book

    When you convert the data range to a Table (by selecting "Format as Table" in the Home tab), you can begin to sort or manage the data in your grade book.