I need help with

Excel 2013 In Practice - Ch 4 Independent Project 4-5 USING MICROSOFT EXCEL 2013 Independent Project 4-5 Independent Project 4-5 Boyd Air has received flight data for recent flights. After importing the data, you format it as a table and use a number filter to display the afternoon flights. Display subtotals by city of origin and use a PivotTable to illustrate the average capacity of the flights. This project has been modified for use in SIMnet. Skills Covered in This Project Step 1 Download start file Import a text file. Create an Excel table. Filter data. Convert a table to a normal range. Sort data. Use the Subtotal command. Copy, name, and move a worksheet. Create a PivotTable. Create a PivotChart. IMPORTANT: Download the resource file needed for this project from the Resources link. Be sure to extract the file after downloading the resources zipped folder. Please visit SIMnet Instant Help for step-by-step instructions. 1. Open the FlightData-04 start file. 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. NOTE: Click the Enable Editing button in the Message Bar at the top of the workbook so you can modify it. Download Resources 2. Import the FlightData-04.txt text file (downloaded from the Resources link), starting at cell A3. The text file is tabdelimited. 3. Click a cell within the data range and format the range as an Excel table using Table Style Medium 21. 4. Use the AutoFilter button, Number Filters, and the Greater Than comparison filter to display those flights with a Departure Time after 12:00:00 PM (Figure 4-102). 5. Type Boyd Air in cell A1. Type Flight Statistics in cell A2. Set the font size to 20 pt. for both labels. 6. Center the labels in cells A1:A2 across the table data. Use the Center Across Selection option from the Horizontal Text alignment drop-down menu in the format Cells dialog box. See Figure 4-102.5. 7. Name the worksheet Afternoon Flights 4-102 Number filter for Departure time field 4-102.5 Center Across Selection Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 9/24/15 Page 1 USING MICROSOFT EXCEL 2013 Independent Project 4-5 8. Copy the Afternoon Flights worksheet, name the copy Subtotals, and move the Subtotals sheet to the right of the Afternoon Flights sheet. 9. On the Subtotals sheet, clear the filter. 10. On the Subtotals sheet, sort the data by Origin in A to Z order. 11. Convert the table on the Subtotals sheet to a normal range. 12. Select cells A3:I17 and use the Subtotal command (Data tab, Outline group) to display a sum for the Passengers field at each change in Origin (Figure 4-103). You must select the cells for the Subtotal command, because the main labels are not separated from the data range by a blank row. 4-103 Subtotals for Passenger field at each change in Origin field 13. Copy the Afternoon Flights worksheet, name the copy PivotTable Source, and move the PivotTable Source sheet to the right of the Subtotals sheet. 14. On the PivotTable Source sheet, clear the filter. 15. Select cells A3:I17 and use the Quick Analysis tool to create a PivotTable to display average of capacity by origin (Figure 4-104). 4-104 PivotTable suggestions from the Quick Analysis tool Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 9/24/15 Page 2 USING MICROSOFT EXCEL 2013 Independent Project 4-5 16. Click cell B3 in the PivotTable. On the PivotTable Tools Analyze tab, click Field Settings and set a Number Format of Percentage with 2 decimal places. 17. In the PivotTable Fields pane, place a check mark to add the Passengers field to the PivotTable VALUES area showing a sum. 18. Add a 3-D Pie PivotChart to the sheet and position the chart to the right of the PivotTable. 19. Rename the worksheet PivotTable&Chart and close the task pane (Figure 4-105). 20. Save and close the workbook. Step 3 Grade my Project 21. Upload and save your project file. Step 2 22. Submit project for grading. Upload & Save 4-105 Excel 4-5 completed PivotTable and PivotChart Excel 2013 Chapter 4 Importing, Creating Tables, Sorting and Filtering, and Using Conditional Formatting Last Updated: 9/24/15 Page 3