PROJECT DESCRIPTION
Theresa Pratt works in the business office at Mills College, a technical college in Council Bluffs, Iowa. She has created a workbook to store and analyze registration and employee data. The workbook is composed of several individual worksheets. Theresa has asked you to help her work with table data, create advanced formulas, and build macros.
GETTING STARTED
Download the following file from the SAM website:
NP_Excel2013_CS_T5-8_P1a_FirstLastName_1.xlsm
Open the file you just downloaded and save it with the name:
NP_Excel2013_ CS_T5-8_P1a_FirstLastName_2.xlsm
Hint: If you do not see the .xlsm file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.
To complete this Project, you will also need to download and save the following support files from the SAM website:
support_NP_E13_CS_T5-8_P1a_Iowa.xlsx
support_NP_E13_CS_T5-8_P1a_Nebraska.xlsx
If you see a Message Bar with a security warning at the top of the Excel window, click the Enable Content button in the Message Bar to enable the macros contained in the file (refer to Figure 1 below).
Figure 1: Security Warning Message Bar 
To complete this Project, you will need to display the Developer tab. To add this tab to the Excel Ribbon, click on the File tab to open Backstage view, and then click the Options button. In the Excel Options dialog box, click the Customize Ribbon option and click the Developer check box (see Figure 2 on the following page). Click the OK button to close the Excel Options dialog box, and then confirm that the Developer tab appears in the Excel Ribbon.
With the file NP_Excel2013_ CS_T5-8_P1a_FirstLastName_2.xlsm still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
Figure 2: Customize the Ribbon Section of the Excel Options Dialog Box
PROJECT STEPS
Switch to the Summary worksheet. Add a comment to the merged range B5:C5 that reads Use these figures in annual performance review. (including the period). Erase any other text that appears in the comment, including your name.
In cell B7, enter a formula using the SUM function that totals the value of cell B7 from each of the following workbooks: Qtr1, Qtr2, Qtr3, Qtr4. Copy the formula from cell B7 into the range B7:C9.
Add defined names to the Summary worksheet as described below:
Select the range A7:C9 and create names for the selection using the left column option.
Add the defined name Iowa_Total to cell B10.
Add the defined name Nebraska_Total to cell C10.
Group the Summary, Qtr1, Qtr2, Qtr3, and Qtr4 worksheets. Format the grouped worksheets as described below:
Format cell A1 in the grouped worksheets using Times New Roman font, with the 24 point font size and the bold font style.
Format the range B7:C10 in the grouped worksheets with the Comma number style and 0 decimal places.
In the center Header section of the grouped worksheets, add a Header & Footer Element that will display the Sheet Name of each worksheet.
Ungroup the worksheets.
Make a copy of the Qtr4 worksheet and name it Qtr4-Revised. Move the Qtr4-Revised worksheet between the Qtr4 worksheet and the Calls worksheet. Switch to the Qtr4-Revised worksheet. Edit the content of the merged range B5:C5 to read Quarter 4 - Revised. (Hint: Do not use a period in the name of the new worksheet or in the range B5:C5.)
View the Qtr4 worksheet in Normal view and add external references to the worksheet as described below:
External reference to support_NP_E13_CS_T5-8_P1a_Iowa.xlsx
Open the file support_NP_E13_CS_T5-8_P1a_Iowa.xlsx available for download from the SAM website.
In cell B7 of the Qtr4 worksheet, create a formula that is an external reference to cell E6 in the Quarter 4 worksheet in the support_NP_E13_CS_T5-8_P1a_Iowa.xlsx file.
Modify the formula in cell B7 of the Qtr4 worksheet to be a relative reference to cell E6 in the support_NP_E13_CS_T5-8_P1a_Iowa.xlsx file.
Copy the formula from cell B7 in the Qtr4 worksheet to the range B8:B9 in the Qtr4 worksheet.
Close the file support_NP_E13_CS_T5-8_P1a_Iowa.xlsx.
External reference to support_NP_E13_CS_T5-8_Nebraska.xlsx
Open the file support_NP_E13_CS_T5-8_P1a_Nebraska.xlsx available for download from the SAM website.
In cell C7 of the Qtr4 worksheet, create a formula that is an external reference to cell E6 in the Quarter 4 worksheet in the support_NP_E13_CS_T5-8_P1a_Nebraska.xlsx file.
Modify the formula in cell C7 of the Qtr4 worksheet to be a relative reference to cell E6 in the support_NP_E13_CS_T5-8_P1a_Nebraska.xlsx file.
Copy the formula from cell C7 in the Qtr4 worksheet to the range C8:C9 in the Qtr4 worksheet.
Close the file support_NP_E13_CS_T5-8_P1a_Nebraska.xlsx.
Switch to the Calls worksheet. Create a table based on the range A1:E48 that contains headers, and then update it as described below:
Change the name of the table to Qtr1Calls.
Apply the Table Style Medium 2 table style to the table.
Edit the record shown in row 43 of the Qtr1Calls table so that it has a State field value of Iowa.
Switch to the EmployeeSubtotal worksheet. Freeze the top row (row 1) of the worksheet.
Insert subtotals into the EmployeeSubtotal worksheet as described below:
Sort the table (in the range A1:G101) first in ascending order by the Dept field values and then in ascending order by the Last Name field values.
Convert the table into a range.
Insert subtotals into the range A1:G101 at each change in the Dept field. The subtotals should use the SUM function to total the values in the Current Salary column. The check boxes for the subtotals options replace current subtotals and summary below data should be checked by default.
Switch to the EmployeeList worksheet and complete the following actions:
Apply a conditional formatting rule to the range A3:A102 that will highlight any Duplicate values using a conditional formatting color option of your choice. (Hint: Two values in the table will be highlighted.)
Change the Emp ID field value for the employee with the last name Ralston to 1011, so that the conditional formatting rule no longer highlights any value in column A. Do not clear the conditional formatting rule from the worksheet.
Sort the table by the Job Status field value in ascending order, then by the Pay Grade field values in ascending order, and finally by the Current Salary field value from Largest to Smallest.
Add a hyperlink to cell H2 in a section of the workbook as described below:
The hyperlink should link to cell N1 in the EmployeeBenefits worksheet.
The hyperlink should have the display text Current Salary. (Hint: Do not include the period.)
The hyperlink should include the ScreenTip Click to view additional Employee Benefits. (including the period).
In cell K3, enter a formula using the COUNTIF function that will count the number of employees with a Job Status field value of FT. Use a structured reference to the Job Status column as the range parameter in your formula.
In cell K4, enter a formula using the AVERAGEIF function that will determine the average salary (based on the Current Salary field values) for the employees with a Job Status field value of FT. Use structured references to the Job Status column and Current Salary column in your formula.
Create a new PivotTable (not a recommended PivotTable) based on the EmployeeList table. The PivotTable should be placed in a new worksheet titled EmployeeListPivot between the EmployeeSubtotal and EmployeeList worksheets. Update the PivotTable as described in the following steps so that it matches Figure 3 below:
Add the Pay Grade field and the Dept field (in that order) to the ROWS area.
Add the Job Status field to the COLUMNS area.
Add the Current Salary field to the VALUES area. (Hint: This field's name will automatically be updated to display as Sum of Current Salary.)
Update the name of the Current Salary field (in the VALUES area of the PivotTable) to display as Total Salaries. Update the number format of this field to display in Accounting number format with 0 decimal places.
Figure 3: PivotTable on EmployeeListPivot Worksheet
Switch to the EmployeeBenefits worksheet. Add a Total Row to the EmployeeBenefits table. Using the total row, add the following calculations to the table:
In cell I102, use the AVERAGE function to determine the average of the Current Salary column values.
In cell L102, use the AVERAGE function to determine the average of the Age column values.
In cell M102, use the AVERAGE function to determine the average of the Years Service column values.
In cell Q102, remove any calculation associated with the Vision Plan Costs column.
Change the text in cell A102 to read Average.
In cell N2, enter a formula using the IF and OR functions to determine whether an employee will earn a bonus vacation week. A bonus vacation week is awarded to employees based on their job status (shown in Column G) or years of service (shown in column M). Use structured references and the following parameters when creating this formula:
If the employee's Job Status field value is equal to FT or an employee's Years Service field value is greater than 20, the formula should return the value 1 (indicating 1 bonus week of vacation).
If neither of those conditions are true, the formula should return the value 0 (indicating 0 bonus weeks of vacation).
The formula should automatically fill into the range N2:N101. (Hint: If the formula does not fill into that range, copy the formula from cell N2 to the range N3:N101.)
In cell O2, enter a formula using nested IF functions to calculate the maximum amount of 401(k) contributions that the company will match for an employee. The company's 401(k) matching plan is based on an employee's job status (shown in column G) and years of service (shown in column M). Use the following parameters and noted reference types when creating this formula:
If an employee's Job Status field value is equal to FT, the employee is eligible for the 401(k) matching program. Use a structured reference to the Job Status field in the formula.
If the FT employee also has a Years Service field value greater than or equal to 15, the Max 401(k) Company Match amount is calculated by multiplying the employee's Current Salary by the value in cell T3. Use structured references to the Years Service and Current Salary fields and an absolute reference to cell T3 in the formula.
If the FT employee does not have a Years Service value greater than or equal to 15, the Max 401(k) Company Match amount is calculated by multiplying the employee's Current Salary by the value in cell T4. Use structured references to the Years Service and Current Salary fields and an absolute reference to cell T4 in the formula.
If the employee's Job Status field value is not equal to FT, the formula should return a value of 0.
The formula should automatically fill into the range O2:O101. (Hint: If the formula does not fill into that range, copy the formula from cell O2 to the range O3:O101.)
In cell P2, enter a formula using the VLOOKUP function to determine the cost of each employee's medical plan as described below:
Use a structured reference to the Medical Plan field as the Lookup_value parameter value.
Use the defined name Medical_Premium (which represents the range S8:T15) as the Table_array parameter value.
Use 2 as the Column_index_num parameter value.
Use FALSE as the Range_Lookup parameter value.
The entire VLOOKUP function should be multiplied by 12 (to convert the monthly premium cost to a yearly premium cost).
The formula should automatically fill into the range P2:P101. (Hint: If the formula does not fill into that range, copy the formula from cell P2 to the range P3:P101.)
In cell Q2, nest the existing formula in the cell into an IFERROR function. The IFERROR formula should display the message Invalid Plan Code if the HLOOKUP function returns an error value.
The updated formula should automatically fill into the range Q2:Q101. (Hint: If the formula does not fill into that range, copy the formula from cell Q2 to the range Q3:Q101.)
Switch to the WorkforcePivot worksheet. Apply the Pivot Style Medium 2 PivotTable style to the PivotTable.
In the WorkforcePivot table, modify the Years Service field (displaying as Sum of Years Service) in the VALUES area as described below:
Change the summary function for the field to Average.
Change the custom name of the field to Average Years of Service. (Hint: If you select a summary function after defining the custom name for this value, confirm that the custom name you defined did not change. Do not include the period in the custom name.)
Change the number format of the column to display using the Number format with 1 decimal place.
Add the Pay Grade field to the FILTERS area of the PivotTable. Filter the table to only display records where the Pay Grade field value equals B.
Add a slicer to the PivotTable based on the Gender field as described below:
Resize and reposition the Gender slicer so that the top-left corner of the slicer appears in cell E3 and the bottom-right corner appears in cell F9.
Using the slicer, filter the table to only display records with a Gender field value equal to F.
Switch to the RegistrationEntry worksheet and add a Data Validation rule to cell B9 as described below:
The Data Validation rule should allow values from a List whose source is the range G4:G6. The list should ignore blank and display as an in-cell dropdown.
The Input Message title should be Select Dorm and the Input Message should be Select a Dorm from the dropdown list. (including the period).
The Error Alert title should be Incorrect Dorm and the Error Message should be Please select a valid Dorm. (including the period).
Switch to the Visual Basic Editor and edit the RegistrationEntry macro so that the Range("B3:B10").Select command will select the range B3:B11. Switch to the RegistrationRecord worksheet and run the macro to confirm that the macro copied a record with a value in the Registration Date field value into the table. (Hint: The shortcut to run this macro is ctrl+k.)
Switch back to viewing the RegistrationEntry worksheet. Add a macro button to the worksheet in the range A12:B13 that is linked to the RegistrationEntry macro in the worksheet. Change the name of the button to Record Registration.
Protect the RegistrationEntry worksheet without a password. Do not change any of the default protection options for the worksheet.
