Contents
Yes, in Excel 2013 it is now possible to work with attractive charts that contain geographic maps. In this version of the program, the tool “power view“. This is a very powerful reporting tool. With it, we can create a sales report and show sales and customer data graphically on a map.
After studying this article, you will learn how to create very cool reports in Excel. We will show countries, cities and given points on a geographic map. The article is aimed at novice users who want to better understand this issue.
Important: The Power View add-in is not available in all versions of Excel 2013.
First steps
Let’s start with a simple Excel sheet.
In this Excel sheet, we have country names (Country), revenue data (Revenue) and number of customers (Number of Customers). We want to display in Excel the income for each country on a world map. We will do this with the help of a report with maps.
First of all, select the desired data. Then press the button Format as a table (Format as Table) and select a table style.
Your table will look something like this:
The next step is to create a chart using Power View. To do this, on the tab Insert (Insert) click the icon power view.
The first time you launch Power View, you will be prompted to enable this add-in. Just click Insert (Enabled).
Note that Silverlight must be installed. This is a tool designed to create interactive applications, it is necessary for building Power View reports. A message will appear prompting you to install Silverlight. Click on the link Install Silverlight (Instal Silverlight) to proceed to download and install the program.
After enabling the add-in, a new sheet will be created for our Power View report:
Click icon The Card (Map).
To increase the size of the map, drag the lower right corner down and to the right with your mouse.
As you can see, the countries listed in the Excel sheet (South Africa, France, Italy and Bolivia) are now marked on the map:
What do the blue circles mean?
The blue circles on the map show the amount of income from the column Revenue. The larger the circle, the greater the income. Working with Power View, you can customize what exactly should be shown on the map. This can be specified in the field Size (Size) panels Power View fields (Power View Fields):
That’s great, is not it?
Can Power View recognize cities?
Power View does a great job of recognizing cities. Let’s check it on the example of the following Excel table:
We want to show income for each city on the map.
Create a report in the same way as described above in the country example. Now it is easier to do this, because you no longer need to include or install anything.
Create a new Power View sheet and click the icon The Card (Map):
As you can see on the map, Power View circled the cities of Houston, San Jose, and Des Moines.
Sometimes in different countries there are cities with the same name. For example, the city of Santa Cruz is in the United States, Chile, Spain and Bolivia. In this case, you may need to provide additional information to select the desired city.
How can I determine the country and city using Power View?
To solve the problem of cities with the same names, you need to specify the name of the city and country in Excel:
In the column with city names (City column) enter the name of the city and the name of the country separated by a comma.
To update the data, right-click on the map and select from the context menu Update Sheet (Refresh Sheet).
Now that we have the exact country, we can be sure that all the cities in our Excel spreadsheet are in the US, which is what we wanted.
Cities around the world are found automatically. It’s great, right?
How can I mark certain geographic coordinates on the map?
If you need to specify the exact geographic coordinates on the map, Excel 2013 comes to the rescue again. To do this, we need latitude and longitude.
If the concepts of latitude and longitude are not familiar to you, I recommend that you familiarize yourself with the system of geographical coordinates.
In Excel, we need latitude and longitude to mark specific points on the map. Coordinates can be found in Google Earth, your smartphone can tell them, or you can use any other tool.
Translator’s Note: In this example, the satelliteview.com website was used to obtain the coordinates, which, unfortunately, was no longer functioning at the time of the translation of the article.
As you can see, on this site you can get the latitude and longitude of any point on the map.
Now we need to create an Excel spreadsheet with latitude (Latitude) and longitude (Longitude) written in columns.
As you did before, click power view tab Insert (Insert) and create a map.
If Power View doesn’t detect the latitude and longitude columns on its own, specify them manually. We want to display on the map the amount of income for each store from the column Revenue, for this on the panel Power View fields (Power View Fields) in a field Locations (Locations) select Revenue. Look at the picture below:
Shops are now shown on the map!
As you can see, we have created a very powerful report in Excel using Power View. All we needed for this was Excel 2013, Silverlight and a little knowledge of geography.
In this article, you learned how to graphically mark countries, cities, and geographic points using Excel and Power View.