Analyze flight delay data with Excel tables and create a dashboard!

You will be analyzing real flight delay data from the U.S. Department of Transportation. Using the skills you gained from this class, you will:

Create a table and filter the table with a Slicer
Write formulas in the table using structured references
Fill out a Flight Delay Dashboard Report using formulas that reference the table
(Extra credit) Write your key takeaways after analyzing the data

In the resources for this class you’ll find the Excel workbook for this project called “DOT_Flights_2015.xlsx.” A quick overview of the worksheets:

After taking a look at the raw data set, here’s what I expect:

Turn the raw_flights_data dataset into a table (call the table something like “flights”) and create a Slicer that lets you slice the data by ORIGIN_AIRPORT.
Turn the airline_lookup lookup table into a table as well (give the table a name).
Create formulas using structured references to fill out the AIRLINE_FULL and MPH columns in the raw_flights_data worksheet.
Use the AVERAGEIFS() formula to summarize the raw data into the summary table on the Dashboard worksheet.
Take a screenshot of your dashboard page and upload this screenshot to the Project page for this class for others to see.
Add some of your takeaways about which airports or airlines have the most delays and include it with your screenshot on the Project page.

Project Tips

Structured reference syntax As you saw in this class, writing formulas with tables is a little different than regular formulas. You can "select” the entire column to use in your formula by typing out the entire reference (e.g. Table1[AIRLINE]) or selecting the entire column without the header (row 1).
Ask questions! As you are building out the formulas for the dashboard, please ask questions in the class discussions page. I will be checking the discussions actively.
Learn more about the dataset This dataset comes straight from the U.S. Department of Transportation’s Air Travel Consumer Report. Learn more about the dataset here.

Resources

Connect with me on LinkedIn and follow me on Instagram, TikTok, and Quora
Check out my other Skillshare classes.
Download the 2015 flight delay data from the Department of Transportation and workbook for your project, here.

Vanessa Chan

1