I often need to do geospatial location mapping in my Excel projects. If you can acquire the latitude and longitude coordinates, it’s pretty easy.
You’ll need an equidistant map like this one, in order to plot the coordinates easily. If anyone knows the mathematics needed to transpose coordinates so they can be plotted on the more common map projections where the border between the USA and Canada is curved, please let me know! I have not figured that out yet.
When using an equidistant map, all you need to do is plot the coordinates on an XY (Scatter) chart. Of course you will need to set the horizontal and vertical axes min and max so that the points are drawn close to accurate. This is easy when your data includes a lot of coastal points (just adjust the axes until those points are barely on land). The included map is calibrated quite well.
For this post I chose to imitate Nathan’s outstanding Walmart growth movie. While Excel is not up to the task of the Flash animation in his movie, the results are not half bad. In my version you can step through year by year manually with a scroll control, or you can click the Animate button and watch the movie.
I got the data from here. They did not include the lattitude and longitude coordinates so I ran the CSV through batchgeo.com and in a few minutes I had coordinates for 99% of the locations. The rest I acquired manually from Yahoo Maps. Unfortunately, the data source only lists openings through 2006; it would be nice to have the complete and up to date dataset…
The map image is set as the background to the chart’s Plot Area. From this project I learned that background images on charts while useful really slow down the charting engine. To demonstrate I’ve included a check box to toggle the chart background. The charting is significantly faster with it off.
Here’s the chart:
Want to Learn How I Do It?
If you are interested in learning the incredible Excel techniques that I showcase on this blog, you should enroll in the Excel Hero Academy. There is no other course available anywhere that will teach you how to leverage Excel’s power like my course does. As an alumni of the Excel Hero Academy, you will be massively more productive when working with data.