When working with data in Excel, we may want to create a frequency distribution andcount the number of times a certain variable occurs. PivotTables make this task easy. Let's show a frequency distribution of test scores by grouping the scores in increments of 10.
Step1. To switch to the TestScores worksheet,
Click the TestScores tab
Step2. Verify that a cell within the list is selected.
Step3. To switch to the Insert tab, on the Ribbon,
Click the Insert tab
Step4. To begin creating the PivotTable, in the Tables group,
Step5. To choose where to place the PivotTable,
Click the "Existing Worksheet" radio button
Step6. To move the dialog box, if necessary,
Press & Drag the dialog box to the right
Step7. To choose a specific location,
Click in cell E4
8. To continue,
Adding Fields to the PivotTable
We will add a row field to display the scores and a values field to count the scores.
Step1. To make the Test Score field a row, from the Field List,
Press & Drag the Test Score field to the Rows area
NOTE: Because of the simplicity of this data set, we could use either the Student ID field or Test Score field in the Values area.
Step2. To make the Student ID field a value, from the Field List,
Press & Drag the Student ID field to the Values area
Step3. To begin modifying the Student ID field, in the Values area,
Click Sum of Student ID, Click Value Field Settings...
NOTE for MacOS Users: To begin modifying the Student ID field, in the Values area, Click
Step4. To change the calculation, in the "Summarize value field by" calculation list,
Step5. To change the custom name, in the Custom Name field,
Press & Drag the text, type: Test Score Distribution
Step6. To close the Value Field Settings dialog box,
Grouping the Values
Now that we have our count of test scores, we can generate groups, or bins, that contain specific scores. Let's say we want to put the test scores in groups of 10. We can create groups that begin at 0 and end at 100, for example: 50-59, 60-69, 70-79, etc.
The grouping tools are under the Analyze tab on the Ribbon. Before we begin, we need to confirm that a score in the PivotTable is selected.
Step1. To select a score in the PivotTable, in the Row Labels column,
Click any score
Step2. Switch to the Analyze tab, if necessary.
Step3. To group the scores, in the Group group,
NOTE for MacOS Users: To group the scores, Click, Click Group Selection.
Step4. To specify the starting value, type:
Step5. To continue,
NOTE: Row labels may be edited to reflect letter grades instead of test scores.
Creating Unique Groups
The above method for creating groups works well if we want equal-sized groups of 10. However, we can also create unique groups. We will create groups for a pass/fail grading scale. Before we can create our new groups, we need to remove the current ones.
Step1. To select the PivotTable, if necessary,
Click any score in the Row Labels column
Step2. To ungroup, on the Analyze tab, in the Group group,
NOTE for MacOS Users: To group the scores, Click, Click Ungroup...
Step3. To select the failing grades, with the white plus sign cursor,
Press & Drag the cell range E5:E10
NOTE: When selecting, make sure to use the white plus sign cursor, not the black arrow cursor. Using the incorrect cursor will bring unexpected results.
Step4. To group the selection,
Right-Click the selection, Click Group...
NOTE for MacOS Users: To group the selection, Right-Click the selection, Click Group and Outline, Click Group...
Step5. To create the second group, in the Row Labels column,
Press & Drag the cell range E12:E53, Right-Click the selection, Click Group...
Step6. To rename Group1,
Click in cell E5, type: Fail Enter
Step7. To rename Group2,
Click in cell E12, type: Pass Enter
Step8. To move to the Design tab, on the Ribbon,
Click the Design tab
Step9. To add the subtotal, in the Layout group,
Click, Click Show all Subtotals at Bottom of Group
NOTE: We created this PivotTable from an Excel range, not an Excel table. For this reason, if scores are added, the PivotTable will have to be recreated.
Step10. To save the workbook, press: