Function Problems

How to Calculate Distance Between Latitude and Longitude Points in Excel

Maira Mehtab Sep 22, 2026 871 views

Question details

The user needs to calculate the geographical distance between two sets of latitude and longitude coordinates in Excel, which lacks a built-in native distance function.

Product: Excel
Device & OS: not provided
Scenario: Calculating the great-circle distance (e.g., in miles) between two geographic points using their latitude and longitude coordinates for spatial data analysis or routing.
Observed behavior: Because Excel does not have a native function for geographic distance, users must manually apply complex mathematical formulas like the Haversine formula or create custom functions to achieve the result.

Before you start

Ensure your latitude and longitude coordinates are formatted as decimal degrees (e.g., 45.4, 77.1) rather than degrees, minutes, and seconds, as trigonometric formulas require decimal values.

Solution 1Recommended

Create a Custom GeoDistance LAMBDA Function

Use Excel's Name Manager to define a reusable custom LAMBDA function based on the Haversine formula, making it easy to calculate distances across multiple rows without typing long mathematical equations each time.

The Haversine formula calculates the great-circle distance between two points on a sphere given their longitudes and latitudes. By wrapping this math inside a custom LAMBDA function, you create a clean, easy-to-use formula that works just like native Excel functions.

The formula below uses an Earth radius of approximately 3958.7613 miles, which means the calculated distance will be returned in miles.

Open Name Manager

Navigate to the 'Formulas' tab on the Excel ribbon and click on 'Name Manager' in the Defined Names group.

Create a New Name

Click the 'New' button in the Name Manager dialog box. In the 'Name' field, type 'GeoDistance'.

Enter the LAMBDA Formula

In the 'Refers to' input box at the bottom, enter the following formula exactly: =LAMBDA(dLat1,dLng1,dLat2,dLng2,LET(a,RADIANS(dLat1),b,RADIANS(dLat2),d,RADIANS(dLng2-dLng1),Re,3958.76131309817,T,(1-COS(a)*COS(b)*COS(d)-SIN(a)*SIN(b))/2,2*Re*ASIN(SQRT(T)))). Click 'OK' to save it.

Use the Function in the Worksheet

Close the Name Manager. In any blank cell, type =GeoDistance(45.4, 77.1, 45.3, 77.2) replacing the numbers with the actual cell references containing your Lat1, Lng1, Lat2, and Lng2 coordinates. Press Enter to calculate the distance.

Kilometers Conversion: To calculate the distance in kilometers instead of miles, edit the formula in the Name Manager and replace the Earth's radius value '3958.76131309817' with '6371'.

Advanced Data Analysis

Calculate Geographical Distances Easily with WPS Spreadsheet

WPS Office provides a robust Spreadsheet application that fully supports all the advanced trigonometric and math functions (such as RADIANS, SIN, COS, ASIN, and SQRT) required to execute complex distance calculations effortlessly.

1. Prepare your coordinate data: Open WPS Spreadsheet and arrange your coordinate pairs by putting Lat1, Lng1, Lat2, and Lng2 values in separate adjacent columns (e.g., Columns A through D).
2. Enter the Haversine formula: Select a blank cell in your distance column. Enter the mathematical formula using WPS Spreadsheet's built-in math functions, referencing your data cells.
3. Apply to the entire dataset: Double-click the fill handle (the small square at the bottom-right corner of the selected cell) to automatically copy the distance calculation down to all rows in your dataset.

100% compatibility with Microsoft Excel (.xlsx) formats and standard formulasSeamlessly processes complex mathematical datasets and trigonometric calculationsLightweight application that opens large coordinate datasets quickly without laggingFree to use for everyday data analysis, reporting, and custom formula creation

microsoft office alternative - wps office

Frequently Asked Questions

Why do I need to use the RADIANS function in the formula?

Spreadsheet software calculates trigonometric functions (like SIN and COS) using radians rather than degrees. Because latitude and longitude are measured in degrees, the RADIANS function is required to convert your coordinate values into a format the math engine can process correctly.

Can I calculate the distance without creating a LAMBDA function?

Yes. If your software version does not support LAMBDA, you can type the math directly into a cell. Assuming Lat1 is in A2, Lng1 in B2, Lat2 in C2, and Lng2 in D2, you can use: =3958.76*2*ASIN(SQRT((1-COS(RADIANS(C2))*COS(RADIANS(A2))*COS(RADIANS(D2-B2))-SIN(RADIANS(C2))*SIN(RADIANS(A2)))/2)).

How do I convert my degrees, minutes, and seconds coordinates to decimal degrees?

To convert Degrees, Minutes, and Seconds (DMS) to decimal degrees in a spreadsheet, use the formula: Degrees + (Minutes / 60) + (Seconds / 3600). Ensure you format the result as a standard number.

Excel & Data Problems