Answer
At the end of this step, your GPA Stats worksheet (derived from the Frat Recruits worksheet) should look like so:
Explanation
For the sake of this sneak peek, we'll be starting at Step #13.
13. Go back to the Frat Recruits worksheet.
Click on the Frat Recruits worksheet.
14. Create a PivotTable with the following characteristics:
- Created the PivotTable on a new worksheet named GPA Stats, positioned after the SortFilter worksheet.
- For each State, then Year, display the Average GPA by FTB (For the Boys) Rating.
- Reduce the Average GPA values to have 1 decimal point and only include the Grand Totals at the bottom of the PivotTable.
- Style the PivotTable with "White, Pivot Style Light 1".
- Move Ohio to the top.
- Alter Average GPA to appear as Mean GPA.
- Group the FTB (For the Boys) Rating values in groups of 2 so that 3-4, 5-6, 7-8, and 9-10 are together.
- Do not include recruits with a Fake vibe in the display.
- Every state except for Illinois should be selected.
Highlight the data in cells A1:G71 and then go to the Insert ribbon and select PivotTable.
In the pop-up window that appears, select New worksheet.
The new worksheet that gets created needs to be repositioned after the SortFilter worksheet...
...and additionally needs to be renamed to "GPA Stats". To rename it, right click on the worksheet and select "Rename".
Next, we must display each State...
...and each Year within the states...
...then display the Average GPA...
...by FTB (For the Boys) Rating.
Yes! That would've worked too, but to keep this PivotTable aesthetically pleasing, I placed the State and Year in Rows and FTB (For the Boys) Rating in Columns.
If I did it the other way around, it'd look like this:
Next, let's reduce the decimal point to 1. To do this, click the "i" icon next to the Average of GPA and select "Number...".
In the pop-up window, select Number then enter 1 into decimal places.
To see more content, get your Excel Cram Kit (Test #2) with the button below!