I've got a list of geographical locations in an Excel spreadsheet. I want users to be able to search for a user-defined location, and then have the X number of closest locations from the list pulled up and displayed, sorted by nearest to farthest by travel time via car. I know Excel alone obviously can't do this.

I have minimal/no programming knowledge. Just an Excel tinkerer. I figure that, at a minimum, this would require utilization of a mapping service like Google Maps or Mapquest.

4 Answers 4


A possible solution would be to use postgresql, postgis, pgrouting and osm2pgrouting.

  • Insert your fixed locations in a postgis database.
  • Insert the real road network in your database for the area that you need with an import of OSM data using osm2pgrouting.
  • Optional: find the closest point on your road network from the user defined location.
  • Use pgrouting to calculate the distance by car from user defined location to all your predefined locations ordered on distance limited by X.

My assumptions:

  • You need the real road network to be able to calculate the driving distance from a user defined location to your fixed locations.
  • Distance from A to B is a good substitute for driving time. This is obviously not always the case but i do not know your specific use case.

Needed skills:

  • What you mainly need with this solution is SQL knowledge.

You say you are a "Excel tinkerer". "Mapping" in Excel is possible to some extent. I once saw a very involved Excel dashboard application that used a line chart to plot out boundaries of a study area and then VBA to determine what portion of the study area a given point was located within - all based on X\Y locations of both the study are line vertices and point locations. It was pretty impressive. Also, these articles might be of interest:

I don't necessarily condone or encourage these as solutions (as there are much better true GIS ones such as mrg's answer to this question), but like I said earlier, you said you were a Excel tinkerer with no-programming knowledge - which begs the question: What's your GIS knowledge level?


It looks like the Google Maps API has a service for this: http://code.google.com/apis/maps/documentation/javascript/distancematrix.html

Limit of 25 destinations, though.

You might also be able to leverage the api form http://www.mapnificent.net/, though it's based on public transportation.


I found an article called Calculate distance in Google spreadsheet that may be of use.

It would take some tweaking to do exactly what you want, I think -- and Google's daily API caps could hinder you, but it's an interesting approach.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.