Excel As-the-crow-flies distance

The demo file offers three ways to calculate the as-the-crow-flies distance, the shortest distance between two geographical locations specified by latitude and longitude
  1. Regular formula. As it is a pretty long one, it might be a bit cumbersome to adjust it and link it to input fields.
  2. VBA - User Defined Function (UDF) GlobeDistance. Much easier to read and link. But will execute noticeable slower if you use this function in thousands of cells (programing language VBA is slow). You can set its optional argument InMiles to TRUE/FALSE to retrieve distance in miles/kilometers. Default is in kilometers!
  3. LAMBDA function L_GlobeDistance. LAMBDA functionality exists since Office365/Excel 2021. LAMBDA functions are user defined functions created directly within Excel's Name Manager, without VBA code. Executes fast. You can set its optional argument InMiles to TRUE/FALSE to retrieve distance in miles/kilometers. If omitted, then it returns the distance in kilometers!

     Download demo file  (32kB - downloaded 2516 times)


Regular formula

Dependent on your Excel setting you need to use semicolon or comma in formulas. Best is to open the demo file and copy the formula from there, as it will contain your local settings. Else - if required - copy in function in text editor and replace comma by semicolon before copying into Excel.

Given the following input fields (in degrees): From latitude (cell A4), From longitude (cell B4), To latitude (cell C4), To longitude (cell D4), InMiles (cell E4) the formula is


User Defined Function GlobeDistance: VBA code to be put in a module

In MS Excel, press Alt+F11 to open the Visual Basic Editor (Windows). Via top menu: Insert → Module. Copy the MS Excel VBA code below into the module.


Note: in general, a VBA user defined function will execute slower than a standard function, which will become noticeable if you use this function in thousands of cells.

LAMBDA Function L_GlobeDistance

Dependent on your Excel setting you need to use semicolon or comma in formulas. Best is to open the demo file and copy the formula from there, as it will contain your local settings. Else - if required - copy in function in text editor and replace comma by semicolon before copying into Excel.