The sample input data that we’ll use to build an interactive map chart
The small sample data in this tutorial is very simple but it demonstrates the approach we’ll use to build a nice looking interactive map chart. You can easily apply this technique whatever type of regions, products, divisions or other numeric values you want to show. The main principle here is building a scatter plot (with a custom background image) that will be converted later into a bubble chart.
Pro Tip: This technique is based on the feature of a scatter plot to show its values according to their X and Y coordinates on a chart area. We use that to set the required position of all our Regions. A custom background image is used to visualize an appropriate geographical area (world map, country map or regional map). Once those steps are done, we’ll convert a scatter plot into a bubble chart. Technically, a bubble chart is the same as a scatter plot, but with an additional dimension, so we use this trick to add one extra dimension to our existing scatter plot. This will be used for our KPI.
The table in the image below has 3 columns that contain information needed for the map chart. We have information on Revenue (column C), Divisions (column A) and geographical Region (column B). The creation of a map chart requires a little bit of preparation. The more Regions we have in a table, the more preparation work we need to do to create the chart. The good news: we need to do this only once. After we have done this step, we can add as many KPIs as we need without any problem.
Step 1. Insert an Empty Scatter Plot.
Click on a blank cell outside the data table (otherwise a scatter plot will be built on the values from this table). After that go to:
INSERT tab on the ribbon > section Charts > Insert a Scatter Plot
Select an empty cell to create a blank scatter plot
Why do we create a blank scatter plot? Because we don’t have any values to set a correct position for the Regions as the Revenue by itself doesn’t represent any information about the required X and Y coordinates. So we need to add such data by ourselves a bit later.
Step 2. Select a Custom Background Image as the map chart.
Based on that, we will define the X and Y coordinates. To do this we need to select the chart area. Double click on the chart area to open the panel with Format Chart Area options. Here we need to select and expand Fill options. From the list visible, we need to select Picture or Texture Fill. Once we select this radio button, we’ll see three options: Insert an image from File…, Clipboard or from Online… source. We can use the first one if we already have an image saved on our computer. The second option will be active when we have copied an image and we want to paste it from the Clipboard. In this tutorial, we use the third option – Online… – so let’s import the image from the Internet. Click on the Online… button to bring up the search Window. In the Bing search field, type the required keyword(s) – we used this combination:
Pro Tip: If you want, you can select an image from OneDrive cloud. You can toggle between Bing and OneDrive (or between more sources if you have access to them) by clicking on the Bing icon near the search field.
Importing a custom background image for the chart area from the online source
Note! Keep in mind that we can use any image we want if we are building the chart only for our personal needs. However, if we need to use the Excel file for any commercial or business purposes (or share the file with other people), we are only allowed to use the images that are tagged with Creative Commons licenses (the default search option). Otherwise, the use of an image protected by copyrights can be considered as copyright infringement and cause appropriate legal consequences.
Consider, however, that even this license category has several different image types and some of them may require the use of the appropriate Creative Common attribution. You can find more details about a license type and an attribution that is required by a specific image by following the link under the selected image.
Once we select the desired image, click the Insert button to add it as a background for the chart area.
Step 3. Change the size and slightly edit the background image so it’s the size you’d like to have.
By default, the image is expandable in every direction, i.e. we can change it as we want. The problem is if we don’t lock its aspect ratio, the image can be deformed or stretched out in a wrong way when its horizontal and vertical size is changed disproportionately. To prevent this, we can double click on the image to open the Format Chart Area panel, then select and expand Size options and check the Lock Aspect Ratio box. Now the image size will be adjusted proportionally.
Pro Tip: You can lock aspect ratio temporally in an easier way, by simply pressing SHIFT button on the keyboard while you are expanding the image.
In addition, we can increase the transparency of the image as we want to focus attention on the Revenue more than on the image itself. To do that, we need to select Fill options on the Format Chart Area panel and set the Transparency parameter that can be found under the Buttons which we use to import the image from the external source. In this tutorial, we set the Transparency at 70%. Also, we do not want the image to change its size or position when we change or move the cells. Mark the Don’t move or size with cells radio button at the bottom of the same section.
Pro tip: Once the Transparency is increased, the gridlines become more visible. To hide the gridlines go to the VIEW tab on the Ribbon > Show section > uncheck Gridlines box.
We can expand the image, make it more transparent and hide the gridlines to make the chart be more attractive
Step 4. Set the position of the regions on the chart.
To do that we need to split the entire image area into imaginary coordinate axis (X and Y). To get the regions defined properly, we need to fix the X and Y axis. You can use any type of scaling you’d like, for example from 0 to 1, from 0 to 10, from 0 to 100 etc. We’ll write X and Y coordinates for every Region from our table accordingly to the selected scale. In this tutorial, we use Min 0 and Max 10 for both the X and Y axis.
Let’s create the additional table that will be used as a basis for the chart. Besides the list with names of unique Regions, this table needs to contain two additional columns – for X and Y coordinates respectively. Our initial step is to guess the position of every region (i.e. its central point) on the selected imaginary coordinate axis. In other words, we split the entire height (axis Y) of the image into equal parts from 0 to 10 and repeat this for the width (axis X). In such case, the estimate coordinates of North America will be near the point (x = 2, y = 8); the coordinates of South America – near the point (x = 3, y = 3) and so on. We need to repeat this exercise for all Regions, but only once.
Pro tip: We can copy/paste the list of Regions manually and that’s OK when we don’t have many of them. But in the case when we have, let’s say, 100+ regions, especially when they can be repeated several times inside the input table, it can be time consuming to prepare the list that contains only unique Regions without any duplicates. There is an array formula that can help in this case.
Let’s select cell G4 in our example and add the following array formula (make sure you press CTRL + SHIFT + ENTER at the end instead of ENTER to create an array formula):
Drag and drop this formula to other cells (as many as you need) to return the list of the Regions without any duplicates.
We can expand the image, make it more transparent and hide the gridlines to make the chart more attractive
Step 5. Add the data for the coordinates to the chart.
Right-click on the chart area and click Select Data. In the dialogue window that appears, select the Add series button at the left side of the window. Inside the Edit Series box that appears, add a Series Name as “Location”, and put the coordinates that we wrote into the appropriate fields for Series X values and Series Y values.
Adding a new series – based on the coordinates we wrote – into the chart
Step 6. Apply final fixes.
In case the axis is still set to auto-scale, adjust it. To fix the auto-scaling of the chart axis, select axis X and double-click it. On the Format Axis panel, we go to Axis options and manually input 0 (zero) as Minimum axis bound and 10 (ten) as a Maximum axis bound. If we did everything properly we see that the status near that values changed, and the button Reset appeared. Do the same exercise for axis Y. Once this has been ready, we can delete unwanted gridlines and axis titles. Just select the appropriate element on the chart area and press the DELETE button on the keyboard.
After we have cleaned the chart area, we can correct the coordinates that are still shifted.
Fixing Minimum and Maximum values for axes X and Y. Deleting all unnecessary elements (axis titles, chart title, gridlines). Correct the coordinates for the points that are still shifted.