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. 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. 2. In the Documentation worksheet, enter your name and the date.

3. 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. 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. 5. Sort the data in the StuData table in ascending order by Ma jor, and then in descending order by GPA.

6. 6. Filter the StuData table to remove all undecided majors.

7. 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. 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. 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. 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. 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. 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. 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. 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. 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. 16. In the PivotTable, change the Residence filter to show In state students.

17. 17. Save the workbook, and then close it.

CBA Author Date Purpose To track data on active students at CBA Data Definition Table Field Description Data Type Notes SID Last Name Gender Major GPA Scholarships Residence Class Student ID Student's last name Student's gender Student's major Student's GPA Student's receiving scholarship Student's residence Student's class Number Text Text Text Number Number Text Text Male, Female Accounting, Finance, Marketing, Management, Undecided Format to two decimal places Accounting format, no decimal places In state, Out of state Freshman, Sophomore, Junior, Senior SID Last Name 101 Mccawley 102 Tatrai 103 Amukamara 104 Simonsen 105 Larralde 106 Shearer 107 Otto 108 Bellof 109 Brizendine 110 Breitenbach 111 Fuller 112 Pomerenke 113 Johnson 147 Lopez 118 Thomas 137 Luna 117 Hull 115 Loertscher 119 Richardson 120 Romero 121 Agbebaku 122 Bocskay 123 Connolly 124 Cruz 125 Jefress 126 Kang 127 Kreisher 128 Lesinski 129 Rich 130 Santillan 114 Nkol 132 Llanes 133 Brezina 134 Thurow 135 Burns 136 Lucchesi 175 Davey 138 Schornack 139 Gomez 140 Skaar 141 Rubino 142 Decarlo 143 Mcclelland 144 Supan 145 Winkler 146 Gallegos 131 Sandoval 148 Waldron 149 Perkins 150 Sapp 151 Licano 152 Gonzalez 153 Matozevich 154 Bowen 155 Staffler 156 Rawls 157 Porter 158 Trevino 159 Rivera 160 Walker 161 Mcdaniel 162 Christ 163 Danielson 164 Starkey 165 Sinclair 166 Strickman 167 Holladay 168 Bishop 169 Strocks 170 Peoples 171 Gadberry 172 Auga 173 Price 174 Scroggins 116 Stone 176 Nguyen 177 Painter 178 Roe 179 Qi Gender Male Female Male Female Male Male Female Female Male Female Female Male Male Male Male Female Male Male Male Female Male Male Female Female Female Female Female Female Male Female Male Female Male Female Female Male Female Male Female Male Female Female Female Male Female Male Female Male Female Female Female Female Male Female Female Female Male Female Female Male Male Male Female Male Female Female Female Female Male Male Male Male Male Female Male Male Female Male Female Major Marketing Accounting Undecided Marketing Undecided Management Undecided Marketing Management Finance Undecided Management Undecided Management Marketing Accounting Management Marketing Marketing Accounting Undecided Accounting Undecided Undecided Finance Marketing Marketing Management Marketing Marketing Management Undecided Undecided Accounting Management Finance Management Management Marketing Marketing Management Undecided Marketing Marketing Management Undecided Finance Undecided Marketing Management Marketing Marketing Marketing Marketing Accounting Marketing Undecided Undecided Finance Management Management Undecided Management Management Management Undecided Undecided Undecided Management Undecided Finance Undecided Undecided Undecided Management Accounting Undecided Finance Finance GPA Scholarships 2.69 2.99 2.95 2.86 2.01 2.32 2.39 2.88 2.28 3.27 2.98 2.88 2.78 2.96 2.98 3.23 2.97 3.25 3.85 2.96 2.99 2.27 3.18 2.07 2.29 2.65 3.27 2.85 2.92 2.26 3.59 2.07 3.26 2.3 2.66 2.32 3.81 2.61 2.57 2.86 2.59 2.95 2.65 2.66 2.96 2.96 3.82 2.93 2.57 2.91 2.03 2.97 2.95 3.54 2.07 2.4 2.69 2 2.67 2.85 2.99 3.58 2.98 2.9 2.58 2.89 3.48 3.5 2.99 3.22 2.37 2.99 2.57 2.04 3.9 2.6 2.32 2.68 2.07 Residence Out of state In state In state Out of state Out of state In state In state In state In state In state In state In state In state 2000 In state 2000 Out of state 2000 In state In state 2000 In state In state Out of state Out of state Out of state Out of state In state In state Out of state In state Out of state In state In state 2000 In state Out of state Out of state In state Out of state In state 2000 Out of state Out of state In state In state In state In state Out of state In state In state Out of state 2500 Out of state In state In state Out of state In state In state Out of state Out of state Out of state Out of state In state Out of state In state In state Out of state In state In state Out of state In state Out of state Out of state In state Out of state In state Out of state In state Out of state Out of state 2500 Out of state In state Out of state Out of state Out of state Class Senior Sophomore Freshman Senior Sophomore Junior Freshman Senior Junior Sophomore Freshman Senior Freshman Senior Junior Junior Sophomore Senior Senior Sophomore Freshman Senior Freshman Freshman Junior Junior Sophomore Junior Senior Junior Senior Freshman Freshman Senior Senior Junior Senior Sophomore Junior Senior Sophomore Freshman Senior Junior Senior Freshman Junior Freshman Junior Junior Sophomore Junior Sophomore Junior Sophomore Sophomore Freshman Freshman Sophomore Senior Senior Freshman Junior Junior Senior Freshman Freshman Freshman Junior Freshman Sophomore Freshman Freshman Freshman Junior Senior Freshman Senior Junior 180 Carrillo Male Marketing 2.07 In state Sophomore