First convert each address into latitude and longitude. Then use the Haversine formula with an Earth-radius constant of 6,371.0088 kilometers or 3,958.7613 miles. The result is great-circle distance—not driving distance, travel time or a route.
Coordinates must be numeric and ordered correctly
Put latitude in one column and longitude in the next. Latitude is bounded by ±90 and longitude by ±180. Keep the status column until bad rows are filtered; an empty coordinate should not quietly become zero.
| Cell | Value |
|---|---|
| B2 | Origin latitude |
| C2 | Origin longitude |
| B3 | Destination latitude |
| C3 | Destination longitude |
| D3 | Calculated distance |
Use the Haversine formula for straight-line distance
=2*6371.0088*ASIN(SQRT(SIN(RADIANS(B3-B2)/2)^2+COS(RADIANS(B2))*COS(RADIANS(B3))*SIN(RADIANS(C3-C2)/2)^2))=2*3958.7613*ASIN(SQRT(SIN(RADIANS(B3-B2)/2)^2+COS(RADIANS(B2))*COS(RADIANS(B3))*SIN(RADIANS(C3-C2)/2)^2))Wrap the expression in an IF that requires all four inputs when applying it to incomplete tables. Round only for display; preserve the unrounded value if later calculations depend on it.
Test boundary cases before filling down
- Identical coordinates return zero.
- Swapping the two locations returns the same distance.
- Latitude and longitude are not reversed.
- Blank rows remain blank rather than calculating from zero.
- A known city-to-city example is geographically plausible.
- The displayed unit is labeled clearly.
Use routing data for operational travel
Driving distance and time require a road-network service. Google's Routes API can accept coordinates, addresses or place IDs and applies road access and travel mode. That service has separate credentials, pricing, quotas and terms from geocoding.
Sources and methodology
Technical claims were checked against the sources below on August 18, 2026. Product limits and third-party pricing can change; verify the live documentation before designing a production workflow.
Common questions
Does the Haversine formula return driving distance?
No. It returns the shortest great-circle distance over a spherical Earth model. Roads, borders, terrain, ferries and one-way restrictions are not included.
How do I return miles instead of kilometers?
Use an Earth-radius constant of approximately 3,958.7613 miles instead of 6,371.0088 kilometers.
Why does my formula return an error?
The usual causes are blank coordinates, text-formatted numbers, swapped latitude and longitude, decimal commas, or latitude outside -90 to 90.
How accurate is Haversine distance?
It is appropriate for many spreadsheet comparisons but assumes a sphere. High-precision surveying, very short engineering distances and legal measurement require more appropriate geodesic methods.