Excel tutorials

How in Excel in a quick way to show the Distance Between Two Airports

In this Excel tutorial I will show how we can use Excel and calculate the distance between the two airports in the world. In order to calculate the distance, we need the latitude and longitude of the particular airport/city. In fact, two parameters are the coordinates of a particular airport. So remember these terms below.

  • Latitude
  • Longitude

Based on coordinates of latitude and longitude using the Haversine formula, we can calculate the air distance between the two airports. In the picture below, notice the elements of the Haversine formula. In the text below, you will see how this formula looks in Excel.

Haversine formula in Excel

Calculation of distance between two cities

In the picture below, note the dropdown menu layout from the starting point to the destination. In the cells 'A2', 'B2', 'C2', 'A5', 'B5' and 'C5' there are drop-down menus. In cells 'B2', 'B5' and 'C2', 'C5' there are dependent drop-down menus because they depend on the previous data in front of the cell. This means if you select the country 'Croatia' in the cell 'B2'/'B5' you will be able to choose only the cities that are in Croatia. Also, in the 'C2'/'C5' cell, you can only select the names of the airports in the city concerned (of course, if there are more than one).

In the eighth row, there are formulas that automatically return results for the conditions set in the previously mentioned cells.

Distance between two airports

Worksheet "WorldCountries"

This worksheet contains a list of countries in the world with its codes, labels continents and web links for details on the country (state). This worksheet is not important and you can delete it if you want.

List of cities and their associated international codes

Creating drop-down menus for the country, city, and name of the airport from the starting point

On the worksheet "Distance Between Airports" there are the following:

The drop-down menu 'from Country'

As you can see in the picture below, in cell 'A2' is a drop-down menu where you can choose one of 237 countries (states) that are found in the database.

Drop-down list from the airport

Set 'Data Validation' in the 'A2' cell according to the instructions from the link, and in the "Source field" set the formula

=helper!$A$2:$A$238

Data Validation of the country

Dependent dropdown menu 'from City'

As you can see in the picture below, in the 'B2' cell there is a drop down menu where you can select one of the 6679 cities with one or more airports located in the database.

Dependent on Data Validation from the city

Set 'Data Validation' in cell 'B2' according to the instructions from the link, and in the "Source field", set the formula below. (Note! Named formulas are found in the text of this tutorial in the chapter Name Manager).

=OFFSET(StartCell,1,MATCH($A2,Countries,0)-1,COUNTA(OFFSET(StartCell,,MATCH($A2,Countries,0)-1,10000,1))-1,1)

Data Validation from the city

Dependent drop-down menu 'from Airport Name'

As you can see in the picture below, in the 'C2' cell there is a drop down menu where you can choose one of the airports for the previously selected city, of course if the city has only one airport then it will only be on the list.

Drop-down menu from the airport

Set 'Data Validation' in the 'C2' cell according to the instructions from the link, and in the "Source field", set the formula below. This drop-down menu pulls items from the 'J' help column (explanation in the chapter 'help columns' below the tutorial).

=$J$2:$J$7

Data Validation for the departure airport

Creating drop-down menus for a destination: country, city, and airport name

The previously described methods for creating Data Validation also apply to destination drop-down menus.

The drop-down menu 'to Country'

As you can see in the picture below, the cell 'A5' is a drop-down menu where you can choose one of 237 countries (states) that are found in the database.

Drop-down list to the country in which the airport

To set up 'Data Validation' in cell 'A5', follow the instructions in the link and place the formula in the 'Source field'

=helper!$A$2:$A$238

Data Validation to the destination country

Depending on the drop down menu 'to City'

As you can see in the picture below, in the 'B5' cell there is a drop-down menu where you can select one of the 6679 cities with one or more airports located in the database.

Data Validation for the destination city

Set 'Data Validation' in the 'B5' cell according to the instructions from the link, and in the "Source field", set the formula below. (Note! Named formulas are found in the text of this tutorial in the chapter Name Manager).

=OFFSET(StartCell,1,MATCH($A2,Countries,0)-1,COUNTA(OFFSET(StartCell,,MATCH($A2,Countries,0)-1,10000,1))-1,1)

Data Validation to the city

Dependent drop down menu 'to Airport Name'

As you can see in the picture below, in the 'C5' cell there is a drop down menu where you can choose one of the airports for the previously selected city, of course if the city has only one airport then it will only be on the list.

Dependent List of airports belonging to the city

Set 'Data Validation' in the 'C2' cell according to the instructions from the link, and in the "Source field", set the formula below. This drop-down menu pulls items from the auxiliary column 'L' (See the chapter 'helper columns' in below tutorials).

=$L$2:$L$7

Data Validation to a specific airport

Helper columns for creating a dependent drop-down list of airport names

In the picture below, note the two auxiliary columns in the worksheet "Distance Between Airports". These two columns contain a list of all airport names in the city you chose in the 'B2'/'C5' and returns the list of all airports belonging to the relevant city.

Auxiliary columns for a list of all airports belonging to a particular city

The ARRAY formula in the 'J2' cell is: (This formula returns a list of all the airport names for the city that is set in the 'B2' cell.

=IFERROR(INDEX(tbl,SMALL(IF(COUNTIF($A$2,AirportsDatabase2017!$B$2:$B$7185)*COUNTIF($B$2,AirportsDatabase2017!$C$2:$C$7185),ROW(tbl)-MIN(ROW(tbl))+1),ROW(C1)),COLUMN(C1)),"")

The ARRAY formula in cell 'L2' is: (This formula returns a list of all city airport names that are set in cell 'B5'.

=IFERROR(INDEX(tbl,SMALL(IF(COUNTIF($A$5,AirportsDatabase2017!$B$2:$B$7185)*COUNTIF($B$5,AirportsDatabase2017!$C$2:$C$7185),ROW(tbl)-MIN(ROW(tbl))+1),ROW(C1)),COLUMN(C1)),"")

Naming Formulas in the Name Manager

Name Manager is located on the Formulas Tab Ribbon. It contains formulas that are nested in the OFFSET formula. There is also a named data range "tbl" that serves as a database for the VLOOKUP formula.

Named formulas in the Name Manager

Named formulas in the Name Manager

Name: Countries
Refers to: =OFFSET(StartCell,,,1,COUNTA(OFFSET(StartCell,,,,100)))

Name: StartCell
Refers to: =helper!$B$1

Name: tbl
Refers to: =AirportsDatabase2017!$B$2:$L$7185

How to Automatically Display IATA International Codes for a specific airport

In the figure below in the eighth row there are formulas that return the results for the set conditions in the second and fifth row (A:C). Notice that in the 'A8' and 'B8' cells, the returned result is the IATA international code that is unique to each aerodrome. The 'C8' result is the distance between the two airports. In D8:G8 cells there are latitude and longitude for the respective cities/airports.

IATA International Airports Code

On the worksheet "Distance Between Airports" formula are as follows:

The formula in the cell 'A8' is: (This formula returns the result from the worksheet 'AirDatabase2017' from the second column for the condition of the cell 'C2').
=IFERROR(VLOOKUP($C$2,AirportsDatabase2017!$D$2:$E$7185,2,FALSE),"")

The formula in the cell 'B8' is: (This formula returns the result from the worksheet 'AirDatabase2017' from the second column for the condition of the cell 'C5').
=IFERROR(VLOOKUP($C$5,AirportsDatabase2017!$D$2:$E$7185,2,FALSE),"")

The formula in the cell 'C8' is: (This formula uses the Haversine formula for calculating the distance between the two airports, the formula is based on latitude and longitude in columns 'D8:E8' and 'F8:G8').
=IFERROR(ACOS(COS(RADIANS(90-D8))*COS(RADIANS(90-F8))+SIN(RADIANS(90-D8))*SIN(RADIANS(90-F8))*COS(RADIANS(E8-G8)))*6371,"")

The formula in the cell 'D8' is: (This formula returns the latitude from the 'AirDatabase2017' sheet for the condition of the cell 'A8').
=IFERROR(VLOOKUP($A8,AirportsDatabase2017!$E$2:$H$7185,3,0),"")

The formula in the cell 'E8' is: (This formula returns the latitude from the worksheet 'AirDatabase2017' for cell condition 'A8).
=IFERROR(VLOOKUP($A8,AirportsDatabase2017!$E$2:$H$7185,4,0),"")

The formula in the cell 'F8' is: (This formula returns the latitude from the 'AirDatabase2017' sheet for the condition of the cell 'B8').
=IFERROR(VLOOKUP($B8,AirportsDatabase2017!$E$2:$H$7185,3,0),"")

The formula in the cell 'G8' is: (This formula returns the latitude from the 'AirDatabase2017' worksheet for the 'B8' condition).
=IFERROR(VLOOKUP($B8,AirportsDatabase2017!$E$2:$H$7185,4,0),"")

Worksheet "Airports DataBase"

This worksheet contains all the information needed to calculate the distance between the two airports. This database is from 2017 and the source is "https://ourairports.com/data/#excel"

Database of international airports

Worksheet "Helper"

This worksheet contains unique lists of all countries of the world (states) and associated cities that have an airport. All lists are sorted alphabetically. In the column 'A' there is a list of all countries/states, and in the columns 'B1:ID1' there is the name of the country/state and below the respective cities. This "helper" worksheet is a data source for the second dependent drop-down menu located in the cells 'B2' and 'B5' in the worksheet 'Distance Between Airports'.

List of countries and cities in the world that have an airport

Automatically remove dependent drop-down lists with a change of data

As you know when changing data in drop-down menus when you want to change the starting country or city, it is desirable that the drop-down menus are removed, so you have a visual warning that you need to select a new item from the list. For this purpose, you can use the VBA macro below.

Copy this VBA macro to the 'Sheet Module' of your VBE for Excel

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2")) Is Nothing Then
Sheets("Distance Between Airports").Range("B2:C2").Value = ""
End If
If Not Intersect(Target, Range("B2")) Is Nothing Then
Sheets("Distance Between Airports").Range("C2").Value = ""
End If
If Not Intersect(Target, Range("A5")) Is Nothing Then
Sheets("Distance Between Airports").Range("B5:C5").Value = ""
End If
If Not Intersect(Target, Range("B5")) Is Nothing Then
Sheets("Distance Between Airports").Range("C5").Value = ""
End If
End Sub

To make this macro functional, save your workbook in *.xls or *.xlsm or *.xlsb format

Please note that you must correctly set absolute and relative addresses in formulas, especially if you copy the formula.
Finally, in the attachment of this tutorial, you have the DOWNLOAD file option on which I did this tutorial.