Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 4/3/18 Page 1
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.
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).
Step 1: Download start file
Excel 2016 Chapter 4 Formatting, Organizing, and Getting Data Last Updated: 4/3/18 Page 2
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 3
USING MICROSOFT EXCEL 2016 Independent Project 4-5
14. Use White, Pivot Style Light 8 for 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.
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.
Step 2: Upload & Save
Step 3: Grade my Project
Applied Sciences
Architecture and Design
Biology
Business & Finance
Chemistry
Computer Science
Geography
Geology
Education
Engineering
English
Environmental science
Spanish
Government
History
Human Resource Management
Information Systems
Law
Literature
Mathematics
Nursing
Physics
Political Science
Psychology
Reading
Science
Social Science
Home
Blog
Archive
Contact
google+twitterfacebook
Copyright © 2019 HomeworkMarket.com