Turn a list of customer locations into a map right in Excel using Microsoft MapPoint. In Episode 563, I show you how to use MapPoint to render a map in Excel, but then also how to make the map more suitable for viewing.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well if you happen to be in England I want to invite you to take part in a discussion.
Go to England.html. There's a link to a message board topic.
We are thinking about coming to England in november for a series of seminars.
Trying to figure out the best cities where people are located. What itinerary makes sense So we'd love to have you join in.
I'd love to meet as many of you as possible And so we're trying to figure out the best places to go as part of that.
My topic today is not necessarily about excel but a product that works excellently with excel called Microsoft Mappoint.
Anytime you have geographic data It helps to be able to visualize it on a map and especially if it's in an area that you're not familiar with.
Mappoint is a cool product from Microsoft and they adds an icon here to excel where we can say we want to take the data in excel and put it on a map.
Microsoft goes through the process of "Geocoding" that's where we take the cities and states or cities and countries and figures out the latitude and longitude for that.
and usually it would ask you for any cities It doesn't know and then puts them on a map.
My big problem with map point in excel is that they're giving us black and white icon so it's a black and white push pin that you can see here and here and here.
Those are really hard to see so what I usually do is go to the view menu.
Notice this menu is actually the Mappoint menu and I'll go to "Legend and overview" then right click the pushpin set to choose "properties" and I can change to a large red circle or something like that, so it becomes much more obvious what's going on in the map.
We can zoom out, zoom in do all kinds of things like that.
If I need to zoom into just one particular country, we can do that and even change the map style, so "view" "Map style" They have five different styles depending on whether you want terrain, the data seems to be a nice map without a lot of roads and things like that.
I'll close the legend and overview.
Now one cool thing about Mappoint is once I click outside of the map, it embeds the map on the worksheet and since I have Mappoint I can edit the map But if I send this Excel file to someone else who doesn't have Mappoint they still see the map.
Basically as a jpeg when they open the file.
So it's great way anytime you have Geographic data.
Here's a list of all my fall seminars.
This gives me a great idea of exactly the range of how far I'll be traveling this fall.
Gives me an idea of where people have heard about me in or where they haven't.
I'm sure that you have a customer data set and it really benefits to throw that data on a map.
Now finally my one last tip about Mappoint.
Microsoft sells his product for around 289 and you can probably find it in Amazon for about 229$ US.
There's two versions. A North American version and a European version and that's important if you're going to be doing routings, so I need to drive from here to here.
They have the appropriate set of streets in either version But if you're looking for the US version I find that Microsoft usually gives us away as a premium.
if an IT manager goes and spends the day learning about some new Microsoft technology, they give them a copy of Mappoint and Inevitably what ends up happening is these end up on the auction sites.
If you're looking for a brand new sealed copy of Mappoint.
Go out to ebay where you can usually find it in the 60 to 75 $ range.
Gives you a great chance to play with the product and then once your manager sees what you can do, and they're impressed.
I'll probably go out buy a copy for everybody.
Hey there you have it using microsoft mappoint in conjunction with your excel data to create beautiful geographic analysis.
Again if you're in England please join our discussion would love to meet up with you in November and want to figure out the right cities to be at.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Well if you happen to be in England I want to invite you to take part in a discussion.
Go to England.html. There's a link to a message board topic.
We are thinking about coming to England in november for a series of seminars.
Trying to figure out the best cities where people are located. What itinerary makes sense So we'd love to have you join in.
I'd love to meet as many of you as possible And so we're trying to figure out the best places to go as part of that.
My topic today is not necessarily about excel but a product that works excellently with excel called Microsoft Mappoint.
Anytime you have geographic data It helps to be able to visualize it on a map and especially if it's in an area that you're not familiar with.
Mappoint is a cool product from Microsoft and they adds an icon here to excel where we can say we want to take the data in excel and put it on a map.
Microsoft goes through the process of "Geocoding" that's where we take the cities and states or cities and countries and figures out the latitude and longitude for that.
and usually it would ask you for any cities It doesn't know and then puts them on a map.
My big problem with map point in excel is that they're giving us black and white icon so it's a black and white push pin that you can see here and here and here.
Those are really hard to see so what I usually do is go to the view menu.
Notice this menu is actually the Mappoint menu and I'll go to "Legend and overview" then right click the pushpin set to choose "properties" and I can change to a large red circle or something like that, so it becomes much more obvious what's going on in the map.
We can zoom out, zoom in do all kinds of things like that.
If I need to zoom into just one particular country, we can do that and even change the map style, so "view" "Map style" They have five different styles depending on whether you want terrain, the data seems to be a nice map without a lot of roads and things like that.
I'll close the legend and overview.
Now one cool thing about Mappoint is once I click outside of the map, it embeds the map on the worksheet and since I have Mappoint I can edit the map But if I send this Excel file to someone else who doesn't have Mappoint they still see the map.
Basically as a jpeg when they open the file.
So it's great way anytime you have Geographic data.
Here's a list of all my fall seminars.
This gives me a great idea of exactly the range of how far I'll be traveling this fall.
Gives me an idea of where people have heard about me in or where they haven't.
I'm sure that you have a customer data set and it really benefits to throw that data on a map.
Now finally my one last tip about Mappoint.
Microsoft sells his product for around 289 and you can probably find it in Amazon for about 229$ US.
There's two versions. A North American version and a European version and that's important if you're going to be doing routings, so I need to drive from here to here.
They have the appropriate set of streets in either version But if you're looking for the US version I find that Microsoft usually gives us away as a premium.
if an IT manager goes and spends the day learning about some new Microsoft technology, they give them a copy of Mappoint and Inevitably what ends up happening is these end up on the auction sites.
If you're looking for a brand new sealed copy of Mappoint.
Go out to ebay where you can usually find it in the 60 to 75 $ range.
Gives you a great chance to play with the product and then once your manager sees what you can do, and they're impressed.
I'll probably go out buy a copy for everybody.
Hey there you have it using microsoft mappoint in conjunction with your excel data to create beautiful geographic analysis.
Again if you're in England please join our discussion would love to meet up with you in November and want to figure out the right cities to be at.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
