Now that we've created our table, let's take a moment to look at the data. We have a worksheet which shows us information about individual students, including names, university ID, program, major, class, gender, and residence. We can use these fields to create PivotTables that will give us many different views of our data.
When creating a PivotTable, we place fields as either a column label or row label. To help decide whether something should be a column label or a row label, you should think about how you want that item to appear in the PivotTable and how you want to summarize the data.
First we will create a PivotTable and use the Count function to count how many students are in various academic programs based on part-time and full-time status.
The PivotTable will look something like this before we modify the layout:
To create a PivotTable, we must first select a cell within the data range from which we want to create our table (A1:M289).
Step1. Verify that a cell within the range A1:M289 is selected.
Step2. To switch to the Insert tab, on the Ribbon,
Click the Insert tab
Step3. To begin creating the PivotTable, at the far left of the Ribbon,
Step4. Verify that the New Worksheet radio button is selected.
Step5. To continue,
NOTE for MacOS Users: In Excel 2016 for Mac, this is called the PivotTable Builder. It looks slightly different but works the same.
Step6. To select the current worksheet name,
Double-Click the Sheet1 worksheet tab
Step7. To rename the tab, type:
Adding Fields to the PivotTable
When creating a PivotTable, we can place as many fields from the PivotTable Field List as we want to any area (Filters, Columns, Rows, Values). Part of the appeal of a PivotTable is that it can always be changed. We can easily move the fields around later if we decide to modify the layout.
First we will add the rows, which will give us a breakdown of majors by the various programs.
To place the fields into the PivotTable, we can either use the checkboxes or press and drag fields to the appropriate area. When using the field checkboxes, fields are placed based on the type of data they contain. This may not give us the results we want and the fields may have to be moved to another area. We will press and drag the fields.
Step1. To place the Program field in the Rows area, in the PivotTable Field List,
Press & Drag the Program field to the Rows area
Step2. To place the Major field in the Rows area, in the Field List,
Press & Drag the Major field below the Program field in the Rows area
Step3. To place the FT/PT field, from the Field List,
Press & Drag the FT/PT field to the Columns area
NOTE: To remove a field from the PivotTable, you can either uncheck the checkbox in the top part of the Field List, or Press & Drag the field out of the bottom part of the Field List.
Step4. To place the University ID field in the Values area,
Click the University ID checkbox
Step5. To select the University ID field, in the Values area,
Click Sum of University ID field
Step6. To modify the University ID field,
Click Value Field Settings...
NOTE for MacOS Users: To modify the University ID field,
Step7. To change the calculation, in the "Summarize value field by" box,
Step8. To change the custom name, in the Custom Name field,
Press & Drag the text, type: Students
Step9. To close the Value Field Settings dialog box,
Step10. To save the Excel workbook, press:
Displaying Data in Tabular Form
When working with a PivotTable, we can display or hide subtotals for individual columns or rows, display or hide grand totals for the entire report, and calculate the subtotals and grand totals with or without filtered items. We can also change the format of the PivotTable.
By default the row labels are in compact form. We can show row labels in outline or tabular form, insert a blank line after each item label to allow more spacing between items, show all items with no data, and insert a page break after each item.
These changes can be made on the Design tab.
Step1. To move to the Design tab, on the Ribbon,
Click Design tab
Step2. To begin changing the layout of our data, in the Layout group,
Step3. To show the item labels in tabular form,
Click Show in Tabular Form
NOTE: The form of the PivotTable can also be changed by using Fields Settings dialog box.
Expanding and Collapsing Fields
Notice the outline symbols to the left of the PivotTable that are used to collapse or expand the various groups. We can use these options to see less or more information in our PivotTable.
First let's collapse an individual field.
Step1. To collapse the Arts & Sciences Undergrad group,
Step2. To see row 70,
Step3. To expand the Arts & Sciences Undergrad row label,
scroll up, Click
Step4. Select the Analyze tab on the Ribbon, if necessary.
NOTE for MacOS Users: Select the PivotTable Analyze tab, if necessary.
Step5. To collapse all of the programs, in the Active Field group,
Step6. To expand all of the programs, in the Active Field group,
Moving and Adding Fields
Notice that the FT/PT field is in the Columns area. When we move a field from the rows area to the columns area, or from the columns area to the rows area, we are transposing the vertical or horizontal orientation of the field, which is called pivoting a row or column. We can also change the look of a PivotTable by moving a field to the Filters area.
Next we will move the FT/PT field to the Filters area. This will enable us to quickly filter full-time or part-time students.
Step1. To move the FT/PT field to the Filters area, from the Columns area,
Press & Drag the FT/PT field to the Filters area
Step2. To add the Class field to the Columns area, from the Field List,
Press & Drag the Class field to the Columns area
Step3. To close the Field List, in the upper corner of the Field List,
NOTE for MacOS Users: To close the Field List, in the upper left corner of the Field List, Click.
Step4. To save the Excel workbook, press: