Monday, May 21, 2012

Using Add-ins: MapCite

Out of the box, Excel is awesome all by itself. And yet there are those who endeavour to kick things up a notch by inventing add-ins...a kind of extreme macro. This specialized code enables Excel to do all sorts of new tricks. In previous posts, we've looked at Sparklines and ASAP Utilities.

I was recently pointed toward MapCite and made some time to give it a try. With MapCite, you can visualize your data using a Bing map embedded in Excel. The add-in looks something like this*:

*See the Bonus Round at the bottom of this post for more information.

For my data set, I pulled from one we've seen here before, the 5th grade state science test results from 2011. I added district address information.

Now, we're ready to "Geocode Data." Select the data---not the columns, or MapCite will tell you it can't handle that much work---for the addresses. Then click the "Geocode Data" button.

 In the pop-up window, fill in the information. Then, click "Geocode."

 Holy cow! We've just generated a whole new set of data:

There's more where that came from...
Now, you can add the mapping features. Select the data in the Latitude and Longitude columns, then click "Add Data."

In the pop-up window, indicate the required information and click "Finish."

Let's have a look at what we have wrought. Click on the "Show/Hide Map Pane" button. Here is our first view:

Not too exotic, but that's because MapCite automatically clusters the pins so things don't look messy.  Here's an unclustered look. Note that when you click on one of the pins, the row in the worksheet with the matching data is automatically highlighted. Keep in mind that you can also change the base map that is used.

You can also use the HeatMap feature to take a look:

You may be wondering why I bothered using science data when we just used the addresses of all the districts. It is a bit of a head-scratcher. But, with filtering tools in Excel, you can choose which groups to look at: small schools, those which scored above the state average, etc. You can also add GPX data (data that shows a route that was followed).

What I like about the add-in is that it's easy to use and that I can see the map in my spreadsheet. I don't have to upload my data elsewhere and pull it into another application. However, at this point, MapCite is fairly limited in features. You can make different pins for different pieces of a data set, but you can't show more than one set at a time. It's great to have things on a map, but I need to derive more meaning than just concentration. As such, classroom applications (other than what students might look at it) are limited. I think new features will come in time. I've been promised an upgrade to a Pro account when it's ready for release, and will let you know what else you can do.

Have a look around the MapCite website or YouTube channel for additional information. Better yet, give it a try for yourself.

Bonus Round
I say "like this," because I couldn't get the add-in to install properly. Although MapCite was very responsive to my inquiry for tech support, we couldn't figure out why Excel was making the add-in invisible. I ended up kludging things by creating a new tab for the ribbon called "MapCite," then dragging the groups from the tab-that-refused-to-show into my kludged one. Tech support said that they haven't had any issues similar to this one, so don't let my experience put you off. Also, if you have any clue why Excel shows the MapCite add-in as being active, but won't put it on the ribbon, I'd love to hear how to fix this.

1 comment: