+'&title='+encodeURIComponent(document.title)+'&overview='+encodeURIComponent(document.title)+'&urllanguage=en&urlaffiliate=32003&encoding=UTF-8&urlpicture=http://www.viadeo.com/v_img14/shareit/viadeoflower.gif')
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.
If you liked this article, please share it!
Hi,
You can use a shape file and get the x/y out of that them plot the out line on the scatter plot too, like this:
http://www.blog.methodsinexcel.co.uk/2007/11/12/geocoding-in-excel-using-google-maps-api/
thanks
Ross
@Ross -
I love your blog and it's an honor to have you reading mine!
Your technique is interesting and I think I'll give it a try. I'd imagine that complex borders and coastlines would really increase the size of the data required to do a plot, but the trade off would be the ability to zoom in or out by adjusting the axes, which cannot be done in my light-weight approach.
Regards,
Daniel Ferry
excelhero.com/blog
Thanks Daniel,
Yep the is the data issue, it the effect it has on the render time really, it's not an issue at the workbook level. Tushar (and others, clear, simply springs to mind) has some good example where they use shapes, there you can scale with code.
The nice thing about a plot is that it manages it's self in relation to the geo data, so it better if those case, but shape I think would be the way to go for "fills".
Thanks again, and keep up the good work.
Ross
In an attempt to improve the charting speed with the the map, I removed the image from the chart and just placed it in the worksheet. Then I set the chart's plot area and chart area to be transparent and placed it on top. Interestingly, the chart loading seemed to be just as slow (although there could have been a difference I did not detect since the graphs were loading fairly quickly using any approach). And the charting was only slowed when the image was actually behind it. Placing it somewhere else in the same worksheet did not slow it. Is there a way to open up or extract an image being used as a background in a chart? In this case, I changed the extension of the workbook to .zip which converts the workbook into a bunch of xml and image files. However, this does not seem to work with pre-2007 file versions.
I don't think the BatchGeo is working any longer, at least to return the lat/long data from geocoding. This is a drag, as it was a lovely way to geocode a lot of data, but apparently it was violating the terms of the Google API agreement or something, so now you can still map but not get back the geocoded data like you used to be able to.