Valerian State College
SUMMARIZING YOUR DATA WITH PIVOTTABLES
GETTING STARTED
• Open the file NP_EX19_7c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX19_7c_FirstLastName_2.xlsx by changing the “1” to a “2”.
o If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file NP_EX19_7c_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
o If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
PROJECT STEPS
1. Lael Masterson works in the Student Activities Office at Valerian State College in Illinois. Lael has started compiling information on students who are interested in helping run student organizations at Valerian State, and she needs your help completing the workbook.
Switch to the Student Representatives worksheet. Determine the base rate for the first student (which is based on the student's number of years of post-secondary education) using a function as follows:
a. Use a structured reference to look up the value in the Post-Secondary Years column. Retrieve the base rate in the table showing the base rate by post-secondary years. The formula should always use the exact range and find an approximate match.
b. Fill the function to the rest of the column, if necessary.
2. Student organizations sometimes require transportation for off-campus activities, and school policy requires students to be over 23 years old to serve as transport. Lael wants to determine how many of the active students will be eligible to transport other group members. Populate the Qualified Driver column as follows:
a. Enter a function to determine if the first student is a qualified driver. Display Yes if the student is older than 23; if not, display No.
b. Fill the function to the rest of the column, if necessary.
3. To be eligible for the leadership training program offered by the Student Activities Office, a student must have at least two years of post-secondary education or have gone through the organization finance training. Populate the Leadership Training column as follows:
a. Enter a formula using nested functions to determine if the first student is eligible for the leadership training program. Display Yes if one or both of the criteria are met. Display No if a student meets neither of the criteria.
b. Fill the formula to the rest of the column, if necessary.
4. Experienced students may serve as mentors if they are at least age 21 and have at least three years of post-secondary education. Populate the Mentor column as follows:
a. Enter a formula using nested functions to determine if the first student is eligible to serve as a mentor. Display Yes if both of the criteria are met. Display No if a student meets none or only one of the criteria.
b. Fill the formula to the rest of the column, if necessary.
5. Lael is always on the lookout for qualified students who might be interested in running for office in student groups. A student is qualified to be an officer if they have already been elected to office in a student group; and if they have not, they are qualified if they have been certified financially. Populate the Officer Qualified column as follows:
a. Enter a formula using nested functions to determine if the first student is qualified to be an officer. If the student has been previously elected (i.e., -Yes- displays in the Elected column), display Elected.
b. Otherwise, if the student is -Finance Certified-, display Yes; if not, display No.
c. Fill the formula to the rest of the column, if necessary.
6. Students who work with student organizations are also considered for employment at the Student Activities Office. Students with more than four years of post-secondary education are qualified for more complex Tier 2 jobs. Populate the final column as follows:
a. Enter Tier as the final column heading.
b. Enter a function to determine the work tier of the first student. If the student has completed at least four post-secondary years, display 2; if not, display 1.
c. Fill the function to the rest of the column, if necessary.
7. To the right of the large table, Lael has made a quick way to look up a student's name by their Student ID number. To make sure that the cell always displays a meaningful message, nest the existing function in another function to display Invalid Student ID if the VLOOKUP function returns an error result.
8. Lael wants to determine several totals and averages for active students. In the appropriate cell, enter a function to count the total number of students if they have been elected to offices in student organizations.
9. In the appropriate cell, use a function to determine the average number of post-secondary years for students who have been elected.
10. In the appropriate cell, use a function to determine the average number of years of post-secondary education for all students.
11. Switch to the Academic Groups worksheet. Below the cell labeled -Largest Academic Club, 2023:-, use a function to display the first value of the AcademicGroups table.
12. Below the cell labeled -2023 memberships in large groups:-, use a function to display the total membership in 2023 for groups with at least 40 members.
13. Lael is also planning for student groups that the office will be working with in the coming year. She decides to create a PivotTable to better manipulate and filter the student group data. Switch to the Academic PivotTable worksheet, and then create a PivotTable in the first cell based on the AcademicGroups table. Update the PivotTable as follows:
a. Change the PivotTable name to: AcademicPivotTable
b. Add rows for Activities and Group Name (in that order).
c. Add values for 2021, 2022, and 2023 (in that order).
d. Display all subtotals at the top of each group.
e. Display the report layout in Outline Form.
f. Display the appropriate field with the name 2021 Membership as a number with zero decimal places.
g. Display the appropriate field with the name 2022 Membership as a number with zero decimal places.
h. Display the appropriate field with the name 2023 Membership as a number with zero decimal places.
14. Lael wants you to summarize data for all student groups in a PivotTable. To do so, you must first update the AllGroups table. Switch to the All Groups worksheet. Update the Astronomy Society membership to 76 for the year 2023.
15. Switch to the All Groups PivotTable worksheet. Refresh the PivotTable data, and then verify that the Astronomy Society membership reflects the change you made in the previous step.
16. Match the style of the PivotTable to the one on the Activities PivotTable worksheet.
17. Add the Office field to the Filters area of the PivotTable. Display only organizations with private offices.
18. Create an Activities Slicer. Resize the slicer so that it has a height of 2.2- and a width of 3.2-. Move and size the slicer so that it covers the range F3:J14. Use the slicer to display only fraternal groups.
19. Lael also wants to summarize membership data for all organizations using a PivotChart to help determine which groups are showing the most interest from students. Switch to the Activities PivotTable worksheet. Insert a Clustered Column PivotChart and format it as follows:
a. Move and size the PivotChart so that it covers the range F3:O19.
b. Add the chart title Membership by Type to the PivotChart above the chart.
c. Display only the membership data for groups with educational, field, and fraternal activities. (This filter may be automatically applied when you create the table.)
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Student Representatives Worksheet

Final Figure 2: Academic Groups Worksheet

Final Figure 3: Academic PivotTable Worksheet

Final Figure 4: All Groups Worksheet

Final Figure 5: All Groups PivotTable Worksheet

Final Figure 6: Activities PivotTable Worksheet