USING MICROSOFT EXCEL 2016 Independent Project 4-5 (Mac 2016) Independent Project 4-5 (Mac 2016 Version) Boyd Air is monitoring flight arrival status as well as capacities. Before formatting the data as an Excel table, you will export it as a text file for use in the reservation software. You will filter the data in the table, build a PivotTable, and create a PivotChart. This project has been modified for use in SIMnet. Skills Covered in This Project • • • Step 1: Download start file Format data as an Excel table. Use a number filter in a table. Set conditional formatting with an icon set. • • • Filter data by cell icon. Create and format a PivotTable. Create and format a PivotChart. This image appears when a project instruction has changed to accommodate an update to Microsoft Office 365. If the instruction does not match your version of Office, try using the alternate instruction instead. 1. Open the BoydAir-04 start file. 2. Click the Enable Editing button. 3. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. 4. Rename the sheet tab Stats. 5. Select cell A4 and format the data as an Excel table using Green, Table Style Medium 21. Select cell A4 and format the data as an Excel table using Style Medium 21. 6. Copy the Stats sheet to the end and name the copy PM Flights. 7. Select the PM Flights sheet and use a Greater Than filter to display flights with a departure time after 12:00 PM (Figure 4-102). 8. Select the Stats worksheet, select cells I5:I32, and set conditional formatting to use 3 Stars from the Icon Sets in the Ratings group. 9. Build a two-level Custom Sort for the Capacity column to sort by icon. Show the solid gold star at the top, followed by the half-gold star. The silver star will default to the bottom (Figure 4103). Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 4/3/18 Page 1 USING MICROSOFT EXCEL 2016 Independent Project 4-5 (Mac 2016) 10. Select the Stats worksheet, select cells A4:I32, and create a PivotTable on a new worksheet to display average of capacity by origin (Figure 4-104). 11. Rename the sheet PivotTable&Chart. 12. Select cell B3 in the PivotTable and use Field Settings to set a Number Format of Percentage with two decimal places. Edit the Custom Name to display Average Capacity. 13. Add the Passengers field to the PivotTable VALUES area with a sum calculation. Edit the field settings to display # of Passengers as the custom name. Set the number format to Number with zero decimals and a thousand’s separator. Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 4/3/18 Page 2 USING MICROSOFT EXCEL 2016 Independent Project 4-5 (Mac 2016) 14. Use White, Pivot Style Light 8 the PivotTable and show banded columns and rows. Use Pivot Style Light 8 for the PivotTable and show banded columns and rows. 15. Add a 3-D Pie PivotChart to the sheet and position the chart object to start in cell E3. Size the chart to reach cell N22. Step 2: Upload & Save Step 3: Grade my Project Select A4:C9 in the PivotTable and add a 3-D Pie Chart to the sheet. Position the chart object to start in cell E3. Size the chart to reach cell N22. 16. Select the legend in the chart and set its font size to 11 from the Home tab. 17. Show Data Labels on the chart positioned at the Inside End. Select a data label and format all labels from the Home tab as bold and 10 pt. (Figure 4-105). 18. Save and close the workbook. 19. Upload and save your project file. 20. Submit project for grading. Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 4/3/18 Page 3 USING MICROSOFT EXCEL 2016 Independent Project 4-5 Independent Project 4-5 Boyd Air is monitoring flight arrival status as well as capacities. Before formatting the data as an Excel table, you will export it as a text file for use in the reservation software. You will filter the data in the table, build a PivotTable, and create a PivotChart. This project has been modified for use in SIMnet. Skills Covered in This Project • • • Format data as an Excel table. Use a number filter in a table. Set conditional formatting with an icon set. • • • Filter data by cell icon. Create and format a PivotTable. Create and format a PivotChart. This image appears when a project instruction has changed to accommodate an update to Microsoft Office 365. If the instruction does not match your version of Office, try using the alternate instruction instead. Step 1: Download start file 1. Open the BoydAir-04 start file. 2. Click the Enable Editing button. 3. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. 4. Rename the sheet tab Stats. 5. Select cell A4 and format the data as an Excel table using Green, Table Style Medium 21. Select cell A4 and format the data as an Excel table using Style Medium 21. 6. Copy the Stats sheet to the end and name the copy PM Flights. 7. Select the PM Flights sheet and use a Greater Than filter to display flights with a departure time after 12:00 PM (Figure 4-102). 8. Select the Stats worksheet, select cells I5:I32, and set conditional formatting to use 3 Stars from the Icon Sets in the Ratings group. 9. Build a two-level Custom Sort for the Capacity column to sort by icon. Show the solid gold star at the top, followed by the half-gold star. The silver star will default to the bottom (Figure 4-103). Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 4/3/18 Page 1 USING MICROSOFT EXCEL 2016 Independent Project 4-5 10. Select the Stats worksheet, select cells A4:I32, and use the Quick Analysis tool to create a PivotTable to display average of capacity by origin (Figure 4-104). 11. Rename the sheet PivotTable&Chart. 12. Select cell B3 in the PivotTable and use Field Settings to set a Number Format of Percentage with two decimal places. Edit the Custom Name to display Average Capacity. 13. Add the Passengers field to the PivotTable VALUES area with a sum calculation. Edit the field settings to display # of Passengers as the custom name. Set the number format to Number with zero decimals and a thousand’s separator. Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 4/3/18 Page 2 USING MICROSOFT EXCEL 2016 Independent Project 4-5 14. Use White, Pivot Style Light 8 for the PivotTable and show banded columns and rows. Step 2: Upload & Save Step 3: Grade my Project Use Pivot Style Light 8 for the PivotTable and show banded columns and rows. 15. Add a 3-D Pie PivotChart to the sheet and position the chart object to start in cell E3. Size the chart to reach cell N22. 16. Select the legend in the chart and set its font size to 11 from the Home tab. 17. Show Data Labels on the chart positioned at the Inside End. Select a data label and format all labels from the Home tab as bold and 10 pt. (Figure 4-105). 18. Save and close the workbook. 19. Upload and save your project file. 20. Submit project for grading. Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 4/3/18 Page 3 Boyd Air Flight Statistics Arrival Times and Capacities Date 2/1/2016 2/4/2016 2/7/2016 2/10/2016 2/13/2016 2/16/2016 2/19/2016 2/22/2016 2/25/2016 2/28/2016 3/3/2016 3/6/2016 3/9/2016 3/12/2016 4/1/2016 4/4/2016 4/7/2016 4/10/2016 4/13/2016 4/16/2016 4/19/2016 4/22/2016 4/25/2016 4/28/2016 5/3/2016 5/6/2016 5/9/2016 5/12/2016 Flight Number BD 2345 BD 980 BD 1234 BD 1345 BD 4567 BD 2213 BD 6578 BD 234 BD 981 BD 123 BD 221 BD 134 BD 98 BD 4567 BD 2345 BD 980 BD 1234 BD 1345 BD 4567 BD 2213 BD 6578 BD 234 BD 981 BD 123 BD 221 BD 134 BD 98 BD 4567 Origin MSP MSP MDW MSP MCO MDW FAR MDW FAR GRB MCO HOU FAR MCO MSP MSP MDW MSP MCO MDW FAR MDW FAR GRB MCO HOU FAR MCO Destination Departure Arrival Code MDW 8:00 AM 9:30 AM On Time FAR 12:30 PM 2:00 PM On Time GRB 10:00 AM 11:00 AM Delayed HOU 4:45 PM 7:30 PM On Time MSP 9:00 PM 11:30 PM On Time MCO 6:30 AM 10:30 AM Delayed STL 8:00 AM 9:30 AM On Time MSP 2:00 PM 3:30 PM On Time MSP 9:00 AM 10:30 AM On Time MDW 2:00 PM 3:00 PM Delayed MDW 11:00 AM 3:00 PM Delayed MSP 10:00 AM 12:45 PM On Time MDW 9:30 AM 11:45 AM On Time GRB 1:00 PM 5:00 PM On Time MDW 8:00 AM 9:30 AM On Time FAR 12:30 PM 2:00 PM On Time GRB 10:00 AM 11:00 AM Delayed HOU 4:45 PM 7:30 PM On Time MSP 9:00 PM 11:30 PM On Time MCO 6:30 AM 10:30 AM Delayed STL 8:00 AM 9:30 AM On Time MSP 2:00 PM 3:30 PM On Time MSP 9:00 AM 10:30 AM On Time MDW 2:00 PM 3:00 PM Delayed MDW 11:00 AM 3:00 PM Delayed MSP 10:00 AM 12:45 PM On Time MDW 9:30 AM 11:45 AM On Time GRB 1:00 PM 5:00 PM On Time Passengers 90 35 80 90 75 90 63 90 35 80 90 90 35 75 85 45 80 90 75 90 45 75 35 80 90 90 35 75 Capacity 98% 55% 85% 98% 82% 98% 65% 98% 55% 85% 98% 98% 55% 82% 90% 75% 85% 98% 82% 98% 50% 80% 55% 85% 98% 98% 55% 82%
Purchase answer to see full attachment