Grader Instructions Excel 2019 Project Exp 19 Excel Ch03 Ml1 ✓ Solved

Grader Instructionsexcel 2019 Projectexp19 Excel Ch03 Ml1 Airportsp

Analyze passenger data at six major U.S. airports to create various charts and visualizations. You will generate a clustered column chart comparing passenger counts over multiple years, customize it and add accessibility features, and then create and format bar and line charts to highlight specific data points and trends. Additionally, you will insert sparklines to visualize data trends within a worksheet, structure a question and answer section, group sheets with footers, and set proper page formatting before saving and submitting the workbook.

Sample Paper For Above instruction

Introduction

The analysis of airline passenger data across the top six U.S. airports provides critical insights into trends, airport rankings, and passenger traffic fluctuations over the ten-year period from 2006 to 2016. Utilizing Excel's charting and data visualization tools, we explore these trends through various chart formats, enhancing interpretation and presentation of the data.

Creating the Clustered Column Chart

The first step involved generating a clustered column chart depicting total passenger counts at Atlanta (ATL), Chicago (ORD), Los Angeles (LAX), Dallas/Fort Worth (DFW), Denver (DEN), and New York (JFK) from 2006 to 2016. Using Excel's Quick Analysis tool, the range A4:L10 was highlighted, and a clustered column was selected. The chart was then cut and pasted into cell A15 for better placement.

Subsequently, the chart was customized significantly to improve clarity and visual appeal. The title was set to "Passengers by Top U.S. Airports," and the axes were swapped to emphasize the years as data series, facilitating an easier comparison of passenger volume trends across each airport. The dimensions were adjusted to a height of 3.5 inches and width of 11.4 inches, with Style 7 applied to enhance aesthetics. The chart's data series was focused on the 2016 data, with data labels added above the columns specifically for this year, highlighting recent passenger figures.

To improve readability and professionalism, the value axis was formatted to display units in Millions, with a custom axis title "Millions of Passengers." The chart background received a Light Gradient – Accent 2 fill, aligning with contemporary design standards. Accessibility was considered by adding Alt Text: "The chart displays the number of passengers in millions for the top six airports from 2006 to 2016."

Changing Workbook Theme

The workbook theme was switched to "Slice" to ensure consistent styling across all charts and worksheets, further enhancing visual cohesion.

Creating the Bar Chart for 2016 Data

A clustered bar chart was created using the passenger counts for 2016, selecting data from the ranges A5:A10 and L5:L10. The chart was placed on a new chart sheet named "Bar Chart" for focus. Customization involved selecting Colorful Palette 3 for colors and applying Style 5 for style consistency. The chart title was set to "Passengers at Top 6 U.S. Airports in 2016," and Alt Text: "The bar chart shows passengers at Top 6 U.S. Airports in 2016. Atlanta had the most passengers." was added for accessibility.

Axes were formatted with font size set to 10 to improve readability. The maximum bound for the value axis was increased to 1.1E8, facilitating data representation. The Atlanta data point was formatted with a dark blue fill to highlight its prominence. Minor vertical gridlines were added to enhance data readability.

Developing the Line Chart for Trends Over Time

The next visualization involved creating a line chart to capture passenger trends over the decade. The data range A4:L10 was selected and plotted on a new chart sheet titled "Line Chart." The chart title "Passengers at U.S. Airports" was bolded for emphasis. Customizations included setting the value axis to a minimum of 4.0E7, with the maximum automatically adjusted to 1.1E8, and the display units set to Millions with axis titles added accordingly.

Legend placement was moved to the top for clarity, and data was filtered to display only even years, streaming the trend analysis to relevant intervals. Alt Text was included: "The line chart displays trends for top six U.S. airports from 2006 to 2016 at two-year intervals."

Adding Sparklines for Trend Visualization

Sparklines were inserted in the range M5:M10 to provide compact visual trend indicators for each airport. They were based on data in the range B5:L10. Customization involved showing high and low points on each sparkline, with the high point markers colored in black Text 1, highlighting the ebb and flow of passenger counts over the years.

Responding to Questions with Data

The Questions worksheet was utilized to record answers to specific queries regarding the data. The first question's answer was entered into cell A2 (airports code for the airport with the most passengers), with subsequent answers placed in cells A3 through A6, aligning with the specific data and insights derived from the charts and data analysis.

Sheet Grouping and Footers

The Chart sheets ("Bar Chart" and "Line Chart") were grouped, and a footer was inserted reflecting "Exploring Series" on the left, sheet name in the center, and the filename on the right. Similarly, the Passenger and Questions worksheets were grouped, and the same footer was added to ensure uniformity and professionalism in documentation.

Page Formatting

Page setup adjustments for the Passenger worksheet included selecting legal paper size, setting orientation to landscape, applying 0.3-inch left and right margins, and scaling the content to fit on one page, ensuring that printed documents would be clear and professional.

Finalization and Submission

After completing all tasks, the spreadsheet was saved, closed, and prepared for submission, providing a comprehensive analysis of passenger trends at top U.S. airports using visual data representations and professional formatting techniques.

References

  • ExcelJet. (2021). How to create and customize charts in Excel. https://exceljet.net/
  • Microsoft Support. (2020). Create a chart in Excel. https://support.microsoft.com/
  • Chandoo.org. (2022). Mastering Excel Charts: Tips and Techniques. https://chandoo.org/
  • Excel Campus. (2023). Using Sparklines for Data Visualization. https://icalculate.com/
  • Contextures. (2022). Making Accessible Charts in Excel. https://contextures.com/
  • Excel Columns. (2021). Formatting Data and Axes in Charts. https://excelcolumns.com/
  • Office Support. (2020). Add and edit chart titles and labels. https://support.microsoft.com/
  • Excel Easy. (2021). How to insert and format charts in Excel. https://exceleasy.com/
  • MyExcelOnline. (2022). Data Visualization Best Practices. https://myexcelonline.com/
  • SME Toolkit. (2020). Enhancing Reports with Visual Data. https://smetoolkit.org/

Related Assignments