Hi. In this Excel tutorial we’re going to go over how to make a 3D Map in Excel and then create a short video of the map that we can share and use offline. So, on my screen I have some data on annual foot traffic for store locations around the world. I’ve already formatted them in a Table format, but know that you can also create maps using data in Pivot Tables or Imported Data. Also, a little about the data. First, if you are using a date field, make sure that it’s formatted as a date. For example, notice how my Years are all entered as January 1st of that year, but then formatted to show only the year. Otherwise, if I only inserted a 4-number year, it won’t be recognized as a Date field later when we go to use it for the map. And notice how I have a Store City and a Store Country – and, by the way, you can get as granular as a specific address. Regardless, you want to provide Excel as much information as you can about the location – by adding columns for city, state, province, postal code, etc. Doing this can help cut down on mapping errors. For example, cities and towns with duplicate name locations in other geographies or those with alternate spellings, may not be mapped properly. And, FYI, Excel uses Bing Maps data for this. So, if you’re not sure how to label the location, you could always do a quick search for it on Bing Maps to see how it’s written there. This also means that you want to make sure to be connected to the internet when you create your 3D Map. And, by the way, if you find this tutorial helpful, don’t forget to Like the video and subscribe to the channel for even more tips. So, once all of your data is ready, we can insert the map. You should find this option in the Insert tab, under 3D Maps. So, with your cursor somewhere on the Data Table, click to open 3D Maps. So, the first thing we want to focus on is the Layer options. ...let's close this for a minute... This is where we’ll set up the key properties for our 3D map. Now, we're only going to be doing 1 Layer for this example. So, here, in Location, Excel picked out the 2 columns in our data that refer to location - Store City and Store Country. Now, I plan to use Store City, but take a look here. This percent lets me know what percent of the locations it was able to find a match for. Now, this looks perfect for Store City, but if I select Store Country I only get an 85% match. So, clicking on this percent shows me the problem. In this case, it relates to England. Now, remember that this pulls the data from Bing Maps. So, typing England in Bing Maps, I see that the trouble is that I should have used United Kingdom instead. So, let's fix this. ...let me get out of this for a minute... I'm going to close out of the 3D Map to do this, but know that you don't have to. There's just something I want to show you. So, after you create a 3D Map, this message comes up letting us know that a 3D Map exists. Now, you can delete it or move it elsewhere - which is what I'm going to do. ...and, let me update this to United Kingdom... So, what I wanted to show you is the importance of this message. So, like I said, you can delete this is if you really want to. However, with 3D Maps, unlike Charts or Tables, where you can see them here, you don't really get an alert that a 3D Map exists. So, to go back into it you'll have to be back in the Insert tab and then click 3D Map again. And, when you do that, you can create a new Tour or more, in our case, we're going to click to open the one we had started working on. So, my Store Country data still shows 85%. But if I come up here and click on Refresh Data, we'll see this update to a 100%. So, let's go back and select Store City, which is what I wanted to use. So, next, let's select what to base the Height of the markers on. Now, for this I want to use Visitor Data, so we'll select that. And we get this Legend which, again, I want to delete just because I want to keep this area clear. Now, for the Category I was torn between selecting Store City or the Year. And, ultimately, I decided on the Year because since there are multiple years for each Store City – I liked the visual effect of the different colors for each year. And, for Time, we're going to select Year. Now, this Time option is pretty cool. It will help to add to the animation once we create our video. And notice, once I selected a time period, this timeline and a date stamp showed up. Now, if I click on Play here... it's moving kind of slow at the moment, but look how the time shows the gradual increase in visitors for each store. So, in Filters you can add one of your data columns to filter by, but I'm not going to be selecting a Filter. And, on the Layer options, you can change the height of your markers, the thickness, the opacity, the color, and how you want to show the value, but, again, I'm not going be changing any of those. Now, the Data Card refers to a small card with information that displays when you click on a marker. So, just decide the information that you want and then you can scroll through the various templates to find a layout that you'd like. ...close out of this... So, up here, we can select a type of visualization. And this refers to how you'd like to display the data in each location. So, it defaults to a stacked column style, but I'm partial to clustered column for this example. Because we're using Year for the category, I like how it displays each year next to each other. But you also have the option to use bubbles. So, you can also use heat maps. And let's zoom in a little see you can see better what that looks like. You can also shade the entire region. ...let's zoom out so you can see that. But let's go back to cluster charts since that's what we'll be using. And, in Category, we'll reselect Year as the category gets lost when you select the heat map. Now, let's zoom in on these for a minute again so you can see the markers. So, if you want to change the markers, you can come up here to Shape and you have 5 to select from. So, let's say I switch mine to Triangle, this switches all of the markers to a triangle shape. And, to zoom in and out, I'm using the wheel on my mouse, but you can also use these minus and plus signs here and use these arrows to move the globe around. Now, we can also decide if to use Map Labels, which I'm going to be doing for this. Oops! And this got switched to Store Country, so let's go back to City. And just turn our globe around. You can also flatten your map. ...deselect that so we can go back to the globe... You can also add Custom Regions if you happen to have a need for that and you have the information. You'll need to import that into Excel. You can add a 2D chart of the data - and you can switch that around. But I'll be honest, I don't much care for this because I can't get rid of the Top and Bottom 100 locations. And since I don't have 100 locations, it just seems weird, but you do have the option to add that visualization. So let's delete that. And you can also toggle on and off some of these other items. If you want to get rid of your Timeline, at the bottom, you can do that. This Date and Time gets rid of the time stamp. This gets rid of the Tour Editor, but let's bring that back. And this one gets rid of the Layer Pane. If you want to show or not show your Field List you can click on this button. Oh, and you can come up here to Themes and this gives you options to change what your map looks like, but I'm going to stick with the default appearance. And, then, once your map is ready, you can now move on to creating your Tour. And all a Tour is is an animated representation of your map. So, some of the options are up here, but we can also click on the settings in the Tour Editor. So, we can change the Scene Duration - that's how long the entire scene will play for. We can give the map a name, so we'll say Map of Stores. This will be the transition time between some of the smaller changes. And the Effect that you want. And because we do have international locations, I'm going to select to Rotate the Globe. And let's click Play so we can kind of see what that looks like. And right now it's moving a little bit slow. We can see the Year categories populate, but the entire Tour stopped at the 10 seconds and I kind of want my map to start and end in about the same geography. So, let's...we can edit some of the Scene Options to help with that. So, one of the things I want to do is the Effects speed is fairly quick, but we'll move the duration to, say, 1 1/2 seconds. And let's do the Scene Duration for 30 seconds. So, now when I hit Play, we can still see everything populate as the time lapse moves on while the globe rotates and, what you'll see, is that it should start..or it should end...in about the same location where it started. For you, depending on what your settings are, you may just have to play with it a little bit. But I'm happy with this. And then, on this screen, one thing you can do is click this button, here, to keep it in a continuous loop if you don't want for your map to stop rotating. So let's get out of this. So, once you're happy with your settings you have a couple of ways to share this. You can do a Screen Capture - which actually only captures this active area of the globe...of the map. So, if we were to paste this in a new sheet, notice we now have a picture of the map we can share. Or we can create an mp4 of the Tour by selecting Create Video. And, here, you have options for the quality you want to use to output your mp4 file. Or if you want to add a soundtrack - which, I do not have a soundtrack for this. And simply click Create and this will automatically help you save an mp4 of your Tour. I hope you enjoyed this tutorial on how to make the 3D Map in Microsoft Excel. If you did, please make sure to Like the video and don’t forget to subscribe to this channel. Thanks!
