Assignment 2: Advanced Excel Functionality
Objective: After the completion of this assignment, you will be able to:
- Employ the v-lookup function to merge data from multiple spreadsheets into a single, useable worksheetUtilize pivot tables to synthesize data from a spreadsheetCalculate various metrics using formulasMerge and Unmerge cellsConvert text in a single cell into multiple cellsUse the concatenate function to merge data from multiple cells into a single cell
Background: Technology is key to data management and reporting, and Excel is an indispensible tool which allows for the manipulation of data and complex data analysis. This assignment assumes you have a basic working knowledge of Excel and will explore more advanced features that will greatly expand your ability to utilize spreadsheets in the context of HR administration.
Instructions: View the tutorial labeled â€œAdvanced Excelâ€ (linked here and housed in the â€œTutorials Folderâ€ on the course content page). Then complete the following tasks within the spreadsheet:
- 1.On the â€œPersonal Informationâ€ worksheet: Concatenate the two name fields into a single field so that name is displayed as Last Name, First Name. Copy and â€œpaste as valuesâ€ and delete the original name fields.2. On â€œSalaryâ€ Worksheet: Compute hourly rates for all employees listed with an annual salary (hrly rate = salary/2080). Replace salary values with hourly (retain only one pay rate field).3.On â€œSalaryâ€ Worksheet: Compute the tenure of each employee from the Hire Date field.4.On â€œAddressâ€ Worksheet: generate an email for each employee consisting of EID@companyx.com. Retain only one email field.5.On â€œAddressâ€ Worksheet: use the â€œconvert text to columnsâ€ to divide location field into city and state and transfer to City and State fields in address block. Create fictitious street address and â€œdrag downâ€ to populate for all; do the same for zip code.6.Create a copy of the â€œPersonal Informationâ€ worksheet and rename it â€œMaster.â€7.Using â€œV-Lookupâ€ function, bring the following fields from the other worksheets into the â€œMasterâ€ worksheet: Pay Rate; Tenure; 2013 Bonus; Job Name; Email; Street Address; City; State; and Zip.8.On â€œMasterâ€ worksheet: select all and create a separate pivot table for each of the following:a.Demographics: Include employee counts by gender and ethnicity. Rename the worksheet with the pivot table â€œDemographics Summary.â€b.Salary by Job: Include employee counts and average tenure, average salary and average bonus by job title. Rename the worksheet with the pivot table â€œJob Summary.â€
Deliverable: Submit your spreadsheet (a single excel file) as an attachment in the assignment dropbox by the due date. In total, you should have 7 active worksheets (4 original and 3 new) in your file. Retain a copy of your file for use in assignment 3.