How to Find Great Circle Distance Using Excel Formula

This tutorial will demonstrate how to find the Great Circle Distance using an Excel formula.

What Is Great Circle Distance?

When dealing with the surface of a sphere, we use the Great Circle Distance to calculate the shortest distance between two points.

The formula is:

d = rcos^-1[cosA.cosB.cos(X-Y) + sinA.sinB]

where,

r represents the radius of Earth, which is approximately 6400 kilometers or 3959 miles,
A and B represent the latitudes of the two points, and
X and Y represent the longitudes.

How to Find Great Circle Distance Using an Excel Formula: Step-by-Step Procedure

Step 1 – Arranging the Dataset

Suppose we have the following dataset, containing States in column B, their corresponding Latitudes in Column C, and Longitudes in column D. We’ll use this dataset to illustrate this article.

Step 2 – Determining Great Circle Distance

Now, we’ll determine the Great Circle Distance between the two states with a formula.

Insert the following formula in cell C8:

=ACOS(COS(RADIANS(90-C5))*COS(RADIANS(90-C6))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-C6))*COS(RADIANS(D5-D6)))*6400

The formula uses trigonometry functions like sine and cos. A, B, X, and Y represent the angles of latitude and longitude of the two states. We use the RADIAN function to convert these angles to radian units. Then we use the ACOS function which mainly represents the anti-cos function of trigonometry. The combination of these functions determines the Great Circle Distance.

Press Enter to return the result.

We have determined the Great Circle Distance between the two states.

Now let’s apply this method to find the distance between two distinct places in a state.

Arrange a dataset like in the below image, with State, Latitude 1, Latitude 2, Longitude 1, Longitude 2, and Great Circle Distance in columns B, C, D, E, F, and G respectively.

Insert the following formula in cell G5:

=ACOS(COS(RADIANS(90-C5))*COS(RADIANS(90-D5))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-D5))*COS(RADIANS(E5-F5)))*6400

Press Enter to return the result for this cell.
Drag the Fill Handle down to apply the formula to the rest of the cells in the column.

The final result is as in the image below. Each cell of the Great Circle Distance column represents the shortest distance between the 2 places specified in each State.

The above calculations are in kilometers. To find the distances in miles, use the value 3959 instead of 6400 in the formula.

How to Calculate the Distance Between Two Addresses in Excel

Now we’ll calculate the distance between two addresses in Excel.

Steps:

Suppose we have a dataset with State in Column B, Latitude in Column C, and Longitude in column D.

To determine the distance between these two states, add a helper cell named Distance(Miles).

Enter the following formula that combines the CONCATENATE and SUBSTITUTE functions in cell C8:

=CONCATENATE("http://maps.google.co.uk/maps?f=d&source=s_d&saddr=",SUBSTITUTE(B5," ","+"),"&daddr=",SUBSTITUTE(B6," ","+"))

This formula will create a Google Map link of how to travel from Ohio to Alaska. The CONCATENATE function will join the addresses in the link, and the SUBSTITUTE function will place the names of the addresses.