3D Maps
Excel Video 504 Power View Maps Part 2
Now that we have covered the basics of creating Power View Maps, Excel Video 504 adds several more features you might find helpful. Watch how adding a field to the Colors area turns the bubble points on the map into pie charts. Now you can see the number of patients based on the size of the bubble and the clinics those patients visit based on the pie chart inside the bubble. As you would expect, vertical multiples, horizontal multiples, and tiles work as they do in other visualizations. Adding geographic fields to those areas can give you even more insight as you analyze the Power View Maps.
We will also add another geographic field to the Locations area. Now I can drill up and down inside the map to larger and smaller geographic areas. You could start at a state level and drill down to county, city, and zip code if you have the geographic data. Drilling back up through those same levels is just a click away.
The next topic for Excel Videos is 3D Maps, a new feature in Excel 2016. Those maps have different features and flexibility than Power View, but if you want to combine maps with other Power View visualizations, here are several tips to get you started.
Excel Video 505 Introducing 3D Maps
Excel Video 505 is devoted to introducing 3D Maps, the new mapping tools in Excel. If you have the professional version of Excel 2013 or the standard version of Excel 2016, you now have access to a mapping tool in Excel. We’ll work through the basics of getting started in this Excel Video. The first key is that you need the internet for 3D Maps to work, since Microsoft uses Bing (surprise!) to map the data.
Watch how my first attempt to map data doesn’t work. Excel has to have data in a Table or in the Excel Data Model for 3D Maps to work. Once we use the raw data instead of the Pivot Table, Excel quickly builds a map for us and gives us plenty of options to review in upcoming Excel Videos.
Note that when I use 3D Maps Excel has two separate Excel windows open, the traditional window and the mapping window. You’ll see Excel remind me that my spreadsheet has maps attached (maps are called Tours in 3D Maps) so I know when I’m in traditional Excel that there are Tours available. You’ve never seen maps like this in Excel before, and we’re just getting started. Stay tuned as we explore all of the things 3D Maps can do.
Excel Video 506 Filtering in 3D Maps
Excel Video 506 introduces filtering in 3D Maps. I hope you’ll be pleasantly surprised at the variety and flexibility of the filtering options available. You can manually filter items as you’d expect, but filtering in 3D Maps also has advanced and/or functionality as well as the ability to aggregate filtered items. Watch how I use the aggregation feature to filter by the distinct count of items in my dataset. To get distinct counts in a Pivot Table you have to use PowerPivot. (Stay tuned. PowerPivot will be a topic of many future Excel Videos.) Distinct count is available now in 3D Maps, making your filters even more powerful.
Note how I can filter by items in the map as well as items that are not displayed on the map. Also watch how I can click to add fields to filter by. I can also drag items from the field list to the filter area to create new filters.
We’re just scratching the surface of what 3D Maps can do. Stay tuned and we’ll continue exploring 3D Maps. I look forward to seeing you then.
Excel Video 507 3D Maps Categories and Colors
We’ll add 3D Maps Categories and Colors in Excel Video 507. A category simply splits the data into columns in our map. Instead of looking at a summary of patients referred to other providers, moving the Hospital or Provider field into the Category area allows us to quickly see whether those referrals not coming to our practice come from hospital based providers or clinic based providers. We’ll also review how to customize the colors of the columns so that you can match the color scheme you need and make your map easy to understand. Also notice the reset button that appears when you change a color. If you want to get back to the original, default colors, that button is the way to go.
The next thing to customize in 3D Maps is the Data Card. A Data Card is like a legend for the map, but with much more flexibility that in a traditional Excel Chart. Data Cards are next. I look forward to seeing you then.
Excel Video 508 3D Maps Legends and Data Cards
Excel Video 508 introduces 3D Maps Legends and Data Cards, two ways to give you users more information about your map. It’s easy to tweak settings on the Legend to change the font, font size, bold, italic, font color, and background color. There’s plenty of flexibility to format the Legend just the way you need to. Note that you can’t change the name of the layer from the Legend window. I’ll briefly discuss the layer name in today’s Excel Video and we’ll talk more about layers in future Excel Videos.
Data Cards are the pop up cards that appear when you hover over areas on the map. There are multiple templates that you can use to display the data on the card in a different order or format. Note that you can also edit the names of the fields that will display on the Data Card, change the way the data is aggregated, delete a field from the Data Card, or you can even add a field to the Data Card. The combination of 3D Maps Legends and Data Cards and the related formatting options give you a variety of ways to better communicate critical data to your end user.
There’s more to be said about the Data area of the layer. We’ll do that in the next Excel Video. Thanks for watching.
Excel Video 509 3D Maps Bubble Chart
We’ll create a 3D Map Bubble Chart in Excel Video 509. Before we get started on new chart types, watch for a brief explanation of Excel and Bing’s attempt to quantify the mapping accuracy of your geographic data. If you have common names like Washington for a city or county, Bing may struggle to accurately place your data. I’ve found the most success using zip codes. The Mapping Confidence Report will give you details on any geographic data Bing is not confident in placing.
It’s easy to create a 3D Map Bubble Chart with one category of data. While that may meet your needs, you can add more power to the Bubble Chart by adding a field in the Categories area. Watch how adding a category turns my single-color bubbles into multi-color pie charts. I’ll also show you several ways to customize the bubbles. Watch for a way to keep the scale of the bubbles constant, even when the scale of the map changes.
There are more chart types to play with in 3D Maps. Heat Maps are next. I look forward to seeing you then.
Excel Video 510 3D Maps Heat Map
Excel Video 510 uses 3D Maps to build a Heat Map in Excel. Instead of column height or bubble sizes, a heat map changes colors based on the value of the data. By default, lower values have cool, blue colors. As the values increase, the color of the heat map transitions through warmer colors like green, yellow, orange and then red. Notice that while you can have categories in column and bubble maps, a heat map won’t allow categories for the colors.
Watch for ways to customize your heat map by changing the scale or intensity of the colors. You can also change the radius size to have the points on your map cover a wider or narrower area on your map. There are also ways to change the colors in the map if you would prefer a transition besides blue to red.
We still have one more map type to discuss. Region maps are next. I look forward to seeing you then.
Excel Video 511 3D Maps Region Map
A region map is a great way to see your data summarized by geographic region, like a zip code. Watch Excel Video 511 to see how to make a region map work for your data. The first thing to note is that unlike heat maps, a region map will let you use categories with your data. I’ll walk through those category options, but my data doesn’t work very well with categories in a region map. Try your data to see if categories work for you. If not, column or bubble maps might be a better way to display categories. There are ways to combine multiple maps in what Excel calls a Tour. We’ll talk more about tours in future Excel Videos.
The great thing about a region map is that the shading corresponds to a geographic region. It’s easy to see your data organized by zip code, for example. It’s also easy to change the color of the shading and the intensity of the shading in the layer options.
Finally, if you need a region for your map that isn’t defined geographically, you can import a custom region file to create the geography you need. A custom region file is a .KML or .SHP file that are essentially custom polygons. Once you have the custom file imported, you can use the custom regions like you would other standard regions. You might use this if you had sales representatives assigned to specific areas and wanted the region map to compare sales representatives.
We have now covered the basic types of 3D Maps available in Excel. Stay tuned. We’ll walk through the 3D Maps ribbon and highlight major options and features in the next Excel Videos. Thanks for watching.
Excel Video 512 Multiple Datasets in 3D Maps
To add more features to our discussion of mapping in Excel, we need to add Multiple Datasets in 3D Maps. Excel Video 512 will do just that. Our objective to is plot different sources of data in one tour, or group of maps. For example, we won’t just track referral data, but we will also track the population in each of the zip codes we analyze to give us some perspective on which zip codes should have the most referrals.
Watch how I can create more than one tour in 3D Maps. Also note how when I add multiple datasets in 3D Maps that Excel builds a new layer for each new set of data. Now that I have data, I can manage those layers and the new tour.
Also notice that even though I have multiple datasets in 3D Maps, I can’t mix and match data from the different datasets in one map. If I want to use fields from multiple datasets in 3D Maps, I need to use the Excel Data Model. For more information about the Excel Data Model, watch Excel Video 474 and Excel Video 475. For more information about relationships in the Excel Data Model, watch Excel Video 477. I will much more to say about the Excel Data Model, especially when we get to PowerPivot.
In the meantime, we have multiple datasets in 3D Maps and can start to build layers and customize tours. We’ll start on that in the next Excel Video. Thanks for watching.
Excel Video 513 Layers in 3D Maps
Now that Excel Video 512 has given us multiple datasets to play with, we will use layers in 3D Maps to show multiple sets of data on one map. To keep things simple to start with, watch how I use the eye icon to hide the CMS Referrals Data layer. We can then work with each of the remaining layers in 3D Maps to customize the data shown. In our example, we will compare referred patients by zip code to the overall population by zip code to visualize the ratio of referrals from larger and smaller zip codes.
Watch how I use different map styles to highlight the different types of data. The referrals are in columns, while the overall zip code population is shown as a region map. The different styles make it easier to see trends in the comparative data.
It’s easy to add another layer to our tour. Watch how the Add Layer button creates a new layer, and then when we select data Excel shows us all of the available data from the dataset we imported in Excel Video 512. Choosing an element from one of the datasets gets our next layer going. In this example, watch how to change the color of the column instead of changing the map type. Now we can see the patients referred and the referring physicians in columns. The population is still shown as a region map. We have plenty of options to view the data. If you like several options instead of just one way to see the data in a map, scenes may be just the thing you need next. Scenes are also the topic of the next Excel Video. I look forward to seeing you then.
Excel Video 514 Scenes in 3D Maps
Excel Video 514 demonstrates scenes in 3D Maps. A scene is a view of your data on a map. Layers allowed us to put multiple sets of data on the same map. Today we can use scenes to put multiple sets of data on different maps. Watch how we can create a new scene based on an existing scene and then make changes to the scene to show the data we need. Also watch for the way to rename scenes. Note how a new scene is placed immediately below the copied scene instead of at the end of the list. Naming the scenes helps me keep track of which scene has the data I need and makes it easy to reorder the scenes to fit my presentation.
Now that we have created several scenes, the next step is to use scene options to customize how the scene is presented in tour. By the time we are finished, the tour will end up like a PowerPoint presentation. There will be a series of scenes in 3D Maps that resemble a PowerPoint slide deck to showcase our data. I look forward to seeing you then.
Excel Video 515 Scene Options in 3D Maps
Happy 4th of July, everyone! I hope you enjoyed the long weekend. In Excel Video 515 we will play a Tour and change some Scene Options relating to how the Tour is presented. A Tour is simply a collection of scenes played in the order we set in Excel Video 514. The Scene Options menu focuses on two choices. How long is each scene and how do we transition from one scene to the next scene? I have intentionally made the scenes short to make the Excel Video shorter. You will probably go longer than 5 seconds per scene. The default scene length is 10 seconds.
Watch for the different transitions from scene to scene. There are several different transition effects. You can control the length of the transition and the magnitude of the effect, or how dramatic the effect appears. The default transition is 3 seconds. There is no transition to the first scene that starts the Tour, but you can control transitions to remaining scenes. The transition from scene 2 to scene 3, for example, is controlled by the transition set up on scene 2.
We will save our work and look at ways to share the Tour in the next Excel Video. I look forward to seeing you then.
Excel Video 516 Videos and Themes in 3D Maps
We have spent a lot of time getting just the right data on our map, so today Excel Video 516 takes a few minutes to think about using videos and themes in 3D maps. Remember that this is Excel, not a movie-producing software package. There are, however, some things we can do to save our Tour as a video. Watch how to choose a resolution for the video and you can even add a soundtrack. Excel will tell you how long your video will be and give you options to fade or loop the soundtrack. I have saved a couple of scenes as a brief video and it has worked for me. I have not tried to build an hour long video presentation. Brief videos seem to work best in 3D maps.
I will spend a brief minute on the Capture Screen button. I used 3D maps with a client just the other day to analyze a potential new clinic location and saved several visualizations for them to use in a PowerPoint presentation. I used the Windows Snipping Tool to create the screen captures. For me, the Snipping Tool offers more flexibility and features, but the Capture Screen button is there if you need it.
Finally, Excel Video 516 will show you how to use Themes in your maps. Your CPA friend uses the default Power Map theme almost every time. If you are more into graphic design, or you have a need for satellite imagery or more or less color in your map, Themes can help you.
Stay tuned. We can customize your map a little more with options from the Map section of the ribbon. I look forward to seeing you then.
Excel Video 517 Map Options in 3D Maps
Excel Video 517 has three ways to add map options in 3D Maps. First, there is an easy way to add map labels like names of cities to your map. The Map Labels button toggles the names on and off. Map labels can be especially helpful if you are trying to understand geographic regions you are not familiar with, like where a zip code begins and ends.
The Flat Map button is also a toggle on or off button and flattens the map. The flattening effect is most easily seen over a broad geographical area. Excel flattens the globe to make the map easier to read and to give you more surface area to plot information.
The Find Location button allows you to enter an address that Bing can recognize. Bing will zoom your map to that location. It would be very helpful if you could drop a pin at those locations so that when you zoom out you can still find the location you searched for, such as a hospital or practice location. Hopefully that feature will be added to the map options in 3D Maps soon.
Stay tuned. In the next Excel Video we will learn about three tools to insert data onto your map. I look forward to seeing you then.
Excel Video 518 Insert Options in 3D Maps
There are three Insert Options in 3D Maps to discuss in Excel Video 518. You can insert a two-dimensional chart that displays the same data shown on your map. You can choose either a column chart or a bar chart and can choose whether or not the chart is stacked, but those are your choices. You can change the sort order of the chart and adding more fields to the map adds more fields to the chart, but that is about it. The key is that you cannot chart a series that is not displayed on the map. If you want to display the same data both geographically and in a chart, the 2D Chart button will get you on your way.
You can insert a text box in your chart. The text box makes it easy to control font, colors, and sizes. You can also move and size the text box to meet your needs. Note that unlike the map options we discussed in Excel Video 517, these button are not toggle buttons. Clicking 2D Chart inserts another chart. Clicking Text Box inserts another text box.
Clicking Legend inserts a legend in your chart. Note that this button is not a toggle, either. If you already have a legend in your chart, the Legend button will be grayed out. To delete a legend, a text box, or a 2D chart, highlight the item and press delete. Once you have deleted the legend, the Legend button will light up and become available again. Note the you can double-click the legend for a few options relating to font. If you add fields in the map Field List, the fields will automatically be added to the legend.
There is one more set of map options to discuss on the ribbon. We will review those options in the next Excel Video. Thanks for watching.
Excel Video 519 Time and View Options in 3D Maps
We will cover the ribbon sections for time and view options in 3D Maps in Excel Video 519. Once you add a time element to your map, Excel Video 519 will show you how to tweak the timeline and the way the time is displayed. Showing seconds in the time display when your data is grouped in months confuses the reader. Watch how to control the display and make your presentation even more powerful.
We will also go through some easy toggle options to turn features on and off. The tips make it easy to maximize the screen real estate devoted to your map. I will also clarify that you do not save a 3D Map independently. You save the spreadsheet containing the 3D Map.
Remember that you need either Excel 2016 or the Pro version of Excel 2013 to get maps in Excel. I hope this series on mapping in Excel has been very helpful for you.
