# How to Find Great Circle Distance Using Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.

`=ACOS(COS(RADIANS(90-C5))*COS(RADIANS(90-C6))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-C6))*COS(RADIANS(D5-D6)))*6400` 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.

`=ACOS(COS(RADIANS(90-C5))*COS(RADIANS(90-D5))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-D5))*COS(RADIANS(E5-F5)))*6400` • 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.

`=CONCATENATE("http://maps.google.co.uk/maps?f=d&source=s_d&saddr=",SUBSTITUTE(B5," ","+"),"&daddr=",SUBSTITUTE(B6," ","+"))` • Finally, press Enter button to get the final result by the CONCATENATE and SUBSTITUTE functions.  Therefore, we have calculated the distance between the two addresses in Excel.

## Related Articles Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  