EXCEL Problem
Peg Henderson is an assistant dean at the College of Business Administration (CBA). She uses Excel daily for a variety of tasks, including tracking student internships and alumni. For this project, Peg wants you to create an Excel table from information about current students and then analyze this data. Complete the following.
1. Open the Student workbook located in the Excel5 ? Case1 folder included with your Data Files, and then save the workbook as Student Data .
2. In the Documentation worksheet, enter your name and the date.
3. In the CBA Data worksheet, create an Excel table. Format the tabl e with Table Style Medium 7, change the GPA data to the Number format showing two decimal places, and then change the Scholarships data to the Accounting format showing no decimal places. Rename the table as StuData .
4. Make a copy of the CBA Data workshe et, and then rename the copied worksheet as Sort by Major . ( Hint : Press the Ctrl key as you drag and drop the sheet tab to the right of the CBA Data sheet tab to make a copy of the worksheet.)
5. Sort the data in the StuData table in ascending order by Ma jor, and then in descending order by GPA.
6. Filter the StuData table to remove all undecided majors.
7. Insert a Total row in the StuData table that shows the number of students in the Last Name column, and the average GPA in the GPA column. Change the Total row label to Average and remove the entry in the Class column of the Total row.
8. Split the Sort by Major worksheet into two horizontal panes. Place the split bar two rows above the bottom row of the worksheet. In the top pane, display the student data. In the bottom pane, display only the Total row.
9. Make a copy of the CBA Data worksheet, and then rename the copied worksheet as Filter by Class . Filter the StuData table to display only those students who have a GPA greater than 3.00.
10. Insert a Class slicer to the right of the StuData table. Resize the slicer's height to 1.7" and its width to 1.2", and then format the slicer with the style that best matches the style of the Excel table.
11. Use the Class slicer to further filter the StuData ta ble to display only Juniors and Seniors. Sort the filtered data in ascending order by Class and then in descending order by GPA.
12. Make a copy of the CBA Data worksheet, and then rename the copied worksheet as Subtotals . Convert the table to a normal range because the Subtotal command cannot be used with an Excel table. Use the Subtotal command to display the Average GPA for each Major in the GPA column.
13. Based on the data in the CBA Data worksheet, create a PivotTable in a new worksheet that counts the number of students in each class and major. In the Value Settings dialog box, change the Custom Name to Number. Apply Pivot Style Medium 14. Rename the worksheet as PivotTable by Class and Major .
14. In the PivotTable b y Class and Major worksheet, insert a PivotChart with the Clustered Column chart type. Place the PivotChart to the right of the PivotTable. Filter the PivotChart to exclude the Freshman field. Insert a descriptive title. Remove the legend. Change the fill colors of the bars to match the PivotTable style.
15.Based on the data in the CBA Data worksheet, create a PivotTable that displays the average of GPA and number of students by Residence and Major in a new worksheet. Place Residence in the FILTERS area, and place Major in the ROWS area. Apply the Pivot Style Dark 7 to the PivotTable. Rename the worksheet as PivotTable by Major .
16. In the PivotTable, change the Residence filter to show In state students.
17. Save the workbook, and then close it.
Attachment:- StudentData.xlsx