Contents
If your company has branches around the country or sells not only within the Moscow Ring Road, then sooner or later you will be faced with the task of visually displaying numerical data from Microsoft Excel (sales, applications, volumes, customers) on a geographical map with reference to specific cities and regions. Let’s take a quick look at the main ways to visualize geodata that exist in Excel.
Method 1: Fast and Free – Bing Maps Component
Starting from the 2013 version, Excel has a built-in app store, i.e. it became possible to purchase, download and install additional modules and add-ons with missing functions. One of these components just allows you to visually display numerical data on a map – it is called Bing Maps and, which is especially nice, is absolutely free. To install it, open the tab Insert – Store (Insert – Office Apps):
After inserting the component, a dynamic container with a map should appear on the sheet. To visualize your information on the map, you need to select a range with geodata and press the button Show Locations:
If necessary, in the settings (gear icon in the upper right corner of the component), you can change the colors and type of displayed charts:
It is also possible to quickly filter the cities, displaying only the ones you need (funnel icon in the upper right corner of the component).
You can easily bind not only to cities, but also to other objects: regions (for example, Tula region), autonomous regions (for example, Yamalo-Nenets) and republics (Tatarstan) — then the circle of the diagram will be displayed in the center of the area. The main thing is that the name in the table matches the captions on the map.
Total in pluses of this method: easy free implementation, automatic binding to the map, two types of charts, convenient filtering.
В cons: you need Excel 2013 with Internet access, you cannot select regions and districts.
Method 2: Flexible and Beautiful – Map View in Power View Reports
Some versions of Microsoft Excel 2013 come with a powerful report visualization add-in called Power View that allows (among other things, and it can do a lot!) Visually display data on a map. To activate the add-on, open the tab developer (Developer) and click on the button COM add-ins (COM Add-ins). In the window that opens, check the box next to Power View and click OK. After all these manipulations on the tab Insert (Insert) you should have a button power view.
Now you can select the range with the source data, click on this button – a new sheet will be created in your book (more like a slide from Power Point), where the selected data will be displayed in the form of a table:
You can easily turn a table into a geographical map using the button The Card (map) tab Constructor (Design):
Pay special attention to the right panel Power View fields – on it, unlike primitive Bing Maps, by dragging the names of columns (fields) from the source table with the mouse and dropping them into different areas, you can very flexibly customize the resulting geo-representation:
- To area Locations (Locations) it is necessary to throw a column from the source table containing geographical names.
- If you do not have a column with a name, but there are columns with coordinates, then they need to be placed in the area Longitude (Longitude) и Latitude (Latitude), respectively.
- If in the area Color (Colour) drop the goods, then each bubble will be, in addition to the size (displaying the total profit in the city), detailed into slices by goods.
- Adding a field to an area vertical or Horizontal multipliers (Dividers) will divide one card into several by this field (in our example, by quarters).
Also on the contextual tab that appears at the top Layout (Layout) you can customize the map background (color, b/w, outline, satellite view), labels, titles, legend, etc.
If there is a lot of data, then on the tab power view you can enable special Filter area (Filters), where using the usual checkboxes you can choose which cities or goods you want to show on the map:
Total in pluses: ease of use and flexibility of customization, the ability to split one card into several categories.
In the downsides: Power View is not available in all Excel 2013 configurations, there are no other types of charts other than bubble and pie charts.
Method 3: Expensive and professional – Power Map add-on
This is a separate COM add-on for the most severe cases when you need a complex, professional-looking, animated visualization of a large amount of data on any (even a custom map), and with a video of the process dynamics over time. At the development stage, it had the working name GeoFlow, and was later renamed Power Map. Unfortunately, the full version of this add-in is only available to buyers of either the full version of Microsoft Office 2013 Pro or Office 365 enterprise subscribers with a Business Intelligence (BI) plan. However, comrades from Microsoft give a preview of this add-on to download “to play” for free, for which we thank them.
Link to download Power Map Preview from Microsoft Download Center (12 Mb)
After downloading and installing the add-on, you need to connect it on the tab Developer – COM Add-Ins (Developer — COM Add-ins) similar to Power View from the previous paragraph. After that, on the tab Insert button should appear The Card (map). If we now select the table with the source data:
… and click the Map button, then we will be taken to a separate window of the Microsoft Power Map add-in:
Without going into details (which is enough for a separate training for half a day), then the general principles of working with the map are the same as in the Power View described above:
- The size of the columns is determined by the source table column (Revenue), which we will throw into the field Height in the right panel. The principle of counting, as in pivot tables, can be changed in the drop-down list of fields:
- To detail each column of sales for individual products, you need to fill in the field Product to the area Category (Category).
- You can use different types of charts (bar charts, bubbles, heat map, filled areas) using the buttons on the right panel:
- If the source data has a column with sales dates, then it can be thrown into the area Time (Time) – then the time axis will appear below, along which you can move into the past-future and see the process in dynamics.
The “wow moment” of the Power Map add-on can perhaps be called the ultimate ease of creating animated video reviews based on the maps made. It is enough to make several copies of the current scene from different viewing angles and different scales – and the add-in will automatically create a 3D animation of flying around your map, focusing on the selected places. The resulting video is then easily saved in mp4 format as a separate file for insertion, for example, on a Power Point slide.
Method 4. Bubble Chart with “File Refinement”
The most “collective farm” method of all listed, but working in all versions of Excel. Build a bubble chart (Bubble Chart), disable its axes, grid, legend … i.e. everything but the bubbles. Then manually adjust the position of the bubbles by placing the previously downloaded image of the desired map under the diagram:
Cons of this method are obvious: long, dreary, a lot of manual work. Moreover, the output of signatures for bubbles is a separate problem when there are a lot of them.
Pros in that this option will work in any version of Excel, unlike the following methods, where Excel 2013 is required. And no Internet connection is required.
Method 5: Third party apps and add-ons
Previously, there were several add-ons and plug-ins for Excel that allowed, with varying degrees of convenience and beauty, to implement the display of data on a map. Now the vast majority of them are either abandoned by developers, or in the stage of silent dying out – it’s hard to compete with Power Map 🙂
Of the survivors worthy of mention:
- MapCite – perhaps the most powerful of all. Able to be attached to the map by the names of settlements, regions, districts and coordinates. Displays data as points or a heat map. Uses Bing Maps as a base. Automatically knows how to throw the created map into a Power Point presentation. A free trial version is available for download, the full version costs $99/year.
- Esri Maps – an add-on from Esri that also allows you to load and analyze geodata from Excel onto maps. Many settings, various types of charts, supports . There is a free demo version. The full version requires a subscription to the ArcGis mapping service.
- MapLand– one of the first add-ins on this topic, created for Excel 97-2003. It comes with a set of maps in the form of graphic primitives, to which the data from the sheet is attached. Additional cards must be purchased. A demo for different versions of Excel is available for download, the Pro version costs $299.