Reference no: EM133230402

Airport Passenger Counts

As an analyst for the airline industry, you track the number of passengers at the top six major U.S. airports: Atlanta (ATL), Chicago (ORD), Los Angeles (LAX), Dallas/Fort Worth (DFW), Denver (DEN), and New York (JFK). You researched passenger data and created a worksheet that lists the number of total yearly passengers at the top six airports. To prepare for an upcoming meeting, you will create a clustered column chart to compare the number of passengers at each airport. Then, you will create a line chart that compares trends over time. Next, you will create a bar chart to com­pare the passenger count for the latest year of data available and then emphasize the airport with the largest number of passenger traffic. Finally, you want to insert sparklines to visually represent trends in passengers at each airport over the ten-year period. You can then refer to the sparklines and clustered column chart to answer critical-thinking questions.

1. Download and open Airports.xlsx and save it as Airports_LastFirst.
2. Use Quick Analysis to create a clustered column chart for the range A4:L10. Cut the chart and paste it in cell A15.
3. Customize the column chart by doing the following:
   • Type Passengers by Top U.S. Airports as the chart title
   • Swap the data on the category axis and in the legend.
4. Set a 3.5" height and 11.4" width.
5. Apply the Style 7 chart style.
6. Adjust the value axis by doing the following:
   • Change the display units to Millions for the value axis.
   • Edit the axis title to display Millions of Passengers.
7. Display data labels above the columns for the 2016 data series only.
8. Apply the Light Gradient - Accent 1 fill colorto the chart area.
9. Add Alt Text The column chart displays the number of passengers in millions for the top six airports from 2006 to 2016. (including the period).
10. Use the Page Layout tab to change the workbook theme to Slice.
11. Create a recommended clustered bar chart for the range A5:A10 and L5:L10 and move the chart to a chart sheet named Bar Chart.
12. Customize the bar chart by doing the following:
    • Change the chart color to Monochromatic Palette 7.
    • Enter Passengers at Top 6 U.S. Airports in 2016 as the chart title.
    • Apply the Style 5 chart style.
    • Add Alt Text Atlanta had the most passengers in 2016. (including the period).
13. Modify the axes by doing the following:
    • Change the font size to 10 for the category axis and value axis.
    • Change the value axis Maximum Bound to 1.1E8. (The Minimum Bound will change automatically.)
14. Format a data point and add gridlines by doing the following:
    • Format the Atlanta data point with solid Dark Blue fill color.
    • Add Primary Minor Vertical gridlines.
15. Create a line chart using the range A4:L10 in the Passengers worksheet and move the chart to a chart sheet named Line Chart.
16. Add a chart title Passengers at U.S. Airports 2006-2016 and bold the title.
17. Customize the line chart by doing the following:
    • Set the Minimum Bound at 4.0E7 for the value axis. The Maximum Bound should change to 1.1E8 automatically.
    • Display the value axis in Millions. Add a value axis title In Millions,
    • Change the font size to 10 for the value axis and category axis.
    • Move the legend to the top.
    • Filter the chart by deselecting the odd-numbered years.
    • Add the Alt Text The line chart displays trends for top six U.S. airports from 2006 to 2016 at two-year intervals.(including the period).
18. Display the Passenger worksheet and insert Line sparklines in the range M5:M10 to illustrate the data in the range B5:L10. This should insert a sparkline to represent yearly data for each airport.
19. Customize the sparklines by doing the following:
    • Show the high and low points in each sparkline.
    • Apply Black, Text 1 color to the high point marker in each sparkline.
20. Display the Questions worksheet. Read the question in cell B2 and type the answer in cell A2. Read the rest of the questions in column B and type the correct answers in column A in the respective cells.