Excel Chapter 5 Using a Master Sheet to Create a Multiple-Sheet Workbook Note: To complete this assignment, you will be required to use the Data Files. The data file is provided at the bottom of the assignment instructions. Problem: You are part of a task force assessing the classroom capacities of the middle schools in your district. You have been charged with creating a master worksheet for the district and separate worksheets for each of the two middle schools. The middle school worksheets should be based on the district worksheet. Once the worksheets have been created, the middle school data can be entered into the appropriate worksheets, and the district worksheet will reflect district-wide information. The district worksheet appears as shown in Figure 5–74. Figure 5–74 Perform the following tasks: 1. Run Excel. Open the workbook Lab 5 – 1 Gladstone Schools provided below. Save the workbook using the file name Lab 5 – 1 Gladstone Schools Complete. 2. Add two worksheets to the workbook after Sheet1 and then paste the contents of Sheet1 to the two empty worksheets. 3. From left to right, rename the sheet tabs District, Hillview Middle School, and McCarty Middle School. Color the tabs as shown in Figure 5–74. On each of the school worksheets, change the title in cell A2 to match the sheet tab name. On each worksheet, fill the range A2:F3 to match the color of its sheet tab. Enter the data in Table 5–8 into the school worksheets. Table 5–8 Middle School Classroom Capacity Figures School Grade # of Students # of Rooms # of Rooms over Capacity # of Rooms under Capacity Hillview 6 Middle School 180 8 2 0 7 188 8 8 0 8 145 7 0 7 McCarty 6 Middle School 124 5 4 0 7 132 6 0 0 8 118 5 2 1 4. 5. On the two school worksheets, calculate Average Students per Room in column D and totals in row 9. 6. On the District worksheet, use the SUM function, 3-D references, and copyand-paste capabilities of Excel to populate the ranges B6:C8 and E6:F8. First, compute the sum in cells B6:C6 and E6:F6, and then copy the ranges B6:D6 and E6:F6 through ranges B7:C8 and E7:F8 respectively. Finally, calculate average students per room for the district for each grade level, and for the district as a whole. 7. Use the Cell Styles button (Home tab | Styles group) to create a new cell style named My Title. Use the Format button (Styles dialog box) to create a format. Use the Font sheet (Format Cells dialog box) to select the Britannic Bold font, a font size of 22, and a white font color. Check only the Alignment and Font check boxes in the Style dialog box. 8. Select cells A2:A3 on the District worksheet. Select all the worksheets. Apply the My Title style to the cell. 9. Using Figure 5–74 as a guide, add borders to the worksheets. The borders should be the same on all worksheets. 10. Select the District worksheet. Create a 3-D pie chart using the range A6:B8. Edit the title to match Figure 5–74. Apply the Chart Style 3 to the chart. 11. Move the chart to the right of the data. Right-click the pie to display the shortcut menu and then click ‘Format Data Series’ to open the Format Data Series task pane. Set the Pie Explosion to 10% to offset all of the slices. 12. Select the chart area and display the Format Chart Area task pane. Set the X rotation to 100°. 13. Use the Chart Elements button to display the Data Labels submenu. Click More Options. Select only the Value and ‘Show Leader Lines’ options. Choose the Outside End label position and adjust the labels as necessary to display the leader lines. 14. Enter the text Prepared by followed by your name in the header, on the left side. 15. Save the workbook. Submit the revised workbook in Canvas Gladstone School District Capacity Evaluation # of Grade Level Students Grade 6 Grade 7 Grade 8 Total # of Rooms Capacity is 22 students per room # of # of Average Rooms Rooms Students over under per Room Capacity Capacity Excel Chapter 5 Using a Master Sheet to Create a Multiple-Sheet Workbook Note: To complete this assignment, you will be required to use the Data Files. The data file is provided at the bottom of the assignment instructions. Problem: You are part of a task force assessing the classroom capacities of the middle schools in your district. You have been charged with creating a master worksheet for the district and separate worksheets for each of the two middle schools. The middle school worksheets should be based on the district worksheet. Once the worksheets have been created, the middle school data can be entered into the appropriate worksheets, and the district worksheet will reflect district-wide information. The district worksheet appears as shown in Figure 5-74. Figure 5-74 Autasun Herre Pegelou Forsi XO Trebuchet MS. Copy Woman! Labs-1 Gleton Schools Complete Rries View Tell me what you want to do Care Merye & Carter. $. * Conditional Fort Col Parting The Styles Algun தங்க ) RN o Delete com Clear Tad E G H 1 1 2 Gladstone School District Capacity Evaluation 3 Gladstone Middle School Students by Grade 263 320 Grade 6 Grade 7 * Grade 8 304 of Hof Average Rooms Rooms of #of Students over under 5 Grade Level Students Rooms per Room Capacity Capacity 6 Grade 6 304 13 23.4 6 7 Grade 7 320 14 22.9 8 8 Grade 8 263 12 21.9 2 8 9 Total 887 39 22.7 16 8 10 11 Capacity is 22 students per room 19 District He Made School Mecany Middle School G Perform the following tasks: 1. Run Excel. Open the workbook Lab 5 - 1 Gladstone Schools provided below. Save the workbook using the file name Lab 5 - 1 Gladstone Schools Complete. 2. Add two worksheets to the workbook after Sheet1 and then paste the contents of Sheet1 to the two empty worksheets. 3. From left to right, rename the sheet tabs District, Hillview Middle School, and McCarty Middle School. Color the tabs as shown in Figure 5-74. On each of the school worksheets, change the title in cell A2 to match the sheet tab name. On each worksheet, fill the range A2:F3 to match the color of its sheet tab. Enter the data in Table 5-8 into the school worksheets. Table 5-8 Middle School Classroom Capacity Figures # of Students # of Rooms School Hillview Middle School Grade 6 # of Rooms # of Rooms over Capacity under Capacity 2. 0 180 8 7 188 8 8 145 124 O Nin 0 4 O NO 6 McCarty Middle School 7 132 0 6 5 NO 00 118 1 4. On the two school worksheets, calculate Average Students per Room in column D and totals in row 9. 5. On the District worksheet, use the SUM function, 3-D references, and copy-and-paste capabilities of Excel to populate the ranges B6:C8 and E6:F8. First, compute the sum in cells B6:C6 and E6:F6, and then copy the ranges B6:06 and E6:F6 through ranges B7:C8 and E7:F8 respectively. Finally, calculate average students per room for the district for each grade level, and for the district as a whole. 6. Use the Cell Styles button (Home tab | Styles group) to create a new cell style named My Title. Use the Format button (Styles dialog box) to create a format. Use the Font sheet (Format Cells dialog box) to select the Britannic Bold font, a font size of 22, and a white font color. Check only the Alignment and Font check boxes in the Style dialog box. 7. Select cells A2:A3 on the District worksheet. Select all the worksheets. Apply the My Title style to the cell. 8. Using Figure 5-74 as a guide, add borders to the worksheets. The borders should be the same on all worksheets. 9. Select the District worksheet. Create a 3-D pie chart using the range A6:38. Edit the title to match Figure 5- 74. Apply the Chart Style 3 to the chart. 10. Move the chart to the right of the data. Right-click the pie to display the shortcut menu and then click 'Format Data Series' to open the Format Data Series task pane. Set the Pie Explosion to 10% to offset all of the slices. 11. Select the chart area and display the Format Chart Area task pane. Set the X rotation to 100°. 12. Use the Chart Elements button to display the Data Labels submenu. Click More Options. Select only the Value and 'Show Leader Lines' options. Choose the Outside End label position and adjust the labels as necessary to display the leader lines. 13. Enter the text Prepared by followed by your name in the header, on the left side. 14. Save the workbook. Submit the revised workbook in Canvas # of Gladstone School District Capacity Evaluation # of # of Average Rooms Rooms # of Students over under Grade Level Students Rooms per Room Capacity Capacity Grade 6 Grade 7 Grade 8 Total Capacity is 22 students per room Excel Chapter 5 Using a Master Sheet to Create a Multiple-Sheet Workbook Note: To complete this assignment, you will be required to use the Data Files. The data file is provided at the bottom of the assignment instructions. Problem: You are part of a task force assessing the classroom capacities of the middle schools in your district. You have been charged with creating a master worksheet for the district and separate worksheets for each of the two middle schools. The middle school worksheets should be based on the district worksheet. Once the worksheets have been created, the middle school data can be entered into the appropriate worksheets, and the district worksheet will reflect district-wide information. The district worksheet appears as shown in Figure 5-74. Figure 5-74 Autasun Herre Pegelou Forsi XO Trebuchet MS. Copy Woman! Labs-1 Gleton Schools Complete Rries View Tell me what you want to do Care Merye & Carter. $. * Conditional Fort Col Parting The Styles Algun தங்க ) RN o Delete com Clear Tad E G H 1 1 2 Gladstone School District Capacity Evaluation 3 Gladstone Middle School Students by Grade 263 320 Grade 6 Grade 7 * Grade 8 304 of Hof Average Rooms Rooms of #of Students over under 5 Grade Level Students Rooms per Room Capacity Capacity 6 Grade 6 304 13 23.4 6 7 Grade 7 320 14 22.9 8 8 Grade 8 263 12 21.9 2 8 9 Total 887 39 22.7 16 8 10 11 Capacity is 22 students per room 19 District He Made School Mecany Middle School G Perform the following tasks: 1. Run Excel. Open the workbook Lab 5 - 1 Gladstone Schools provided below. Save the workbook using the file name Lab 5 - 1 Gladstone Schools Complete. 2. Add two worksheets to the workbook after Sheet1 and then paste the contents of Sheet1 to the two empty worksheets. 3. From left to right, rename the sheet tabs District, Hillview Middle School, and McCarty Middle School. Color the tabs as shown in Figure 5-74. On each of the school worksheets, change the title in cell A2 to match the sheet tab name. On each worksheet, fill the range A2:F3 to match the color of its sheet tab. Enter the data in Table 5-8 into the school worksheets. Table 5-8 Middle School Classroom Capacity Figures # of Students # of Rooms School Hillview Middle School Grade 6 # of Rooms # of Rooms over Capacity under Capacity 2. 0 180 8 7 188 8 8 145 124 O Nin 0 4 O NO 6 McCarty Middle School 7 132 0 6 5 NO 00 118 1 4. On the two school worksheets, calculate Average Students per Room in column D and totals in row 9. 5. On the District worksheet, use the SUM function, 3-D references, and copy-and-paste capabilities of Excel to populate the ranges B6:C8 and E6:F8. First, compute the sum in cells B6:C6 and E6:F6, and then copy the ranges B6:06 and E6:F6 through ranges B7:C8 and E7:F8 respectively. Finally, calculate average students per room for the district for each grade level, and for the district as a whole. 6. Use the Cell Styles button (Home tab | Styles group) to create a new cell style named My Title. Use the Format button (Styles dialog box) to create a format. Use the Font sheet (Format Cells dialog box) to select the Britannic Bold font, a font size of 22, and a white font color. Check only the Alignment and Font check boxes in the Style dialog box. 7. Select cells A2:A3 on the District worksheet. Select all the worksheets. Apply the My Title style to the cell. 8. Using Figure 5-74 as a guide, add borders to the worksheets. The borders should be the same on all worksheets. 9. Select the District worksheet. Create a 3-D pie chart using the range A6:38. Edit the title to match Figure 5- 74. Apply the Chart Style 3 to the chart. 10. Move the chart to the right of the data. Right-click the pie to display the shortcut menu and then click 'Format Data Series' to open the Format Data Series task pane. Set the Pie Explosion to 10% to offset all of the slices. 11. Select the chart area and display the Format Chart Area task pane. Set the X rotation to 100°. 12. Use the Chart Elements button to display the Data Labels submenu. Click More Options. Select only the Value and 'Show Leader Lines' options. Choose the Outside End label position and adjust the labels as necessary to display the leader lines. 13. Enter the text Prepared by followed by your name in the header, on the left side. 14. Save the workbook. Submit the revised workbook in Canvas # of Gladstone School District Capacity Evaluation # of # of Average Rooms Rooms # of Students over under Grade Level Students Rooms per Room Capacity Capacity Grade 6 Grade 7 Grade 8 Total Capacity is 22 students per room Figure 5-74 Les Gestore School Complete Does Reis Ve Tell me what you w Payla Trebuchet MS Σεια - EN Copy Format part Conditions for Cdi Ferming Table Styles Delete forme Merye Curter. $. * да уние C B с н Gladstone School District Capacity Evaluation 3 Gladstone Middle School Students by Grade 263 320 Grade 6 Grade 7 Grade 8 304 of of Average Rooms Rooms #of #of Students over under 5 Grade Level Students Rooms per Room Capacity Capacity 6 Grade 6 304 13 23.4 7 Grade 7 320 14 22.9 8 8 Grade 8 263 12 21.9 2 9 Total 887 39 22.7 16 8 10 11 Capacity is 22 students per room District Feie Middle School Mecany Middle School 000 +
Purchase answer to see full attachment