Analyzing the Learning Center’s Data The Learning Center at your school has been tracking the students, including you, who attend tutoring sessions. Because you have been doing well in your Excel sessions, they have asked you to analyze the data that has been collected over the past two semesters. a. Start Excel , and then open e-Tutor . Save the file as e-Tutor_LastFirst using your last and first name. b. On the Student Data worksheet, insert a table with headers that uses the range $A$11:$G$211. With the data table selected, create named ranges using the top row as the names. c. Copy range A11:G11 , and then paste the range in cell A1 . In cell C2 , type Sophomore . In cell D2 , type Microsoft Office .d. Create an advanced fi lter using the data in range A1:G2 . Filter the list in-plac e to display the fi ltered data on the Student Data worksheet. Copy the fi ltered data, and thepaste it in cell A1 on the Filter worksheet. Resize the columns so all the data is visible. e. On the Student Data worksheet, in cell H11 , type TotalDue . In cell H12 , enter a formula that multiplies TotalSessions and Rate. Format the TotalDue column as Currency with 0 decimal places. Create a named range for the TotalDue column that uses the column heading as the name. f. Insert a slicer for the Subject field. Click Science and Math in the Subject slicer. Drag the Subject slicer so the top-left corner is in the top-left corner of J1. Drag the bottom edge of the Subject slicer to adjust the height so that the extra white space is no longer visible. Apply Slicer Style Dark 5 to the slicer. g. Use the SUBTOTAL function to complete the following. • In cell F6 , insert a formula that counts the number of cells in the LastName field thatare not empty. • In cell F7 , insert a formula that averages the cells in the Rate field. • In cell F8 , insert a formula that sums the cells in the TotalDue feld. i. Using the range $A$11:$H$211 , insert a PivotTable in cell A10 on the Analysis worksheet. j. Configure the PivotTable using the following: • Add Subject , LastName , and TotalSessions to the PivotTable. • Move Subject to the COLUMNS area. • Right-click cell H12 , point to Sort , and then sort the data in descending order. • Apply Pivot Style Dark 6 to the PivotTable. • Change the color of the table to Color 16 . • View the data in the PivotTable to answer question 1. Enter the student’s last name who attended the most tutoring sessions in cell A2, and then enter the student’s last name with the second highest number in cell B2.k. Remove LastName and TotalSessions from the PivotTable, and then modify the PivotTable so you can answer the second question. • Add the TotalDue , Date , and ClassStanding fields to the PivotTable. • Move Subject to the ROWS area. • Move ClassStanding to the FILTERS area. • Format column B in the PivotTable as Currency with 0 decimal places. • On the ANALYZE tab, modify the dates to be displayed by month. • On the DESIGN tab, modify the subtotals so they are displayed at the bottom of the group. • In cell A10 , use the Filter arrow to display data for Aug , Sep , Oct , Nov , and Dec . • View the data in the PivotTable to answer question 2. Enter the amount of revenue that was generated during the fall semester (August 25, 2014-December 31, 2014) in cell A4.l. Modify the PivotTable so you can answer the third question. • Add the FirstName field to the PivotTable. • Move FirstName to the VALUES area. • In cell B8 , use the Filter arrow to display data for Sophomore . • View the data in the PivotTable to answer question 3. Enter the total sophomores that used the Learning Center’s services in cell A6.m. Modify the PivotTable to prepare for creating a PivotChart. • Remove the FirstName field from the PivotTable. • Remove the ClassStanding fi lter, and then move ClassStanding to the COLUMNS area. • Move the Date field as the filter • In cell A10 , type Amount Due . In cell A11 , type Subject by Month .n. Using the data in the PivotTable, insert a Clustered Column PivotChart. Format the PivotChart as follows. • Move the PivotChart to a new sheet named Revenue Chart . • Add an Above Chart title. Replace Title with Revenue Generated by Class and Subject . • Apply Style 8 to the PivotChart, and then change the color to Color 16 .o. Complete the Documentation worksheet according to your instructor’s direction. Insert the filename in the left custom footer section of the Header/Footer tab in the Page Setup dialog box on all worksheets in the workbook. p. Click Save , close Excel, and then submit the fi le as directed by your instructor.