Using categories with 3D Map in Excel

image Till now we have been plotting just one number for each geographical location. Now we will understand how the number can be broken down by categories available in your data. This way you can see traditional charts on top of the map – another new way of understanding information better.

Required Reading

This article is a part of series about Power Map usage. Please read the following articles before reading this one.
Instant, Interactive 3D mapping of your data – Power Map
Uncover hidden secrets: 3D, Time Animated Map using Power Map
3D Map with Date/Time based animation: In-depth
3D map – Time Animation – Part 2

Power Map – what should the data contain

Here is another block of data which we will use in this article. For Power Map, the minimum data you require is one column containing some geographic information (Country in this case) and one numeric column to plot the chart on the location (Revenue in this case). Another type of column which can be used is a date time column (Year in this case, shown as a date). We have already seen how to work with these three types of columns.

Now let us work with another type of column called Category. In this case we have the Product column. I am sure you can relate to similar data in your context even though the columns may be different. For example, for HR, the data could be Location, Headcount, Gender and Date of Joining. In Finance, the data could be location, expense, expense type and month.

To simplify things we have 3 countries (Thailand, Malaysia and Combodia), 3 products (Car, Bus, Truck) and two years (2012 and 2013).


Use this file to follow along the steps in this article Download Sample File

Create base Power Map with Country and Revenue

This is easy. Click inside the data – Insert Power Map. Countries will be plotted automatically. Click Next at the bottom and click on Revenue to plot the bars on the countries. Now the map should look like this – showing country and combined revenue (SUM) across both years.



Viewing the data in different ways

This is the default way of showing the data. Now let us look at different ways in which the same data can be shown. This is controlled from the buttons just above the Height section (which currently has Revenue (SUM)).

Stacked Column

The map shown above is using the default setting – Stacked Column. There is only one value for each country so the bar is a single column – no stacking.


Height is the total revenue by each country. Here are the totals for your reference.


The second option is for Clustered Column. Again, this does not show any clusters because we have not yet added any category. We will see this shortly.

Bubble Chart

The next one is like a bubble chart. The size of the bubble shows the total revenue for each country. Notice that the caption Height is now replaced with Size. This indicates that the Total Revenue will now decide the size of each bubble.


Remember that this is a 3D view. So in order to compare the bubble sizes visually you may need to rotate the globe. You can also hover the mouse on top of each country to see the actual number in a tooltip.


If you want to do a purely visual comparison, convert it to a Flat Map. Change the pitch using the UP / DOWN arrow till it is visually flat.


Now the values are visually comparable.


Region visualization

Region shows the geographic boundaries using color. Usually this is used as one of the layers. One layer showing region (Country, State, etc.) and another layer showing numeric information (like Revenue in this case).


Understanding Categories

Now let us introduce a category. Category is something which segments the overall number being plotted. Now I am changing the visualization to the first option – Stacked Column and adding a category – which is the Product itself. When you click on Product (which is text), it automatically gets added to the category area. Categories work with Column, Bubble and Region charts.


Stacked Column with Categories

The column chart is now divided and color coded by product. Legend will show what each color means.


Mouse hover on any area will show the details in a tooltip.


Clustered column with categories

Now change the visualization to the second option and see the result.



Bubble chart with categories = Pie Chart


Region visualization with categories

This is a bit complex. When you add Product as categories, it shows different colors for each country.

Understanding Heat Map

Heat map works differently compared to all other visualizations. Therefore, I am discussing it separately.

Heat map does not work with categories. When you choose Heat Map option, categories are not shown. The Height field is shown as Value, which is Revenue(SUM) in this case.


Here is the actual revenue by country for your reference.image
Let us see what happens when you change the visualization to heat map.  In order to understand the map I have opened the settings menu (Gear Icon) and the Legend which shows the color scale.


Initially the scale is from lowest value – which is 465 for Combodia to highest value – 23 which is for Malaysia. Combodia is not shown at all because it is at the lowest end of the scale. So there is nothing to show. Value of Malaysia is 523 and for Thailand it is 520. Therefore it shows a different kind of shading for each.

Now let us increase the Color Scale slider value from 100 to 110.


Now Combodia is plotted but the colors are different. The legend shows that the boundary values have been changed – 422 to 475. Now the value of Combodia falls above the lower limit. Therefore it is plotted. It does not go all the way up to 475. Therefore, the scale stops at the color equivalent of Combodia value which is 465 – somewhere in the orange region.

Other values are beyond the red color – that is 475. Therefore, it is increased. Malaysia value is 523 and Thailand value is 520. Therefore, both heat maps look almost the same. Now, I am going to change the value of Malaysia to 650. Now, notice what has changed.


The scale readjusted to the higher value. Now it is from 422 to 590. Malaysia value is beyond 590 so it has lots of red color – to indicate that it so much beyond the scale maximum.

Cambodia value has remained the same but its colors have changed. Now it has blue and green only. No Yellows. Why? Because the scale is now bigger. The actual value of Combodia (465) is represented somewhere in the green region. That is why it stops there.

Thailand value is 520. Therefore on a color scale which ends at 590, the value is represented by the yellow color. That is the reason why Thailand heat map starts with blue an continues inside till yellow. No red or orange is shown in Thailand heat map because the value does not reach that high as per the color scale.

Here is an expanded version of color scale to show which values represent which color. Click on this image to see it in full resolution to appreciate the percentages and values.


What next?

The visualization options work with timeline as well. Add the year and see the animation as we have done earlier. In the next article I will cover how to create impressive visual geographical presentations using Power Map. This is a completely new way of showing plans, analyzing past performance, showing forecasts, planning product launches or promotions, explaining spread of a disease and so many different day to day scenarios.


Queries | Comments | Suggestions | Wish list