Home / Other Things / How To / How To: Find a Cheap Flight Using Excel Magic
Published on July 5, 2013, by in How To.

I admit it, I’m a bargain shopper.  It’s how I was raised.  We didn’t have much money growing up and my mom was great at stretching a dollar.  She still is, in fact!  And even though I’m lucky enough not to HAVE to be cheap, I still am most of the time.

Spending money on airfare is one of those times.  Why should I pay more than the person sitting next to me when we get exactly the same product?  We take the same flight, get the same food, see the same movie, and land at the same time – I want to spend as little as I possibly can because the quality of my flight won’t change (unless it’s an upgrade in flight class, of course).

There are tons of tips and tricks about how to save on airfare out there on the internet.  More than 2 million websites about it, according to the Google.  I think I’ve read a million of them (well, maybe just a couple thousand).  None have what I have, though – an easy price comparison spreadsheet template!

Making a price comparison spreadsheet is Step #1 for finding a cheap flight.  It’ll help you organize information, identify trends, and buy when prices are at their lowest.  Here’s how it works:

I figure out all the different combinations of airports I could use and make one sheet for direct flights, plus additional sheets for ‘hops’ or indirect connections.  Then I use some fancy functions to calculate the cost of the hops on my main page.  Was that confusing?  An example makes more sense.  Here’s the spreadsheet I’m using to calculate the cost of our upcoming flight to the Big Island of Hawaii (YAY, btw).

Airline Comparison Spreadsheet

This is the main page – I put my flight search engine on the left and my direct routes/dates on the top.  Typically there’s only one ‘direct flight’ column but the Big Island has two airports so I’ve listed both.  I also add a column for alternative dates because if I can save $200 by flying one night earlier, I will do it.

Then on the right I add some columns for my hops.  The only reasonable one for this trip is Phoenix to Honolulu then Honolulu to Hilo or Kona.  For all international flights, I check PHX to LAX, since flights to Los Angeles are occasionally dirt cheap on smaller airlines and they’re our closest international hub.

I find the prices for Honolulu to Hilo/Kona and put them on a second tab.

Flight Hop Comparison Spreadsheet

To easily pick out the cheapest flight from all the options, I use the ‘min’ function.  That means as I update the cost on the left, the Lowest Price field will automagically update with the lowest number.

Then I go back to my main spreadsheet and use another function to add the cost of a flight to Honolulu to the cheapest price for a hop from Honolulu to Hilo/Kona.

Add Function

This function references the Lowest Price field on my other spreadsheet and adds it to the cost of a flight to Honolulu.  That way I can see if it’s cheaper to hop two different airlines or take a direct flight.  If you look at my first spreadsheet example, you’ll see that using a local airline (Allegiant) to get to Honolulu yields the cheapest flight.

I update this spreadsheet once or twice a week starting as soon as I know about the trip.  The cheapest prices start to show up at 6 – 8 weeks before the flight (domestic only – for international, start the year before if you can) and at that point, I might update it every other day.  Why start early?  This gives me some baseline information so that I know what a ‘cheap’ deal really looks like.  If I didn’t know $712 was a little high, I might jump at it just because it’s cheaper than the Kayak price of $742, for example.  I also check more than once in a week because the best deals are usually, but not always, posted on Tuesday or Wednesday.  These are Friday prices, they went up a bit from my last search on Tuesday.  Start early, update often!

Is this a little complicated?  Yes.  Have I made it easier for you?  YES!  I made a nicer-looking template of this magical spreadsheet for you to download for free!

Just click the following link and go to File -> Download As:  https://docs.google.com/spreadsheet/ccc?key=0ApAKp63PVNEIdHRkaE5XamNTa1pyRFJuOU02TVNqTmc&usp=sharing

I fancied it up a bit from the screenshot examples above and made it easy to tell what info should go in what fields.  I also included the function calculations for you.  Hopefully it’ll help you save a couple of dollars on your next big adventure!