Distance from one address to many in Google Sheets

Q: How do I calculate distance from one address to multiple addresses in Google Sheets?

Put each destination on its own row and repeat the origin in a column beside it. In MapRows, choose those columns on the Distance tab. Straight-line distance is free; driving, walking and cycling distance require Pro.

Q: Can I find customers within 25 miles of my store for free?

Yes. With coordinates for the store and customers, use the free MAPROWS_DISTANCE function for straight-line miles, then filter for distances of 25 or less. The result is a geographic radius, not a 25-mile driving area.

Q: Do I need to geocode the same store address for every customer?

You can geocode the store once and repeat its coordinates in origin columns. With coordinates at both ends, MapRows straight-line distance uses no lookups. For a Pro route, each row still costs one lookup for the route.

Q: Does MAPROWS_DISTANCE calculate driving distance?

No. MAPROWS_DISTANCE calculates great-circle straight-line distance from coordinates. MapRows Pro provides driving, walking and cycling distance and travel time through the Distance tab.

Q: How many lookups do 100 customer routes use?

With an address in both the origin and destination cells for each row, allow about 300 lookups: one for each address end and one for each route. With coordinates at both ends, allow about 100 route lookups.

Q: Why does a customer appear within 25 straight-line miles but outside 25 driving miles?

The straight-line measurement follows the great-circle distance between coordinates. A road route must follow available roads, so it can be longer. Use a driving-distance result when the decision depends on miles traveled by road.

Q: What should I check if a coordinate row says INVALID_INPUT?

Check that latitude and longitude are numeric and in the right order. Latitude must be between -90 and 90, and longitude between -180 and 180. For the Distance tab, use a readable lat, lng pair in each selected location cell.

The short answer

To calculate distance from one address to multiple addresses in Google Sheets, give each destination its own row and repeat the origin beside it. In the Distance tab of MapRows, select the Origin and Destination columns, choose a distance type and choose miles or kilometres. Each row represents one pair, such as a shop and one customer.

MapRows Free calculates great-circle, or straight-line, distance. If both ends are coordinates, that calculation uses no lookups. The free MAPROWS_DISTANCE function also calculates straight-line distance from coordinates and works on whole ranges. Driving, walking and cycling distance and travel time require MapRows Pro.

For a list of customers within 25 miles, calculate the distances in miles and filter for values at or below 25. Be clear about which distance you mean: a 25-mile straight-line radius is useful for an initial geographic screen, but it is not a 25-mile delivery radius measured along roads. A customer inside the circle can still require a longer drive.

You do not need an add-on for every version of this task. Our haversine formula guide shows a free spreadsheet formula for straight-line distance. Our driving distance guide has a free Apps Script method for road distance and travel time, subject to its own quota and terms. Choose the result that answers your question before choosing a tool.

Set up one origin and many destinations

Start with a header row and one customer per row. Suppose a sheet named Customers has customer names in column A, addresses in B, latitude in C and longitude in D. Put the shop latitude in H1 and its longitude in H2. The coordinates below are examples to show the arrangement, not a verified customer list or service area.

Row	A: Customer	B: Address	C: Lat	D: Lng	E: Distance mi
2	Customer A	Address A	`40.75`	`-73.98`	Distance result
3	Customer B	Address B	`40.72`	`-74.00`	Distance result

For this example, enter 40.7484 in H1 and -73.9857 in H2. In a working sheet, replace those values with the checked coordinates for your own shop. Keep latitude and longitude in separate numeric cells for the formula method. The customer coordinates also need to belong to the address on the same row.

The Distance tab expects one Origin column and one Destination column. Each selected cell can contain an address or a lat, lng pair. It measures one origin and one destination on each row; it does not automatically compare a single origin cell with the whole customer list. Repeat the origin down a column so every customer has a corresponding origin.

For an address-based run, put the full shop address in H3, label an empty column Origin and enter =$H$3 in its first customer row. Fill it down through the customer list. Select that column as Origin and the customer Address column as Destination. The dollar signs keep each row pointing to the shop address in H3.

For a coordinate-based run in the Distance tab, create a column of readable coordinate pairs. For example, with the shop coordinates in H1 and H2, enter =$H$1&", "&$H$2 in the first Origin cell and fill it down. A Destination cell can similarly combine the customer coordinates with =C2&", "&D2. Check the resulting pairs before selecting the columns, especially if some customer coordinates are missing.

To open the sidebar, install MapRows, then choose Extensions → MapRows → Open MapRows. On Distance, choose the two location columns, the distance type and units. Keep the original customer details together with the results so you can identify which distance belongs to which address. MapRows writes results in new columns and does not edit your own columns or formulas.

Calculate straight-line distance from coordinates

If you already have reliable latitude and longitude for the shop and every customer, you can use the free formula without an address lookup for each distance. If you have addresses instead, first turn them into coordinates. Our geocoding guide explains free methods and the MapRows workflow.

In MapRows, choose the Geocode tab, select the sheet and header row, tick the address column, choose coordinates as the output and run the selected or all rows. Geocoding an address costs one lookup. Review the results before measuring: an incomplete address can match a town or other broad area instead of the intended building. Compare the matched address when necessary and investigate LOW_CONFIDENCE or NO_MATCH statuses.

For the formula example, assume customer latitude and longitude are in C2:C100 and D2:D100, and the checked shop coordinates remain in H1 and H2. Make two constant origin columns in unused space. Label I1 as Origin lat and J1 as Origin lng. Enter =$H$1 in I2 and =$H$2 in J2, then fill both formulas down to row 100.

Enter this whole-range formula in an empty result column, for example E2:

=MAPROWS_DISTANCE(I2:I100, J2:J100, C2:C100, D2:D100, "mi")

The four ranges represent origin latitude, origin longitude, destination latitude and destination longitude, in that order. They cover the same rows, so the shop coordinates on row 2 are paired with customer coordinates on row 2, and so on. Leave space below the formula for its results. Adjust all four range endpoints together when your list has a different last row.

For a single row, the equivalent is =MAPROWS_DISTANCE(I2, J2, C2, D2, "mi"). In row 2 of the example, it compares 40.7484, -73.9857 with 40.75, -73.98 and returns a distance in miles. The origin columns do not change which customer is being measured; they repeat the shop’s coordinates so each pair is explicit.

The formula calculates great-circle distance inside the cell and uses no lookups. It accepts "mi", the default, or "km". It does not geocode an address or calculate a route. Keep incomplete customer rows out of the measurement range until you have usable coordinates, and check results that will affect a real decision.

Filter customers within a radius

Once column E contains valid straight-line distances in miles, a helper column makes the 25-mile test easy to read. Label F1 Radius check. In F2, enter the following formula and fill it down for rows with valid distances:

=IF(E2<=25,"Within 25 mi","Outside")

A distance of 18 produces Within 25 mi; a distance of 27 produces Outside. A result of exactly 25 is included because the comparison uses <=. Apply the formula only where E contains a valid numeric distance. Do not treat a blank, an error or an unverified geocode as evidence that a customer is inside or outside the area.

To produce a separate list from the example range, put this formula in an empty area with room for the results:

=FILTER(A2:F100, ISNUMBER(E2:E100), E2:E100<=25)

It returns customer rows whose distance is numeric and no more than 25 miles. Check the range endpoints when adding customers, and keep the distance column in miles. If you want the nearest customers first, use a sorted version in an empty area:

=SORT(FILTER(A2:F100, ISNUMBER(E2:E100), E2:E100<=25), 5, TRUE)

The 5 identifies the fifth column of the returned range, which is Distance mi in this layout. If you change the order or size of the returned columns, check that the sort column still points to the distance. Keep the unsorted source rows together so a customer name, address and coordinates do not become separated.

This list answers a straight-line question. For a delivery decision based on actual road miles, route the candidates and apply the 25-mile test to the driving-distance column instead. Someone just inside a straight-line radius is not necessarily reachable within the same number of driving miles. For a boundary case, inspect the address and route before making a commitment.

Compare straight-line and road distance

A great-circle distance is the shortest distance over the Earth’s surface between the two coordinates. A road route follows a network instead, so straight-line distance is shorter than road distance. It is useful for finding nearby candidates, while a driving result is more relevant when your rule concerns miles on the road. Walking or cycling can call for a different route again.

MapRows Pro provides driving, walking and cycling distance, plus travel time in minutes and as text. On the Distance tab, each Origin and Destination cell can be an address or a lat, lng pair. Select the travel mode and units appropriate to the question. The free formula and the Free plan’s straight-line measurement do not provide road travel time.

Address inputs affect lookup use. MapRows geocodes each address end for one lookup, and a driving, walking or cycling route costs one additional lookup. A coordinate end does not need that address lookup. For example, with an address origin and an address destination on each of 100 customer rows, allow about 100 × 3 = 300 lookups: two address lookups and one route lookup per row.

If both ends are already coordinates, the same 100 routes need about 100 × 1 = 100 lookups for the routes. If only the shop is coordinates and each customer is an address, allow about 100 × 2 = 200. These examples assume one successful measurement per row and no other lookup use. They are planning estimates, not a promise about retries or shared capacity.

Repeating the shop address makes the layout convenient, but do not assume the repeated text is free to geocode for every row. If you want to avoid address lookups in the distance step, first obtain and check the shop and customer coordinates, then use those coordinates as both ends. Geocoding the customer list still has its own lookup cost.

A straight-line screen can reduce the number of routes you need to inspect. Calculate straight-line distances first, then route the rows relevant to your rule. Keep the distinction visible in your headers, for example Straight-line mi and Driving mi, so someone reading the sheet does not mistake one measurement for the other. A route result is an estimate; check important destinations and the route before relying on it.

Plan lookup use for 500 customers

MapRows Free includes 100 lookups per day and straight-line distance. Driving, walking and cycling routes require Pro, which includes 2,500 lookups per day, subject to fair use. Pro costs $9 per month or $59 per year, tax included. See the pricing page for the plan details.

For 500 customer rows with an address at both ends, budget about 500 × 3 = 1,500 lookups for one road-distance run: 1,000 address-end lookups and 500 route lookups. That is below the Pro daily allowance if the full allowance is available, the rows finish successfully and shared capacity is available. Other work that day reduces the remaining allowance.

Alternatively, if the 500 customer addresses have already been geocoded and the shop coordinates are also available, budget about 500 lookups for 500 routes. Obtaining those customer coordinates with MapRows costs about 500 lookups beforehand, assuming one address lookup per customer. If the shop also needs one address lookup, allow about one more for it. Do not count the customer geocoding as free simply because the later route uses coordinates.

For straight-line distance from those checked coordinates, the Distance tab uses no lookups when both ends are coordinates, and MAPROWS_DISTANCE uses no lookups. On Free, geocoding 500 customer addresses at one lookup each would take about five daily allowances of 100, assuming no other lookup use and available capacity. The shop address, if it also needs geocoding, adds another lookup.

Allowances reset at 00:00 UTC; the sidebar shows the reset time in your time zone. Large sheets run in chunks with a progress card. If an allowance or shared daily capacity runs out, the task pauses and you can press Resume after the reset to continue from the next unfinished row. Closing the sidebar keeps progress; reopen it and resume. Check the progress and statuses before planning the next batch.

Pro also lets you add your own Geoapify key. When you do, MapRows limits stop applying and your Geoapify plan’s limits apply instead. That option is useful to assess if the shared allowance does not fit your workload, but check the other plan’s limits and pricing before relying on it.

When you do not need MapRows

If you have coordinates and only need straight-line distance, a spreadsheet formula may be enough. Our distance between coordinates guide explains the free haversine formula. It lets you calculate the measurement without MapRows. You still need coordinates, a consistent unit and a way to check that each pair belongs to the right customer.

If you need driving distance and are comfortable adding code to the spreadsheet, our driving distance and travel time guide includes a free Apps Script function. Its example accepts an origin and destination and returns miles and minutes. The guide also explains quota, recalculation and storage considerations. Check the applicable limits and terms in your account before using it for a large or recurring list.

For a few locations, the geocoding guide also describes copying coordinates from Google Maps. For a larger list, compare the free script and other methods in that guide with the work of checking and maintaining the results. An address-to-coordinate step remains necessary before either the haversine formula or MAPROWS_DISTANCE can measure straight-line distance from coordinates.

MapRows is a reasonable fit when you want a sidebar, row statuses and pause-and-resume handling, or when its Pro route measurements fit your volume. It is less useful if a formula already answers the question or if you need a full origin-by-destination matrix: the documented Distance workflow measures one pair per row. For several possible stores, see our nearest location guide.

When MapRows contacts its external service, it sends the selected addresses or coordinates to Geoapify. Results use Geoapify and OpenStreetMap data and can be incomplete or wrong. The results stored in your sheet are yours to keep; if you publish them, keep the attribution © OpenStreetMap contributors. Review the documentation for permissions and data handling.

Common problems

The status says INVALID_INPUT. Coordinates could not be read. Check that latitude is between -90 and 90 and longitude is between -180 and 180. For a Distance tab cell, use a readable pair such as 34.05, -118.25. For the formula, use separate numeric latitude and longitude cells in the documented order.

Latitude and longitude are swapped. Compare both values with the source and put latitude first. A swapped pair may still consist of numbers, so do not rely only on whether a formula returns a result. This matters especially when importing a source that presents longitude before latitude. Check the shop and a few customer locations before processing the full list.

The radius result uses the wrong unit. The formula’s unit is "mi" by default, or "km" when specified. The Distance tab also lets you choose miles or kilometres. A threshold of 25 means different things in those units. Label the distance column, inspect the selected unit and update the helper formula only after deciding which unit the rule uses.

The origin column contains unexpected text. Check that every populated row points to the shop cell and that the selected Origin column is the intended one. An address must be a usable address; a coordinate pair must contain the intended latitude and longitude. A header, note or customer name in place of the origin can make a row unsuitable for the intended measurement. Correct the input before running it again.

A customer looks much closer or farther away than expected. Compare the customer’s matched address and coordinates with the original address. Add missing city, state, postcode or country information where it helps distinguish the place. Check the shop coordinates too. A broad match to a town or postcode can yield a plausible distance to the wrong point.

A formula result does not fit in the sheet. The whole-range example needs room for the returned rows. Place it in an empty result area and check that the four input ranges cover corresponding rows. If you use a separate filtered or sorted list, give that result its own empty space as well.

The task paused. Check the sidebar’s usage and progress. A daily allowance or shared-capacity pause keeps progress; resume after the reset. Correct a row’s reported input problem rather than assuming a pause changed its address or coordinates. The MapRows documentation describes statuses and resuming.

Frequently asked questions

How do I calculate distance from one address to multiple addresses in Google Sheets?

Put each destination on its own row and repeat the origin in a column beside it. In MapRows, choose those columns on the Distance tab. Straight-line distance is free; driving, walking and cycling distance require Pro.

Can I find customers within 25 miles of my store for free?

Yes. With coordinates for the store and customers, use the free MAPROWS_DISTANCE function for straight-line miles, then filter for distances of 25 or less. The result is a geographic radius, not a 25-mile driving area.

Do I need to geocode the same store address for every customer?

You can geocode the store once and repeat its coordinates in origin columns. With coordinates at both ends, MapRows straight-line distance uses no lookups. For a Pro route, each row still costs one lookup for the route.

Does MAPROWS_DISTANCE calculate driving distance?

No. MAPROWS_DISTANCE calculates great-circle straight-line distance from coordinates. MapRows Pro provides driving, walking and cycling distance and travel time through the Distance tab.

How many lookups do 100 customer routes use?

With an address in both the origin and destination cells for each row, allow about 300 lookups: one for each address end and one for each route. With coordinates at both ends, allow about 100 route lookups.

Why does a customer appear within 25 straight-line miles but outside 25 driving miles?

The straight-line measurement follows the great-circle distance between coordinates. A road route must follow available roads, so it can be longer. Use a driving-distance result when the decision depends on miles traveled by road.

What should I check if a coordinate row says INVALID_INPUT?