Earlier this month, I had the pleasure of flying out of Chicago’s O’Hare airport. I was checking in at the ungodly hour of 6 AM on a Sunday, and hoped that would be a quiet time at the airport. The streets near the downtown hotel were almost empty, so that was encouraging, but the closer the taxi got to the airport, the busier things got. Not a good sign!
There was no line at the Air Canada counter (woohoo!), but there was no counter agent either. He showed up a couple of minutes later, and that part of the process went quickly. (The same guy showed up at the gate later, to take our tickets, and attached the jetway to the airplane when it arrived. I figured he’d fly the plane too, but he didn’t!)
Then, it was on to the dreaded security checkpoint, and the inevitable long wait.
One poor gentleman actually passed out in the line, and fortunately recovered quickly, aided by a paramedic, who was also in line. That’s another bad sign, if you have to step over people, to get through security.
And what does all this suffering have to do with Excel, you ask? Well, to help you plan your next trip, I found a website that lists the wait times for US airport security checkpoints. From their data, I created an Excel Box Plot (Box and Whisker Chart), for a few of the security checkpoints at O’Hare. You can do something similar for your airport, and choose the best time to fly. Hint: It is not Sunday at 6 AM.
Create Statistics from Your Data
To create a box plot, you’ll calculate the the MIN, MEDIAN and MAX for each series. Then, use the PERCENTILE function, to find the lower quartile (Q1) and upper quartile (Q3). You can calculate the AVERAGE too, if you’d like to show that on the chart.
Based on those numbers, you’ll calculate the differences, to find the size of each box and whisker.
Then create and format a stacked column chart (boxes), with error bars to show the highs and lows (whiskers).
Unfortunately, I don’t remember which checkpoint I went through, but I’ll bet that it was Checkpoint 6.
Jon Peltier’s Box Plot Utility
If you need to make box and whisker charts, or other custom chart types, the PTS Excel Chart Utility will make it much easier and quicker for you to create them. It adds a new sheet in your workbook, calculates the statistics for your data, then creates and formats a box and whisker chart. It will pay for itself very quickly, in the time you’ll save.
Watch the Box Plot Video
To see the steps for creating an Excel Box and Whisker chart, watch this Excel video tutorial. It’s recorded in HD, so select that option when viewing it, if you can, for a clearer picture.