Working with Maps in Excel 2013
Yes, now it is possible to work with nice charts that have Maps in Excel 2013. The latest version comes with the Power View. It is an extremely powerful reporting tool. Sometimes we need to get sales reports and we want to identify regions and patterns of sales related to the customers.
In this article you will learn how to create cool reports in Excel. We will show countries, cities and specific geographical points. This article is for newbies who want to learn about this exciting area.
Getting Started
Let's start with a simple Excel sheet
We have countries, the revenue and the number of customers in an Excel Sheet. We want to see the revenue per country on a World Map using Excel. So, let's create a report with maps.
First, select your data. Then click on the Format as Table icon and pick a table style.
Your table will look like the one below:
The next step is to create the charts with Power View. To do this, go to the menu and select the tab Insert -> Power View.
If it is the first time that you use Power View you will be prompted to enable the feature. So, just press the Enable button.
Please note that you will need Silverlight installed. Silverlight is a tool for creating interactive applications, and it is required to generate Power View reports. You will see a message offering to install the program. Click on the Install Silverlight link to get it.
If you receive the message that your current security settings do not allow this file to be downloaded, visit Your current security settings do not allow this file to be downloaded to solve the problem.
Once enabled, a new sheet will be created for our Power View reports:
Now choose the Map option.
In order to enlarge the map, click in the bottom right corner and move your mouse cursor to the right and down.
As you can see, the countries from your Excel Sheet (South Africa, France, Italy and Bolivia) are now displayed on the map:
What do the blue circles mean?
Blue circles on the map show the revenue. The bigger a circle the bigger our revenue is. With Power View you can chose what you want to check on the map using the Size field on the Power View Fields pane:
Awesome. Isn't it?
Can Power View detect cities?
Power View is great in detecting cities. Let's try it with this Excel table:
We want to see the revenues per city on the map.
Follow the same steps to create reports that we used for the countries. Now it will be easier because you do not have to install or enable anything.
We are going to create a new Power View sheet and select the map option:
As you can see, Power View spots the cities and it shows Houston, San Jose and Des Moines.
Sometimes there are cities with the same name in different countries. For example, there is a city named Santa Cruz in USA, Chile, Spain, and Bolivia. In this case you may need to give more details to display the correct city.
How can I specify the country and city with Power View?
In order to solve the problem with identically named cities, you need to specify both the name of the city and country in Excel:
In the city column, we type the city and the country separated by comma.
To update the information right click on the map and select the option Refresh Sheet.
With the country specified we are now sure that all the cities in our Excel table are from the USA as expected.
It can detect cities around the world automatically. Cool isn't it?
What if I want to graph specific geographical coordinates on the map?
If you want to display certain geographical coordinates on the map, your Excel 2013 can do that. For this purpose we will work with latitude and longitude. These coordinates are necessary to graph the points on our map.
If you do not know anything about latitude and longitude, please have a look at the article Geographic coordinate system.
In Excel you will need the latitude and longitude to graph specific points. You can get the points using Google Earth, your smartphone can help you to get this information or you can use any other tools.
In this sample I will use the website satelliteview.com
As you can see, it is possible to obtain the latitude and longitude of any blog from the site.
Now you will need a table with the Latitude and Longitude columns in Excel:
As we did before, go to Insert <->Power View and create a map.
Power View detected the Latitude and Longitude columns. We need to add Revenue to the Locations list, because we want to see the Revenue value per store on the map. Please see the screenshot below:
Now you have the stores displayed on the map!
As you can see now, we have got an extremely powerful report in Excel with Power View. All we need are Excel 2013, Silverlight and some geography knowledge.
From this article you learnt how to graph the countries, cities and geographical points using Excel and Power View.
