Create and format a PivotTable.

Independent Project 4-5

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).Figure 4-106 Filter results for Departure Time field
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).Figure 4-107 Custom sort for the 3 Flags icon set
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-108).Figure 4-108 PivotTable suggestions from the Quick Analysis tool
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.
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).Figure 4-109 Excel 4-5 completed
19. Upload and save your project file.
20. Submit project for grading.