11 Aug Ch 4 Independent Project

Independent Project 4-5

YOU GET 4 TRIES

Windows  Mac

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.

[Student Learning Outcomes 4.1, 4.2, 4.3, 4.4, 4.7, 4.8]

File Needed: BoydAir-04.xlsx (Available from the Start File link.)

Completed Project File Name: [your name]-BoydAir-04.xlsx

Skills Covered in This Project

• Export data as a text 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.

1.  Open the BoydAir-04.xlsx start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it.
2.  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.
3.  Rename the sheet tab Stats.
4.  The worksheet includes the Boyd Air Flight statistics.
5.  Select cell A4 and format the data as an Excel table using Green, Table 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-106).
   

   

8.  Select the Stats worksheet, select cells I5:I32, and set conditional formatting to use 3 Flags from the Icon Sets.
9.  Build a two-level Custom Sort for the Capacity column to sort by icon. Show the green flag at the top, followed by the yellow flag. The red flag will default to the bottom (Figure 4-107).
   

   

10.  Select the Stats worksheet, select cells A4:I32, and create a PivotTable on a new worksheet to display average of capacity by origin.
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 Field 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 Field Name. Set the number format to Number with zero decimals and a thousand’s separator.
14.  Use White, Pivot Style Light 8 for the PivotTable and show banded rows and columns.
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 change the 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.
18.  Select cell A1 and save and close the workbook (Figure 4-109).
    

    

19.  Upload and save your project file.
20.  Submit project for grading.