What is Power Map for Excel?
Microsoft Power Map for Excel 2013 is a Power BI tool that allows you to visualize geographic data on a map (Bing Maps) in Excel 2013. This will give you insight into the geographic distribution of your sales, customers, competitors, incidents, and more.
In this blog post, we'll walk you through how to use the tool for the first time.
Applications
Some possible uses of Power Map for Excel:
- Map view of your customer portfolio: this allows an account manager to see the geographic distribution of their customers on a map. This is useful for planning sales visits and reducing transportation costs (travel efficiency).
- Map view of customer service requests: Which customer service requests are currently pending in the area?
- Where did each incident occur?
- In which regions is revenue the highest?
- etc.
Install Power Map
You can use the Power Map tool Download for free here from the Microsoft website. Follow the on-screen instructions to install the tool.
See the image below: after installation, you will see the Folder Find the button under the Insert tab in the group Tours.
Create a Power Map
To use Power Map, start with a Excel Workbook (.xslx) file. At least one column in your data table must contain geographic information. This could include, for example, a ZIP code, city, country, or latitude and longitude.
Use Microsoft Dynamics CRM to create the desired data list and export it to Excel. Below, we describe the various steps:
STEP 1: Select the data
Decide which data you want to display on the map. Using the function Advanced Search Create a new view in Microsoft Dynamics CRM.
For example: The search query below returns a list of all companies for which you are the person in charge.
It is important that the display include at least one column of geographic data.
To illustrate, below is an account form in which we have entered the following geographic information:
- Address
- Street
- Location
- Zip Code
- Latitude & Longitude
ATTENTION: Power Map automatically determines the geographic location based on the data you provide. If you want to specify the exact location, it is, of course, best to use latitude and longitude coordinates.
STEP 2: Export a view
Through the Export This Microsoft Dynamics CRM feature allows you to export records from a view.
- Navigate to the view and click the button Export to Excel
- Select the option Static Worksheet
- Click on Export
- Open the export file (in .xls format) in Excel 2013
- Save the export file as a Excel Workbook
STEP 3: Create a Power Map based on the exported data
- Select the data in your table (including column headers).
- On the Insert tab, click the button Folder.
- In the drop-down menu, click on Launch Power Map.Power Map opens in a new window.
- In the section Geography and Map Level Specify which geographic field in the Excel file corresponds to which type of address field. For example: if your Excel file contains a column labeled “ZIP Code,” have this field map to the geographic field* Zip. You must create this mapping for each column containing geographic data that you wish to use for location determination.
- Click the button in the lower-right corner Next. Power Map will now generate the map. Click the Map button Labels to show more details.
*Power Map for Excel supports various types of geographic data, such as latitude and longitude, street name, province, ZIP code, and country. Use multiple geographic values to increase accuracy. If Power Map is initially unable to determine the location of a specific point, these fields are used to visualize the point.
STEP 4: Visualizing and Interpreting Data
There are several ways to visualize your data. We'll illustrate this with a few examples.
Heat map
To show geographic density on a map, it is helpful to use a heat map.
The red zones indicate high density.
Bar Chart
In our export, we have an additional column in addition to the geographic data Annual Revenue. Because the annual revenue figures are linked to the account records, we can easily display them on a map. In the example below, we visualize the annual revenue based on the geographic value zip code.
