If you have Excel 2016 or newer, you have access to Filled and Power maps. In this article, you will learn how to create beautiful map charts based on data tied to geographic locations. 2D Filled maps show data points tied to locations as areas rather than a point on a map. 3D Power Maps allow you to create column and bubble charts based on data that is tied to geographic locations. Our work is located in this Excel workbook.

2D Filled Maps

The worksheet “Filled Map Start” (see Figure 1) contains sales of products in five countries.

Figure 1: Sales data for a Filled Map

Before using Excel’s mapping capabilities, you should make the data into an Excel table by selecting the cell range A1:B6 and then using the keystroke combination Control+T  (or choose Table from the Insert tab.) Next, select Filled Maps from the Map icon on the Charts section of the Insert tab. You will now see the map (without labels) shown in Figure 2 and the worksheet “Filled Map.”

Figure 2: Filled Map of sales by country

Larger sales are selected with darker blue coloring. Right clicking on any country allows you to select Format Data Series options, which brings up the menu shown in Figure 3.

Figure 3: Formatting options for a Filled Map

• Four projection options are available. Automatic allows Excel to choose the method used to display the map.  Mercator is the standard projection developed in 1569 by Gerardus Mercato. The Miller projection is a modification of the Mercator projection. Finally, the Robinson projection is a projection that shows the entire world.
• From Map Area option, you may select Automatic Only Regions With Data (this shows only the locations with data) or World which ensures the entire world is shown.
• Map Labels allows you to control the labels shown on the map by choosing None, Best Fit or Show All.
• Series Color dropdown options allow you to choose a two or three-color map and you can select the desired color scheme.
• We added a chart title. Then, after selecting Map Projection Automatic, Map Area Automatic, Show All and the Three-Color option, we obtain the map shown in Figure 2.

3D Power Maps

Excel’s 3D Power Maps feature allows you to create amazing maps based on the following geographic formats:

• Street Address
• City
• State/Province
• Zip code/Postal Code
• Country/Region
• Latitude and Longitude

To utilize 3D Maps, open 3D maps from the middle of the Insert tab in the Tours group. The worksheet “3D Map Start” contains information on U.S. power generation capacity. The state, county, plant capacity and energy description columns will be used to create a 3D Map. Suppose you want to use a stacked column chart to summarize the total energy capacity  for each state by energy type.

To start, make the data into an Excel table. After selecting the data in the table, click 3D Map and choose Open 3D Maps. Then, fill in the Layer pane as shown in Figure 4.

Figure 4: Layer Pane settings to create Stacked Column 3D Map

• In the location portion of the pane, select the geographic data (in this case State) that provides the basis for the mapped area.
• Select Capacity(Sum) to control the height of our 3D map. This ensures that the height of a state’s column will represent the total generating capacity for a state.
• Selecting Energy Description as our Category and choosing the Stacked Column chart (the first option at the top of the pane in the  Data section), the energy capacity for each state is summarized by category. You may also choose a Clustered Column Map,  Heat Map, Bubble Map or Region Map.

Our 3D Map is shown in Figure 5. You can click the + button to enlarge the map. Clicking the - button shrinks the map. The up and down arrows move the map up or down while the left and right arrows rotate the map.

You can also use a mouse wheel to zoom in and out. Your map is saved as a Tour, which can be played back.  If you wish, you can open a new tour. Clicking the X in the upper right-hand corner pf the ribbon will close the map and return you to Excel.  Hovering over the column for a state provides useful insights into where a state gets its power. For example, Texas gets a large percentage of its power from natural gas, the state of Washington gets a large fraction of its power from hydroelectric power, and Indiana  gets a large  fraction  of its power from coal.

Figure 5: 3D Stack Column Energy Capacity Map

I am sure you will love using Filled Maps and 3D Maps to create amazing charts!