How to build a service area map from a customer list in Google Sheets

To build a service area map from a customer list, install the InstaMaps add-on in Google Sheets, geocode your addresses using =GEOCODE(A2:A500), and deploy =INSTAMAP(B2:B500) to generate a live, shareable map URL. This plots your entire base instantly without exporting data to third-party tools.

This workflow is designed for field service managers, HVAC dispatchers, and franchise operators handling dense local routes. Instead of guessing if a new lead falls outside your standard patch, you end up with a dynamic dashboard showing precise locations, radius boundaries, and automated territory splits that update as your sheet changes.

Preparing your customer data

Field-service spreadsheets require a strict column layout to process location data. A standard structure uses Column A for Customer Name, Column B for Raw Address, and Column C for Cleaned Address. Raw addresses exported from CRMs or scheduling boards frequently contain double spaces, trailing commas, or localised formatting variations that trigger geocoding errors.

Enable the InstaMaps sidebar by navigating to Extensions > InstaMaps > Enable formulas. Instead of manually trimming text, click the Build-the-workflow button or insert =CLEAN_ADDRESS(B2) into cell C2 to normalise capitalisation and standardise abbreviations (for example, converting 'Rd' to 'Road'). Drag this formula down to C201 to prepare a 200-home customer list.

Because CLEAN_ADDRESS runs locally, it does not consume your daily lookup quota. However, translating those cleaned text strings into coordinates does. The free tier permits 100 lookups per day. If your service base exceeds this, use the free email unlock inside the add-on to increase the limit to 1,000 lookups per day, allowing you to process the entire 200-home roster in a single batch without hitting an #OVERQUERYLIMIT error.

1. Column A: Account Name

2. Column B: Raw text address

3. Column C: Cleaned address (using =CLEAN_ADDRESS())

4. Column D: Latitude (extracted via =GEOCODE)

5. Column E: Longitude (extracted via =GEOCODE)

Step-by-step: Generate the base service area map

1. Convert addresses to coordinates: In cell D2, enter =GEOCODE(C2). This queries the database and returns the latitude and longitude. Drag this down to D201 to cover the 200-home farm. If a cell returns #N/A, it indicates an invalid address format or a location not found in the database.

2. Generate the map: Select an empty cell, such as G2. Use the InstaMaps sidebar to insert =INSTAMAP(D2:D201, A2:A201). This references the coordinate range and the account names.

3. Open the hosted map: When the formula executes, cell G2 displays a live hosted shareable map URL. Clicking this opens a browser window displaying all 200 customer points.

The primary advantage of this method is the live link between the spreadsheet and the map. When a customer cancels and you delete row 45, or when a new home is added to row 202, the URL automatically reflects the updated map. There is no need to re-export data or manually refresh a static image. The hosted map reads directly from the specified ranges. If you add 5 new rows to the bottom of the sheet, extend the =GEOCODE() formula down, and the =INSTAMAP() range will immediately include the new properties.

Radius checks: Filtering out-of-area calls

Dispatching crews outside your operational area destroys route margins. To filter out-of-area calls automatically, use =WITHIN_RADIUS() to check each customer against your central depot.

First, define your depot. In cell H2, input the depot coordinates (for example, 51.5074, -0.1278). In cell H3, input your maximum service distance in kilometres (for example, 45).

In cell F2 of your customer table, enter the following formula: =WITHIN_RADIUS(D2, $H$2, $H$3). Ensure you use absolute references ($H$2 and $H$3) for the depot coordinates and radius limit; otherwise, dragging the formula down will shift the target coordinates down empty rows. Drag this formula down to F201.

The function evaluates the distance between the coordinates in D2 and the depot in H2. If the distance is less than or equal to 45 kilometres, the cell returns TRUE. If the customer is 52 kilometres away, the cell returns FALSE. If cell D2 contains text instead of coordinates due to a failed geocode, =WITHIN_RADIUS() returns a #VALUE! error.

With a 5-crew operation, assigning a job 50km away pulls a technician out of a dense 47-stop cluster, wasting drive time. Apply a filter to Column F and sort by FALSE. This instantly isolates the out-of-area accounts, allowing you to decline the job before a dispatcher assigns it.

Patch splitting: Assigning technicians by region

When managing multiple crews, you must divide the customer base into distinct operational patches. =TERRITORY() automates this assignment, preventing overlapping dispatch routes.

1. Create a reference table: In cells K2:K5, list the coordinates for the centre of your four patches. In cells L2:L5, type the corresponding technician names or patch labels (for example, Crew A, Crew B, Crew C, Crew D).

2. Assign the patches: In cell G2 of your customer table, enter =TERRITORY(D2, $K$2:$K$5, $L$2:$L$5). This formula checks the coordinates in D2 against the patch centres in K2:K5, identifies the closest centre, and returns the label from L2:L5.

3. Filter dispatch routes: Drag the formula down to G201. If D2 contains coordinates and the closest patch centre is in K3, G2 will output 'Crew B'. A dispatcher can filter Column G for 'Crew B' and immediately export that list to a mobile device.

If two new stops are added to row 202 and 203, the formula automatically calculates which crew's territory they fall into. If =GEOCODE() fails in D2 and leaves the cell blank, =TERRITORY() returns #N/A, clearly flagging the unassigned row before the morning dispatch. You can use =SORT_BY_DISTANCE() in tandem with this to sequence the stops within each assigned patch, ensuring Crew C's 47 stops are routed efficiently without bleeding into Crew A's geographic zone.

Worked example: 200-home maintenance farm, 5 crews

A 200-home property maintenance business operates from a single hub but dispatches five local crews. Today, 47 properties (marked TRUE in column D) require a site visit. The objective is to split these 47 stops into geographical patches and verify that no crew travels outside a strict 15-mile radius from their local home depot.

Column A holds the Customer Name. Column B contains raw text addresses. Because customer input data is notoriously inconsistent, Column C runs =CLEAN_ADDRESS(B2:B201) to standardise the formatting. Column D flags Scheduled Today. Column E applies =GEOCODE(C2:C201) to output precise latitude and longitude coordinates for the entire 200-home farm.

Crew depot coordinates are stored in cells K1:K5. To enforce radius limits, Column F checks the distance to the North Depot (K1) using =WITHIN_RADIUS(E2, K$1, 15). This returns a simple TRUE or FALSE. By dragging this down to F201, the dispatcher can immediately filter out any properties that fall outside Crew 1’s 15-mile patch.

Next, assign the qualifying stops to specific geographical territories. Column G defines the territory patch using =TERRITORY(E2, "North"). This evaluates the coordinates against predefined boundaries. If the coordinates sit inside the northern boundary limits, the cell outputs "North"; otherwise, it remains blank. You repeat this logic for South, East, West, and Central columns.

To visualise this patch split without manual screen-pinning, place =INSTAMAP(FILTER(A2:G201, D2:D201=TRUE)) in cell I1. This filters the 47 active stops and generates a live, hosted map URL. When a customer cancels and you change D45 to FALSE, the map updates automatically to remove the pin.

Finally, prepare the turn-by-turn navigation for the crew's phone. Using the sidebar (Extensions > InstaMaps > Enable formulas), click Build-the-workflow to insert the routing formulas without typing. You cannot send all 47 stops to a single routing link due to Google Maps' strict URL constraints. Filter the sheet to show only the 12 stops assigned to the "North" territory. To sequence these efficiently, run =SORT_BY_DISTANCE(M2:M13, K1) to order the stops based on their distance from the depot. Apply =ROUTE_LINK(M2:M13) to generate the final clickable map link, honouring the exact sequence of the rows.

Limits and honest alternatives

InstaMaps is free, but it operates within strict quota parameters. The free tier permits 100 lookups per day. Registering with an email address unlocks 1,000 lookups per day. If your 200-home farm requires geocoding 250 new customer addresses in a single afternoon, the system will process the first 1,000 and return a quota error for the remainder until the daily reset.

Routing constraints are dictated by Google Maps' official URL scheme, not the add-on itself. The =ROUTE_LINK() function strictly caps at exactly 11 stops (10 waypoints plus the destination). If you pass a range containing 15 addresses, the formula will return an exact URL truncation error, omitting the final 4 stops. You must manually split 30-stop routes into three separate 10-stop links, or use =VISIT_ORDER() to identify the optimal stopping sequence before chunking the data into batches of 11.

A spreadsheet-native workflow is not the correct environment for dynamic, real-time fleet tracking. If your operation requires GPS pings every 30 seconds, dynamic ETAs that adjust based on live traffic, or automated dispatching based on crew availability, you require dedicated fleet-routing software.

InstaMaps is built for planning, static territory analysis, and route preparation. If your dispatchers spend three hours every morning manually plotting pins on a map and typing addresses into navigation apps, this tool eliminates that manual bottleneck. If your dispatchers need to reroute 50 trucks automatically because a bridge closed, a spreadsheet will not support that reaction time.

Who this workflow is for

This spreadsheet mapping workflow targets a very specific operational profile.

1. **Buy it if:** You are an operations manager or dispatcher running a small to medium field-service business (2–10 crews) and your customer database lives entirely in Google Sheets or Excel. You spend the first two hours of your morning matching addresses to crew patches and need a visual, automated way to split territories without buying enterprise CRM software.

2. **Skip it if:** You manage a national logistics network, operate a 50-vehicle fleet, or require live telemetry. Spreadsheets lack the processing speed and API depth to handle highly constrained routing problems like vehicle capacity limits, multi-day sequencing, or live traffic recalculations. Heavy-duty operations require dedicated routing algorithms.

3. **Alternative approach:** If you already pay for Jobber, Housecall Pro, or ServiceTitan, check their native mapping modules first. InstaMaps is strictly for businesses that cannot justify £100+ monthly software subscriptions and prefer to manage their service area map directly from their existing customer list spreadsheet.

Common Questions