Visualizing location data by time – Excel 3D Map

The good old Microsoft Excel has an effective utility called 3D Maps. It’s great for simple geo-animations. My original use case was to visualize geographical master data for one of Solita’s customers.

See the demo video (00:35 min) created from OECD asylum seeker inflow data from years 2000-2017. The asylum data is not related to the customer project.

Next I will show how to create a 3D animation yourself.

Preparing the data for Excel 3D Map

I downloaded OECD asylum data from International Migration Database in CSV format.

After saving the CSV to my laptop I imported the data to Excel from Data > New Query > From file > From CSV.

The data had only a Year column, so I needed to create a Date column to fulfill 3D map requirements. For example year 2018 became date 31.12.2018.

The data set had lots of information about different types of asylums in each country. For the demo I only took Inflows of asylum seekers by nationality category and Total for each country.

Creating a new tour in Excel 3D Map

You can create a new 3D map tour in Excel from Insert > 3D Map.

A tour contains multiple scenes. In my demo the tour had five scenes.

Transitions and animations between the scenes are automatic. You just zoom the map to the right position for each scene. When all the scenes in the tour are in place, the tour can be played or you can create a video file from it.

Excel 3D Map has relatively few editing options. This makes the use cases somewhat limited but keeps things simple.

Creating a 3D Map with a time dimension

This is what you see when entering to 3D Map editing view.

Here are the setting I adjusted from the panel on the right side of the screen.

Layer name. In the demo there’s only one layer. It would be possible to add multiple data layers per scene. You could have different data series for the number of asylums and number of immigrants.

Visualization type. There are five icons in the right panel where you can choose. My selection was region. Other options are stacked column, clustered column, bubble and heat map.

Location. Column in the data that represents the location. I chose the Country column. Could be also an address or coordinates.

Value. Column in the data that represents the magnitude, height or intensity of the location. In this example it’s the Number of asylum seekers in a specific year and country.

Time. It would be simple to show asylums in different countries on a single year. To make the demo more interesting I added a timeline to animate the asylum numbers annually. From the clock icon select Data stays until it is replaced to avoid showing cumulative numbers.

Layer options. Changed the color from blue to red.

Finally you can set the scene transition duration by clicking the gear icon from a specific scene in the left panel.

For some scenes I added a Text Box from the top menu (the Ribbon) to explain what is happening.

Creating a video from the 3D Map tour

Click Create Video from the ribbon.

Select the quality. Background music often makes the video more vivid, so you can add music from Soundtrack Options. I downloaded the sound track from here.