ilter, List and Sorting: (18pts)
- Open SortFilterData.
- In Wages worksheet:
a. Display only Winter employees from the Tours department. (1pt)
b. Sort the results by Status alphabetically and then by Hourly Wage with the most expensive employees first. (3pts)
- In the Roster worksheet:
a. Create a table from the data in A1:H112 and add yourself to the list with your LastName and FirstName; You work in Marketing, 1st shift and you make $33,333; you were hired yesterday, choose your own marital status. (1pts)
b. Display the Roster employees who make anywhere from $30,000 thru $40,000.Sort the results by marital status in descending order and if there are duplicates, then by salary in descending order, and if there are still duplicates, by Last Name in descending order. Print (or upload a screenshot) and label your printout. (3pts)
c. Add a total row to the table that computes ONLY the Average salary of the employees displayed in the previous step. (2pts)
d. In B120 enter the label Last Name and in C120 enter the label Salary. In C121 create a function that looks up the name entered in B121 and returns their salary in C121. Test the function by entering Metzger in B121. (2pts)
e. Print and label your printout (or upload a screenshot).
- In the TV Shows worksheet:
a. Use an Advanced Filter to filter the data to A40 in order to display the TV Shows that were 60 minutes in length OR were a Comedy that received a Rating of at least 4.(3pts)
5. In the Cruises worksheet
a. Sort the data by Accommodation category in Ascending Order (1pt)
b. Create Subtotals below the data for each Accommodation category that totals the # of Rooms Available and the Revenue. Display only the 2nd level detail so that only category totals and the Grand Total display. (2pts)
6. Save and upload the file SortFilterData to D2L!
School Stats– 22pts
- Open School Stats. Save the file as XXX School Stats. Create a header in each sheet (current and new sheets) of the workbook that includes your name, centered. (1 pt)
- Create a table from the data A3:G20. Row 3 should be a header row. Add data validation to column G that only allows choices of Freshman, Sophomore, Junior or Senior. Add an appropriate alert message that warns the user if they enter an invalid response. Add a new record into the table for yourself in row 20 – (you are a Junior Business major) make up your points data. (4 pts)
- Create a pivot table in a new sheet, named Pivot Data, based off the data in range A3:G20. The columns should be the Year in School, the rows should be the Majors, and the data should be the average GPA. (3 pts)
- Format the data so that GPA scores only show two decimals. (1 pt) Manually adjust the columns so that the Sophomore data is to the immediate right of Freshman data and so forth. (BONUS pt)
- Add Last Name as the inner field row label after Major. (1 pt)
- Format the PivotTable as Light Orange, Pivot Style Medium 7. Remove the grand totals for rows. Remove the column and row label headings and PRINT this sheet. (or upload a screenshot) (3 pts)
- Create a pivot chart in a new sheet, named Pivot Chart, based off of the pivot table data. Modify the data via the PivotTable created in step 3 by adding slicers for the Year in the School & Major. The chart should only display Junior and Senior data for the Business and Liberal Arts majors. (see illustration below) Format the Year in School slicer as Ice Blue, Slicer Style Dark 1 and the Major slicer as Lavender, Slicer Style Dark 4. (6 pts)
- Name the chart title, “Average GPA by Major by Year in School” and format the chart as Chart Style 5. Add a data table with legend keys below the chart, then PRINT the chart and the related PivotTable (or upload screenshots). (3 pts)
- Save and upload the file to D2L.