Intro: Manage Flights Using Excel
Travelling by air is one of the most popular way to get to another place, especially when the distance is longer. However, it is not an easy job to memorize the departure time of every single flight, especially when you need to fly a few times in a week. Using calendar is a good way, but the records are separated. Therefore, it is a good idea to find a way to keep all the flight plans together and review the flight history easily.
There are many apps or websites that can manage the flight plans. However, it may either require you to fill in the reservation code, which might not work for them, or require you to forward the reservation email to them, which might not work as well, and gives you the risk of sending out private information. There are also websites that can keep all the flight records, but you have to manually update them, and the information it kept may be limited.
The way I am using to keep the flight records is using an Excel spreadsheet. Although it still requires you to manually update it, it gives you full private and complete freedom. I will discuss about how to create a flight history and plan list in Excel that looks cool, and contains the information you may use or concern about.
Step 1: Design Excel Spreadsheet
When recording a flight, it is necessary to first consider about what information you want to record. The most essential information I used in my document are as follow:
- Date – The date of flight departure
- Flight Number – The flight number shown on the ticket
- Actual Flight Number – In the cases of codeshare flights, this will help locate the carrier who operates the flight.
- Carrier – The airline company who will be responsible to fly
- Departure Airport – The departure airport with the information about terminal
- Arrival Airport – The arrival airport with the information about terminal
- Departure Time – The scheduled departure time with time zone. All time are local time.
- Arrival Time – The scheduled arrival time with time zone. All time are local time. Give another space to represent the different date of arrival.
There are also some optional information that I was using:
- Mileage – The distance between two cities.
- Class – The class of service and the one-letter class code that will be useful to find mileage accrual information.
- Seat – The seat that is reserved.
- Price – The total price of the ticket and the tax / surcharge collected by airline.
- Aircraft – The aircraft type that would be used in this flight.
- Reservation number (or PNR) – A six-character code that represents your reservation in the reservation website.E-ticket number – A 13-digit number that represents the actual flight ticket.
I have my sample attached so if you would like to start with it, feel free to do so. Remember, you can always add or delete a column if you would like to.
NOTE: The reservation number and e-ticket number are private information that should only be disclosed to the airlines during check-in or modifying / refunding trip via phone. Anyone holding these information and name could get access to all trip details and possibly modify the ticket.
Step 2: Retrieve Booking Details
The first step is to find the booking details. Typically, they can be found in the email sent from the booking website. The reservation confirmations should contain information about your flight details, the reservation confirmation code, and other information. Here are two sample emails from United and Singapore Airlines. As you can see, although the format may be different, the information are similar.
Step 3: Entering Data
Now you have a spreadsheet with first row as headers and the reservation details, you can start putting information into the document. Most of the information should be shown in the reservation details. Here I have a sample with data entered from the United reservation details into the document.
Step 4: Updating the Data
If you reserve your flight early (like a few months before departure), it is possible that your carrier will change the details of your flight. The change may involve flight number (very common in domestic flights), flight time, aircraft type, etc. After the changes, you should change your information in the document accordingly so that the data in your document is up to date.
For my trip to Shanghai, I did receive an email from United stating that I had a schedule change, although that is only a slight one with different flight time and aircraft type of the last segment. However, some of the changes may be more significant and even require you to change your travel plan completely.
Step 5: Add Some Statistics
Another benefit of an Excel spreadsheet is that you can conduct some calculation with it. Here is a very simple one, but you can always add more to it.
Calculate the Total mileage flown
- Locate to the first empty row of mileage column.
- Enter =sum(
- Click on the second row of mileage column (the first data) and drag to the last row with data.
- Hit enter
The computer will calculate the sum of every mileage and display the total in this cell. As long as you add more, it will add up the new numbers and update automatically.
Step 6: Keep Adding
Since you cannot memorize everything, keeping the old records while adding new ones can help you track what you have flown on before. A few years later, you can still find which flight you have flown and you will see the difference in between them. So keep adding, and create your own diary.