How to Use the 3D Map Feature in Excel 2019 - dummies

How to Use the 3D Map Feature in Excel 2019

By Greg Harvey

3D Map is the new name of an exciting visual analysis feature that’s available in Excel 2019, formerly known as Power Map in Excel 2016. 3D Map enables you to use geographical, financial, and other types of data along with date and time fields in your Excel data model to create animated 3-D map tours.

To create a new animation for the first tour in 3D Map, you follow these general steps in Excel 2019:

  1. Open the worksheet that contains the data for which you want to create the new Power Map animation.
  2. Position the cell cursor in one of the cells in the data list and then click Insert→  3D Map→  Open Power Map (Alt+NSMO) on the Excel Ribbon.

    Excel opens a 3D Map window with a new Tour (named Tour 1) with its own Ribbon with a single Home tab. This window is divided into three panes. The Layer pane on the right contains an outline of the default Layer 1 with three areas: Data, Filters, and Layer Options. The Data area in the Layer Pane is automatically expanded to display a Location, Height, Category, and Time list box. The central pane contains a 3-D globe on which your data will be mapped. A floating Field List containing fields in the selected Excel data model initially appears over this 3-D globe. The left Tour Editor pane contains thumbnails of all the tours and their scenes animated for your data model in 3D Map (by default, there is just one scene marked Scene 1 when you create your first tour).

  3. Drag fields from the floating Field List to the Location, Height, Category, and Time list boxes in the Layer Pane to build your map.

    Drag the geographical fields whose location data are to be represented visually on the globe map and drop them into the Location list box in the Layer Pane. 3D Map displays data points for each location field for your animation on the 3-D globe as you drop it into the Location list box. The program associates the selected location field with a geographical type in the drop-down list box to the right of the field name in the Location list box in the Layer pane. You can modify the type by selecting its drop-down button, if necessary. Just keep in mind that each location field needs to have a unique geographical type.

    You also add fields from the floating Field List that you want depicted in the animation to the Height, Size, or Value list boxes (depending upon the type of visualization selected) as follows:

    • Add values you want represented in the type of chart displayed on the 3-D map to the Height list box. By default, Excel uses the Sum function for value fields, but you can change this function to Average, Count, Maximum, Minimum, or No Aggregation by clicking its drop-down button in the Location list box.
    • Add data fields that you want to appear as categories in the legend for the 3D Map animation to Category list box. The items in this field are automatically added to a floating legend in the 3-D map if your chart type has a legend.
    • Add date and time fields to the Time list box to set the time element for your 3D Map animation. By default, 3D Map does not associate the fields you add here to any time unit. You can specify the time unit by selecting Second, Minute, Hour, Day, Month, Quarter, or Year from the field’s drop-down list button in the Time list box.
  4. Select the type of visualization by clicking its icon under the Data heading in the Layer Pane: Stacked Column (default), Clustered Column, Bubble, Heat Map, or Region.

    3D Map now displays data points for your Height, Size, or Value data on the 3-D globe appropriate to the type of visualization selected along with a floating legend for the data values (organized by any fields used as categories) in the center pane of the 3D Map window. At the bottom of the map, you see a Time Line control with a play button that enables you to play and control the animation.

  5. (Optional) Click the Map Labels button on the Ribbon to add country and city names to the maps on your 3-D globe.
  6. (Optional) Click the Close the Layer Pane button and Close the Tour Editor button to hide the display of Layer and Tour Editor panes, respectively.

    Now, your 3-D globe with the Layer 1 legend on the right side and animation timeline below fill the entire window below the 3D Map Ribbon. Note that you can redisplay the Layer pane and the Tour Editor pane in the 3D Map window at any time by clicking the Layer Pane or Tour Editor Ribbon buttons, respectively.

  7. (Optional) Drag the Layer 1 legend so that it’s not obstructing your 3-D globe. You can also resize the legend by selecting it and then dragging its sizing handles. If the Time Line animation control is obstructing key areas of the globe, you can hide it by clicking its Close button.

    You can redisplay the Time Line control at any time by clicking the Time Line button in the Time group on the 3D Map Ribbon. Note that you can’t reposition or resize the Time Line control when it is displayed and that you can play your animation by clicking the Play Tour button on the Ribbon when the Time Line control is hidden.

  8. (Optional) Drag the globe to display the area of the world with the locations you want to watch when you play your animation or use the Rotate Left (Shift+←), Rotate Right (Shift+→), Tilt Up (Shift+↑), or Tilt Down (Shift+↓) buttons to bring this area into view. Then, click the Zoom In (Shift+ +) or Zoom Out (Shift + -) to bring the area closer into view or further away. Once you have the viewing window beneath the 3D Map Ribbon positioned the way you want it when viewing your animation, you are ready to play the 3-D map tour you’ve created.
  9. Click the Play Tour button on the Ribbon or the Play button on the Time Line control (if it’s still visible).

    When you click the Play Tour button on the Ribbon, 3D Map automatically hides the Tour Editor and Layer pane along with the Time Line control if they are still visible at that time. You can pause the animation by clicking the Pause button that appears in a cluster of controllers in a bar at the bottom of the screen or by pressing the spacebar on your keyboard. When you’re finished watching the animation, return to the regular Editing view of 3D Map by clicking the Go Back to Edit View button (the one with the arrow pointing left at the very beginning of the bar at the bottom of the screen) or press the Esc key on your keyboard.

  10. Click the Close button in the far right corner of the 3D Map title bar to close 3D Map and return to your Excel worksheet and then save the workbook (Ctrl+S) to save your 3D Map tour as part of the workbook file.
3D Map feature Excel 2019
3D Map window with new Clustered Column 3-D animation for visualizing the quantities ordered of various Northwind items between July1996 and May 1998 at various locations in western Canada and United States.

After you create your initial animation tour for the data model in your Excel workbook, you can always replay it simply by reopening it in 3D Map and clicking Play Tour on its Ribbon. To reopen the tour when the workbook with the data model is open in Excel, select Insert→  3D Map→  Open Power Map on the Excel Ribbon (Alt+NSMO) and then click the Tour 1 button at the top of the Launch Power Map dialog box.

Once the tour is open in 3D Map, you can also edit it. You can change the appearance of the 3-D globe in your animation by selecting a new theme by clicking the Themes button in Scene group of the 3D Map Ribbon and then selecting a thumbnail on this button’s drop-down palette. You can flatten 3-D globe to make it two-dimensional by clicking the Flat Map button in the Map group of the Ribbon.

You can also add new layers to your original scene that animate a different data set over the same or a different set of date and time values. To add a new layer, click the Add Layer button in the Layer group of the 3D Map Ribbon and then define the type of visualization and the fields to use in your data model for the location, analysis, and time (following the same steps as outlined earlier for creating the initial layer of the first scene of a new tour). After you add a new layer (automatically named Layer 1) to your original scene, 3D Map shows the legends for both Layer 1 and 2 (which often overlap and need to be separated manually). 3D Map also shows the data points for each layer’s location, analysis, category, and time fields when you play the animation.

If you want to see the animation for just one of the layers in your scene, you hide all the other layers before you play the animation. To do this, click the Layer Manager button at far left on the top of the Layer Pane and then click the Show or Hide Layer button (the one with the CBS eyeball icon) in front of the name of all layers you don’t want to view in the animation.

In addition to adding new layers to a scene, you can also add entirely new scenes to your tour that use a copy of the active scene, a new 3-D globe world, or a custom map with an entirely new background. To create a new scene, click the New Scene button in the Scene group of the 3D Map Ribbon and then click Copy Scene, World Map, or New Custom Map options on its drop-down menu.

After you select the type of scene, you then define the animation for the new scene using the steps you followed earlier in creating the opening animation for the first layer in the first scene of the tour. To play the animations you add to the layers of any new scene you add to your 3D Map tour, you simply click the Scene thumbnail in the Tour Editor pane before you click the Play Tour button on the Ribbon or the Play button on its Time Line controller.

Use the Create Video and Capture Screen buttons in the Tour group of the 3D Map Ribbon to share completed animations with your colleagues and clients. Click Capture Screen to take a static picture of the visualization displayed on your 3-D globe that Excel saves in the Office Clipboard. Once in the Clipboard, you can use the Paste feature (Ctrl+V) to paste the graphic into a worksheet after you close 3D Map and return to Excel.

Click the Create Video button to create a video of the animation for the currently selected scene and layer(s) in your tour. When you click this button, 3D Map opens a Create Video dialog box where you choose the quality of the video selecting one of the following option buttons: Presentation & HD Displays (largest size and best quality at 1080 pixels), Computers and Tablets (medium size and quality at 720 pixels), or Quick Export & Mobile (lowest quality and smallest size at 320 pixels).

If you want an existing music or narration file to accompany the animation, click the Soundtrack Options button and then select the sound file and the playback options followed by Apply before you click the Create button to make the video. 3D Map then generates a video of the currently selected animation saved as .mp4 video file in your device’s default Video folder. You can then save this video file to a folder on your OneDrive or Dropbox to share with colleagues or clients. You can also play the video on your device using its default video player.