How to Find Great Circle Distance Using Excel Formula

This tutorial will demonstrate how to find Great Circle Distance using the Excel formula. When we are dealing with the surface of a sphere, we need to calculate the shortest distance between two points. We can find this shortest distance by using the Great Circle Distance formula. As our earth is just like a sphere and so for transportation, we need to use the great circle distance. It shows us the shortest route and saves time and transportation costs at the same time. So, it is essential to learn how to find great circle distance using the Excel formula.

What Is Great Circle Distance?

When we are dealing with the surface of a sphere, to calculate the shortest distance between two points we use the Great Circle Distance. It can be easily applicable to find out the shortest distance between any two places. To determine great circle distance, we need to use a certain formula. 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 show the latitude of the place,
• And lastly, the longitudes are presented by X and Y.

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

We’ll use a sample dataset overview as an example in Excel to understand easily. If you follow the steps correctly, you should learn how to find great circle distance by using the Excel formula on your own. We can learn this method by following the below steps.

Step 1: Arranging Dataset

In this case, our goal is to find a great circle distance in Excel. For this, we need a dataset. For instance, we have State in column B, their corresponding Latitudes in Column C, and Longitudes in column D. We’ll use this dataset for further explanation of this article.

Step 2: Determining Great Circle Distance

Now, we want to determine the great circle distance between two states in Excel by using the Excel formula. We can find the whole process by following the below steps.

• First, insert the following formula in the C8 cell.

The formula has used trigonometry functions like sine and cos. Moreover, A, B, X, and Y present the angle of latitude and longitude of the two states. Then, we used the RADIAN function to convert these angles to radian units. Furthermore, we used the ACOS function which mainly represents the anti-cos function of trigonometry. By these, we have used the above-discussed formula to determine the great circle distance.

• Last, press the Enter button to get the final result which presents the great circle distance between the two states.

Hence, we have determined the great circle distance between two states in Excel.

Now we want to show you an application of finding the distance between two distinct places in a state by the great circle distance formula We can find the whole process by following the below steps.

• To begin with, we arranged a dataset like the below image. In this case, we have State, Latitude 1, Latitude 2, Longitude 1, Longitude 2, and Great Circle Distance in columns B, C, D, E, F, and G.

• In addition, insert the following formula in the G5 cell.

• Furthermore, press Enter button to get the result for this cell and then use the Fill Handle option to apply the formula to the whole column.

• Finally, you will get the final result accordingly. Each cell of the Great Circle Distance column presents the greater circle distance in the same state within two selected places.

So,  we have calculated the great circle distance between two places in the same state in Excel in kilometers. If you want find the distances in miles, you should use the value 3959 instead of 6400.

How to Calculate Distance Between Two Addresses in Excel

In this portion of the article, we want to calculate the distance between two addresses in Excel by using the following steps.

• To begin with, we have State in column B, Latitude in Column C, and Longitude in column D. We want to determine the distance between these two states, and for this, we have an extra cell named Distance(Miles).

• In addition, insert the following formula in the C8 cell with the help of the combination of CONCATENATE and SUBSTITUTE.

This formula will create a Google Map link of how you can travel from Ohio to Alaska. The CONCATENATE function will add the addresses in the link and the SUBSTITUTE function will place the name of the addresses.

• Finally, press Enter button to get the final result by the CONCATENATE and SUBSTITUTE functions.

• After that, use this link in your internet search bar and you will get the distance information about these two addresses.

Therefore, we have calculated the distance in miles between the two addresses in Excel.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you learn how to find great circle distance using the Excel formula. We will be glad to know if you can execute the task in any other way. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.

<< Go Back to Distance | Formula List | Learn Excel