PivotTables offer much flexibility in viewing data. The filter drop-down list allows us to view only full- or part-time students. We can also hide or display data by using the drop-down arrows in the column or row label areas.
Step1. To view the full-time students only, in the FT/PT drop-down list,
Click, Click Full time, Click
NOTE for MacOS Users: To view the full-time students, Click the Part time check box to deselect it.
Step2. To deselect all academic programs, in the Program drop-down list,
Click, Click the (Select All) checkbox
Step3. To select the Arts & Sciences Undergrad program,
Click the "Arts & Sciences Undergrad" checkbox
Step4. To close the dialog box,
NOTE for MacOS Users: To close the dialog box, Click in a cell of the PivotTable.
Step5. To view the totals for the Arts & Sciences Undergrad students,
Step6. To redisplay all of the data for Arts & Sciences Undergrads,
Viewing Hidden Details
When working with PivotTables, it may be helpful, or even necessary, to see the details behind the data. By double-clicking a value, we can see the hidden details behind it.
Let's look at the names of the full-time, Anthropology majors who are juniors.
Step1. To view the two juniors in Anthropology,
Double-Click cell C5
Step2. To rename the worksheet,
Double-Click the worksheet tab,
type: AnthJuniors Enter
Step3. To switch back to the AcadPivotTable worksheet,
Click the AcadPivotTable worksheet tab
Rearranging Column Labels
Column or row labels are usually sorted alphabetically in ascending order. Sometimes this may not be the most logical way to view labels. For example, we would like for the class labels to be in chronological order (sophomore, junior, senior, etc.) rather than in alphabetical order. Therefore, we will move the Sophomore label.
Step1. To move the Sophomore column label to the beginning,
Right-Click the Sophomore column label
Step2. To move the Sophomore label,
Point to Move, Click Move "Sophomore" to Beginning
NOTE for MacOS Users: To move the Sophomore label, Press & Drag the label before Junior.
It is a simple task to manipulate the data in a PivotTable to show different perspectives of the data. In this exercise, we will calculate a percentage using a built-in calculation. We will customize a calculation later in the workshop.
Using the % of Row Total Calculation
Next we will have Excel calculate the percentage of the total number students in each program. Because we once again need to work with a unique identifier, we will add the University ID field to the Values area a second time. We can then change the field settings of the second occurrence to calculate a percentage of total number of students. Fields can be repeated as often as necessary.
Step1. To open the Field List,
Right-Click in the PivotTable, Click Show Field List
NOTE for MacOS Users: To open the Field List, on the Ribbon, Click the PivotTable Analyze tab, Click Field List.
Step2. To place the University ID field in the Values area,
Press & Drag University ID below Students in the Values area
Step3. To begin to reflect a percentage format, in the bottom of the PivotTable Field List,
Click the Sum of University ID field,
Click Value Field Settings...
NOTE for MacOS Users: To begin to reflect a percentage format, in the PivotTable Builder, Click
Step4. To move to the Show Values As tab,
Click the Show Values As tab
NOTE for MacOS Users: To move to the Show data as tab, in the PivotTable Field dialog box, Click the Show data as tab.
Step5. To specify the type of calculation we wish to perform, in the Show values as drop-down box,
|Show Value As||Description|
|No Calculation||Displays the value that is entered in the field|
|% of Grand Total||Displays values as a percentage of the grand total of all values or data points in the report|
|% of Column Total||Displays all the values in each column or series as a percentage of the total for the column or series|
|% of Row Total||Displays the value in each row or category as a percentage of the total for the row or category|
|% Of||Displays values as a percentage of the value of the Base item in the Base field|
|% of Parent Row Total||Calculates values as follows:|
(value for the item) / (value for the parent item on rows)
|% of Parent Column Total||Calculates values as follows:|
(value for the item) / (value for the parent item on columns)
|% of Parent Total||Calculates values as follows:|
(value for the item) / (value for the parent item of the selected Base field)
|Difference From||Displays values as the difference from the value of the Base item in the Base field|
|% Difference From||Displays values as the percentage difference from the value of the Base item in the Base field|
|Running Total In||Displays the value for successive items in the Base field that are displayed as a running total|
|% of Running Total In||Calculates the value as a percentage for successive items in the Base field that are displayed as a running total|
|Rank Smallest to Largest||Displays the rank of selected values in a specific field, listing the smallest item in the field as 1, and each larger value with a higher rank value|
|Rank Largest to Smallest||Displays the rank of selected values in a specific field, listing the largest item in the field as 1, and each smaller number value with a higher rank value|
|Index||Calculates weight value as follows:|
((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total))
NOTE for MacOS Users: Not all of these options are available in MacOS.
Step6. To continue,
Click % of Row Total
NOTE for MacOS Users: To continue, Click % of row.
Step7. To change the name of the field to reflect a percentage, in the Custom Name field,
Press & Drag the text, type: % Students
Step8. To open the Format Cells dialog box, in the lower left corner,
NOTE for MacOS Users: To open the Format Cells dialog box, Click.
Step9. To change the number of decimal places to 0, in the Decimal places field,
Press & Drag the value, type: 0 Enter
Step10. To confirm the changes and close the dialog box,
Calculating a Percentage of Students by Residence
Let's add more data to our PivotTable. We will add the Residence field to the Columns area. This will allow us to see the percentage of students in each class with various home residences (In State, Out of State, International).
Step1. To add the Residence field, from the Field List,
Press & Drag Residence between Class and Values fields in the Columns area
Step2. To close the Field List,
NOTE for MacOS Users: To close the Field List, in the upper left corner of the Field List, Click.
Step3. To remove the filter,
Click (All), Click
NOTE for MacOS Users: To remove the filter, Click, Click (Select All), Click in the spreadsheet.
Step4. To save the Excel workbook, press:
Formatting the PivotTable
Excel provides us with several pre-defined styles that we can apply to a PivotTable. These styles can not only make our report more attractive, they can also make it easier to understand. By differentiating rows and columns with color, a user's eye can be more easily drawn to important information.
Step1. Verify that a cell is selected in the PivotTable.
Step2. To begin to format the report, under PivotTable Tools,
Click the Design tab
Step3. To view the various styles,
NOTE: In some configurations of Excel 2016, Live Preview may be available. This feature allows users to point to a style to see a preview without applying it.
Step4. To see different styles,
Step5. To apply a style,
NOTE: We can override any style format by manually choosing different fonts and background colors by using the Mini toolbar or the Ribbon. Remember to consider accessibility when choosing colors and fonts.
Step6. Save the workbook.