Excel #2

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 compare 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  Download 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 color to 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.

21. Save and close the file.

Submit the assignment on Canvas.

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 compare 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  Download 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 color to 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.

21. Save and close the file.

Submit the assignment on Canvas.