Getting distance between two cities into spreadsheet by putting down their names

The request that a student posted on Reddit—automatically pulling city‑to‑city distances into an Excel carbon‑footprint model—touches on a broader shift in how we treat data as a living part of our workflow. Rather than treating a spreadsheet as a static ledger, the user is looking to embed live, contextual knowledge directly where decisions are made. That vision aligns with the ideas explored in our recent piece on Building an Evaluation Harness for Production AI Agents: A 12‑Metric Framework From 100+ Deployments, where we argued that AI‑enabled tools must move beyond batch processing to become real‑time collaborators. The same principle applies here: a free, built‑in method for geocoding distances would transform a tedious lookup into a seamless, repeatable step, freeing students and professionals alike to focus on analysis rather than data entry.

At first glance the problem seems simple—enter “New York, NY” and “Los Angeles, CA,” and receive a mileage figure. Yet the challenge lies in marrying two worlds that have historically spoken different languages: the static, formula‑driven environment of Excel and the dynamic, API‑centric realm of mapping services. Fortunately, Microsoft has already opened a bridge through its Power Query and the free “WEBSERVICE” function, which can call public endpoints such as the OpenStreetMap Nominatim API or the free tier of the Google Distance Matrix (subject to usage limits). By constructing a query that concatenates the two city names into a URL, users can retrieve JSON results, parse the distance field with “FILTERXML,” and drop the value directly into a cell. This approach requires only a handful of lines of formula, no paid add‑on, and it respects the user’s desire for an accessible solution.

Beyond the mechanics, the real value of this capability is strategic. When a carbon‑footprint model can instantly adjust distances based on mode of travel—air, rail, or road—the output becomes a living indicator of sustainability choices. A simple dropdown linked to a lookup table can multiply the base distance by emission factors for each transport mode, turning a static spreadsheet into an exploratory sandbox. Users can instantly see how opting for a train instead of a flight shrinks the carbon tally, encouraging data‑driven decision making. This aligns with the progressive tone we champion: legacy tools that once forced manual lookups are now gateways to insight, not obstacles.

Implementing the free API route does carry responsibilities. Public geocoding services impose rate limits and require attribution, so a well‑designed workbook should include error handling that flags when a request fails and falls back to a cached value. Moreover, privacy‑conscious users must be aware that each request transmits location data to a third party; for educational projects this is typically acceptable, but enterprise deployments may need a self‑hosted solution such as the open‑source “OSRM” engine. By acknowledging these constraints, the editorial stays human‑centered, guiding readers toward responsible experimentation rather than presenting a silver‑bullet.

Looking ahead, the convergence of AI‑native spreadsheet extensions and free geospatial APIs promises a future where any “what‑if” scenario can be quantified on the fly. Imagine a workbook that not only pulls distances but also predicts traffic‑adjusted travel time, converts that into fuel consumption, and then visualizes the carbon impact on a map—all without leaving the sheet. As we continue to explore these integrations, the question worth watching is how quickly spreadsheet platforms will embed native, no‑code connectors for the most common public data services, turning every cell into a portal to up‑to‑date knowledge. The answer will shape whether tools like Excel remain a static repository or evolve into the truly interactive, future‑focused workspaces we envision.