Calculate Distances Between Two Addresses or Points

The shortest distance between two points is a straight line, but how long is that line? When it comes to those two points, you may know them as GPS coordinates, or you may only have addresses. Even if you know each latitude and longitude, the math behind the distance calculation is complicated. There are several approaches and tools you can use to make it easier, even bringing the calculations to every row of a spreadsheet. We’ll cover a few options below to get you started with calculating distances.

Many Distances from a Single Place

Calculating a single distance is easy. In fact, we do it all the time using Google Maps and other directions services. Of course, that distance is typically a driving distance instead of the straight line distance. Nevertheless, when you find yourself needing to know multiple distances, the most common reason is you want to find out which locations are closest to single point, such as your current location.

Let’s say you have a spreadsheet of addresses of your city’s mechanics. Your car is broken down and you don’t want to have to push or tow it any farther than necessary.

Add your address, or the address of wherever you want to use as a reference location in the first row of your spreadsheet, just after the header row.

Now highlight and copy your entire spreadsheet (including the header row) and paste it into our simple map making tool. Click Validate and Set Options, then choose Advanced Options. Check the option to calculate distance from the first address. Then click Make Map.

View Santa Monica Mechanics in a full screen map

Now when you see the map, the distance is listed along with the other data with each marker. Since we used letters to label the markers, we know that Marker A is our first row, which is used to calculate the distance for all the other markers.

Break Out Your Digital Measuring Tape

Calculating the distance across all your locations is incredibly useful. Yet, sometimes you want to perform ad hoc measurements between many markers. That’s where the measuring tools that are part of BatchGeo’s Advanced Mode come in handy.

You can check whether Advanced Mode is active, and activate it if necessary, using the Pro menu in the upper right corner of any map. You must be a BatchGeo Pro member and logged in to use Advanced Mode. If Advanced Mode is active, you’ll see several buttons near the zoom controls in the upper left corner of your map.

Select the measuring tool, the button with a ruler icon. The hand cursor will become a plus sign target. Now click and hold where you want to begin a measurement (such as one of the markers). Next, drag the cursor to the end of your measurement. As you drag, you’ll see the current distance from the initial point to the current cursor. To switch between metric and imperial systems, click the scale on the bottom right of the map.

Deploy the Haversine Formula in Your Spreadsheet

The absolute best way to view geographic data is from a map, which is why BatchGeo lets you map Excel data (and other spreadsheets) with our simple copy-paste interface. However, if you need the distances in your spreadsheet directly, you’ll need to include a complex series of functions.

Let’s say you know the latitude and longitude points of those mechanics stored in columns B (latitude) and C (longitude) of your spreadsheet. Using a formula derived from this site, you could calculate the distance from Fleece’s Greases (row 3) to your location (row 2) using this formula:

=3958*ACOS(SIN(B$2*PI()/180)*SIN(B3*PI()/180) + COS(B$2*PI()/180)*COS(B3*PI()/180)*COS(C3*PI()/180-C$2*PI()/180))

You can change the 3,958 — the approximate radius of the earth in miles — to another unit, such as kilometers (6,371), meters (6,371,000), or feet (20,898,240). The rest is advanced math based on the spherical law of cosines. The important parts are the B$2 and C$2, which ensure you’ll compare other rows in the spreadsheet to your location, affixed in that second row. When you copy and paste this formula, the other fields will update to correctly reference the current row.

The haversine formula only works if you know your latitude and longitude points. If all you have is addresses, BatchGeo can help: create a map with measurements from a single distance (as in the first section above), and copy the data back out to your spreadsheet. Go to edit your map, copy the entire data, and you’ll see the distance as the final column when you paste into an empty spreadsheet.

The best part is that your map will still be there as a visual representation of the same data. Naturally, we think a map is a great way to augment the data in your spreadsheet. These different methods of calculating distances have varied levels of difficulty and usefulness, depending on the data you have available. Why not try creating a map now for free?