Say that you have a list of cities in Excel and need to know the latitude and longitude for each city. A new Geography Data Type feature coming to Office 365 will make this easy. Check the Data tab in the Excel ribbon. Do you have a new Data Type category with Stocks and Geography? Create a Map chart with Data Types. Map charts have gotten even easier with geography data types.Simply input a list of geographic values, such as country, state, county, city, postal code, and so on, then select your list and go to the Data tab Data Types Geography. Google has developed a service for converting addresses (like “555 Main St., Anywhere USA, CA”) into geographic coordinates (like latitude 36.411022 and longitude -120255489). The service, made possible by the Google Maps Geocoding API, is commonly used online to quickly convert physical addresses to coordinates. It allows you to copy & paste the address and get the GPS values from the text section. The example below is based on Euro 2016 stadiums in France. When pasted into Excel depending on your local settings you might need to replace the dots with the comma. You need also to prepare data in the order: first Longitude, then Latitude. A tool to convert latitude and longitude coordinates from degress/minutes/seconds to decimal format, and viceversa. Main features: - Navigate along all the points converted using a Bing map - Export the history using the sharing feature of Windows 8 - Tap on the map to get the latitude/longitude coordinate of the point - Zoom in/out on the map to reach a great precision This app supports.
See how to calculate distance in Excel, using Latitude and Longitude, in this tutorial by Excel MVP, Jerry Latham.
Getting Started
For those who are in a rush for the solution and don't need all the background information, jump to the longitude latitude code. Or download the calculation workbook, and enter your longitude and latitude.
The search for an accurate solution to this problem has led me to numerous sites and attempted solutions. A long list of related sites is included at the end of all of this, but the most crucial to what I've found to be the current end of the road are these:
along with
Accuracy is Important
A formula that is accepted to provide results that are accurate to within millimeters is known as Vincenty's formula. Naturally the accuracy of the results depends in large part on the accuracy of the latitude/longitude pairs describing the two points.
Why all the fuss over accuracy? Well, from what I've seen of other formulas, especially those written as a single worksheet function, their values differ quite a bit for what might be considered 'life critical' situations. Typically they are short by some number of meters, typically about 20 to 30 feet per statute mile, and after flying just 30 or 40 miles, I wouldn't care to land several hundred feet short of the approach end of a runway, much less be off by over 7 miles on a trip between Los Angeles and Honolulu.
Since the general tendency in dealing with these types of calculations is to 'measure it with a micrometer, mark it with chalk and cut it with an axe', what we have here is a very precise micrometer to begin the measuring process with.
Excel Formulas for Distance
There are numerous Excel worksheet functions that will return an initial heading from one point to the destination point for a Great Circle path between them and similar formulas to return the distance between them.
But based on experience using them and comparing them to known measured distances, the Vincenty method of calculating the distance between the points has not been translated into a single Excel worksheet function, and very likely cannot be at least not easily.
Because it relies on reiterative calculations to deal with points that are very close to being on the exact opposite sides of the world, implementing it as even a series of Excel worksheet formulas is a daunting task.
Creating Excel Code for Distance
The basis for the solution presented by T. Vincenty can be found here:
During my search I first found the movable-type.co.uk page which presented the coded solution in JavaScript. That didn't do me much good as far as coming up with an Excel VBA solution; so I continued the search and finally found the lost-species.livejournal.com page!!
I thought the search was over. Almost, but not quite.
When I moved that code into an Excel code module I was confronted with two sections that Excel not so politely informed me were 'too complex' for it to evaluate. I managed to break those down into simpler statements that Excel could deal with and that did not corrupt the final results. That is the sum total of my contribution to the function provided below. I claim nothing more than that small contribution.
Working With Angles in Excel
But before using the function, there are a few preliminary steps that must be considered. Most significant is that Excel and VB works with angles expressed in radians, not as decimal values of the angles, nor from their initial 'plain English' representation.
Consider this situation:
You have a Latitude represented as 10° 27' 36' S (10 degrees, 27 minutes 36 seconds South)
You need to get that into radians, and there is not a direct way to do it, before we can get it to radians it has to be converted into a decimal representation. We need to see it as: 10.46 which is the decimal equivalent to 10° 27' 36' and we need to take into consideration whether it is a North or South latitude, with South latitudes being treated as negative numbers.
Luckily Microsoft provides a couple of handy functions to convert standard angular notations to their decimal equivalent, and back, at this page:
Those routines are included at the code section and one of them has a change made by me to permit you to make a regular angle entry as
10~ 27' 36' S instead of 10° 27' 36' S
because ~ is directly accessible from the keyboard, while the ° is not.
Converting to Radians
After converting the standard notation to a decimal value, we still have to convert that to radians and deal with the sign of the radian result.
The routines and formulas here consider negative latitudes to be South latitudes and negative longitudes to be West longitudes. While this may seem unfair to those of us living in the western hemisphere, what can I say other than deal with it!?
A decimal degree value can be converted to radians in several ways in Excel and for this process, a simple function is used that is also included in the code presented later.
The basic formula is
radians = angleAsDecimal x (Pi / 180)
where Pi is 3.14159265358979
The Final Solution
Copy all of the code below and paste it into a regular code module in your workbook. Instructions for placing the code into a regular module are here: Copy Excel VBA Code to a Regular Module
Set up your worksheet to pass the latitudes and longitudes of the start and end points as standard entries, then enter a formula to pass them to function distVincenty().
Given two points with these coordinates:
Point 1:
- Latitude: 37° 57' 3.7203' S
- Longitude: 144° 25' 29.5244' E
Point 2:
- Latitude: 37° 39' 10.1561' S
- Longitude: 143° 55' 35.3839' E
The general format of the function call is:
=distVincenty( Pt1_LatAsDecimal, Pt1_LongAsDecimal, Pt2_LatAsDecimal, Pt2_LongAsDecimal)
A raw formula would look like this [Note the double-double quotes after the seconds entries]. The SignIt() function, provided as part of the code, converts a standard angular entry to signed decimal value.
=distVincenty(SignIt('37° 57' 3.7203' S '), SignIt('144° 25' 29.5244' E'), SignIt('37° 39' 10.1561' S'), SignIt('143° 55' 35.3839' E'))
You can use the ~ symbol instead of the ° symbol if it makes it easier for you:
=distVincenty(SignIt('37~ 57' 3.7203' S '), SignIt('144~ 25' 29.5244' E'), SignIt('37~ 39' 10.1561' S'), SignIt('143~ 55' 35.3839' E'))
If the coordinates for Point 1 are in B2 and C2 and the coordinates for Point 2 are in B3 and C3, then it could be entered as
=distVincenty(SignIt(B2), SignIt(C2), SignIt(B3), SignIt(C3))
The result for the 2 sample points used above should be 54972.271, and this result is in Meters.
Problems Encountered
The initial code that I began working with generated 'formula too complex' errors in two statements. These statements were initially broken into two pieces and then those two separate calculations were 'rejoined' in a formula to obtain their final result without the 'formula too complex' error.
During the preparation of this document, the package was tested in Excel 2010 64-bit and began returning #VALUE! errors for all input values. Investigation determined that a portion of the already split formula to determine the deltaSigma value was generating an overflow error. This error did not occur in the 32-bit version of Excel 2010, nor in Excel 2003 (a 32-bit application).
The offending line of code was once again broken down into smaller pieces that were eventually rejoined in a mathematically correct process that resulted in the proper values being determined without resorting to any alteration of the original algorithm at all.
These sections are noted in the comments in the code for Function distVincenty() below.
For any questions regarding all of this, contact Jerry Latham at HelpFrom@JLathamSite.com.
The Longitude Latitude Code
Now, at last, the code:
Download the Workbook
To see the code, and test the formulas, download the Excel Distance Calculation sample workbook. The file is in xlsm format, and is zipped. There are macros in the workbook, so enable the macros if you want to test the code.
Excel Function Tutorials
Many mapping tools (and other applications like phones and GPS units) are able to create maps directly from columns containing latitude and longitude data. To use these in Excel-based mapping tools sometimes requires you to modify or transform the data. In this article we're going to go through a number of ways that you can quickly manipulate the latitude and longitude data you already have, and show accurate, high performing maps with 10000s of points.
We've enabled comments for this discussion thread. Scroll to the bottom to ask questions and make comments.
Quick(ish) background
Latitude and longitude are used to locate points on the globe as a pair of numbers. Latitude represents how far north/south the point is and longitude represents how far east/east the point is. The numbers are often expressed in degrees because the earth is approximately a sphere. Because we can travel all around the world we use 360 degrees of longitude for a full circle and 180 degrees of latitude for the half circle of north-south. To make life more interesting, we don't use 0-360 longitude and 0-180 degrees latitude though. We use -180 to 180 degrees longitude and -90 to 90 degrees latitude.
(Source: Wikipedia)
0 degrees longitude passes through the 'prime meridian' of Greenwich, in south west London, England. Points west of this are negative longitude, points east are positive. 0 degrees latitude is the equator with positive numbers representing the northern hemisphere and negative the southern hemisphere.
See https://en.wikipedia.org/wiki/Geographic_coordinate_system for more of the technical details!
Numeric format conversions
The most common step necessary to get existing longitude latitude data successfully into maps is to convert the the values into a format that the Excel maps can understand. Here are some of the most common conversions:
Converting from degrees, minutes and seconds to decimal degrees
Probably one of the most common formats for latitude and longitude is the degree format of degrees minutes seconds (DMS). For example (38.897680 , -77.03653) becomes (38° 53' 51.648' N 77° 2' 11.508' W). Degrees, minutes and seconds breaks down angles a little bit like the hands of the clock into hours, minutes and seconds (hence the name). To show these in many mapping tools such as Visual Crossing requires us to convert from DMS to decimal degress using the formula:
Microsoft has created a series of macros that provide a function for performing the conversion - see https://support.microsoft.com/en-us/help/213449/how-to-convert-degrees-minutes-seconds-angles-to-or-from-decimal-angle.
Excel Mac 2016 Get Longitude Latitude Coordinates For An Address In China
Your data contains prefixes rather than negative and numbers (eg 38.897680N , 77.03653W)
Sometimes you will encounter latitude, longitude data such as (38.897680N , 77.03653W). The N indicates 'North of the equator' and the W indicates 'West of the prime meridian'. In these cases we need to remove the letter suffixes and replace them with negative values where appropriate. Negative values are for 'West' and 'South'. Therefore we use (38.897680 , -77.03653) as our final result. An Example Excel formula to clean up the 'W' and 'S' suffix would be:
Your data contains values that aren't in the right range
As we mentioned above, most mapping tools support latitudes from -180 to 180 longitude and -90 to 90 degrees latitude. If your data is between 0 to 360 and 0 to 180 degrees, simply subtract 180 from the longitude and 90 from your latitude! Sometimes you may encounter data that goes beyond the range. For example, some latitude and longitude data may be expressed in radians rather than degrees or other differences related to the projection of the data.
Making sure the values have enough precision
One of the most common reported problems users have is when a map of points resembles this display:
The real map should look like this.
This problem occurs because the values don't have enough precision - typically you want at least five or six digits after the decimal point to produce an accurate business map (some highly accurate use cases may require even greater precision!) The rounding of the numbers causes the values to snap to a grid-like appearance like the first screenshot. The fix for this common problem is to review your data to ensure that you have at least 5 digits after the decimal point.
How can you use longitude & latitude values in Excel? Try Location Analysis in Excel or try retrieving historical and forecast weather data
Final thoughts
Once you have your data in the right format with enough precision you will be able to construct high perfoming maps in many applications. If you are looking to investigate location demographics or weather trends, consider using Location Analysis or Weather Data.
