What is a PivotTable?
A PivotTable is a useful tool for creating summary reports from Excel data. And the cool thing is you can “Pivot” or move the fields of the report. For example all of the following screen shots are from PivotTables that were created from an Excel list of 5406 movies.
Figure 1: Category / Rating Matrix
|Figure 2: Per Category, per Rating report
||Figure 3: Per Rating, Per Category Report|
Figure 4: Movies from the 1970s by Year by Rating
Figure 5: Movies by Decade
How do I create a simple easy to use PivotTable?
- Start Excel and create or open your file.
- Make sure your data can support a PivotTable by using the check list below:
- Each column of data has a column heading
- Each column heading is unique
- Each column heading is in the same row
- The column headings do not span multiple rows
- No merged cells exist
- All the data is contiguous
- The data in each column follows a consistent format
Position the cursor in one cell, and only one cell of the data.
From the ribbon choose Insert, Tables, PivotTable.
In the resulting dialog box make sure the table/range is referring to the correct table or range and that the destination is a New Worksheet, then click OK.
Drag the fields from the PivotTable Field List to the desired location as shown below.
Notice, you now have a PivotTable.