How to build an interactive map in Excel
This article describes how to create a map in Excel, the map is an x y scatter chart with an inserted background picture.
The image above shows the map to the right and a table with cities and their chart coordinates. A drop-down list in cell B14 lets you pick a city and a formula extracts the appropriate coordinates.
A block dot displays the location of the selected city on the map and it changes accordingly when a new city is selected. There is a workbook to download below if you want to try it out.
How I built this map
A scatter chart allows you to place dots based on x and y values which is great in this case. I will show you how to
- insert a scatter chart
- create a drop-down list
- create formulas
- create a dynamic chart
- insert a background picture
Insert a scatter chart
The following steps describe how to place a scatter chart on a worksheet.
- Go to "Insert" tab on the ribbon.
- Click "Scatter" button.
- Click "Scatter with only markers" button.
A blank chart shows up on the screen.
You can click and hold with the left mouse button on the chart and then drag to the desired location.
Click and hold on the handles then drag to resize the chart, see image above. Hold the shift key as you resize the chart to lock the relationship between height and width.
Hold the Alt key while resizing to snap the chart to the cell grid.
Create a drop down list
The drop-down list makes it easier for the user to select items, it is populated with values from cell range B3:B10. Change the values in cell B3:B10 and the drop-down list automatically changes the list accordingly.
If you know you will be adding more values later I recommend that you convert cell range B2:D10 to an Excel Table. That will save you time adjusting the drop-down source cell reference when you add new values.
Remember to use the INDIRECT function if you reference an Excel Table in a drop down list.
- Select cell B14.
- Go to tab "Data" on the ribbon.
- Click "Data Validation" button.
- Go to "Settings" tab.
- Select "List".
- Select source range: B3:B10.
- Click OK.
Formulas
The worksheet has two cells containing formulas, they extract x and y values respectively from the list based on the selected item in cell B14.
Formula in cell C14:
The MATCH function looks for the selected value in cell B14 in cell range B3:B10 and returns a number representing the position of that value.
MATCH($B$14, $B$3:$B$10, 0)
becomes
MATCH("Berlin", {"Vienna"; "Copenhagen"; "Warsaw"; "London"; "Madrid"; "Paris"; "Rome"; "Berlin"}, 0)
and returns 8. Berlin is the eight and last value in cell range B3:B10.
The INDEX function returns a value from cell range C3:C10 based on a row number which is provided by the MATCH function.
INDEX(C3:C10, MATCH($B$14, $B$3:$B$10, 0))
becomes
INDEX(C3:C10, 8)
becomes
INDEX({34; 54; 46; 44; 15; 37; 17; 46}, 8)
and returns 46 in cell C14.
Formula in cell D14:
This formula is exactly the same as the first one except that the INDEX function returns a value from D3:D10.
Adjust chart data source
The dot location on the chart is based on the values in cell C14 and D14, the following steps demonstrate how to change the data source to these two coordinates.
- Right click on the chart.
- Click "Select Data" from the menu.
- Click "Add" button.
- Select a name, an x value, and a y value.
- Click Ok.
Insert a background picture
The map will be the background picture in our chart, make sure the chart has the same ratio between height and width as the image. You will get a chart that looks stretched out horizontally or vertically if the don't match.
- Right-click on the empty chart.
- Click "Format Plot Area...".
- Click "Fill".
- Select "Picture or texture fill".
- Click "File..." button.
- Select a picture.
- Click "Insert".
- If you like, change "Transparency" value.
- Click "Close" button.
To check the image aspect ratio open windows file explorer and locate the image. Hover over the image file name with the mouse cursor and a box appears named tool tip. It contains the image width and height and also the image file size.
Divide the height with the width to get the aspect ratio we need. Now go back to Excel and double click on the chart.
A format pane shows up on the right side of the screen, click on the Size and properties button and then click on the black arrow next to "Size" to expand settings.
Make sure the height and width has the same aspect ratio as the background image.
Divide the chart height with the width to calculate the chart aspect ratio.
Change the chart height or width to make the aspect ratios match.
Chart settings
The chart has a horizontal, and a vertical axis, gridlines, a chart titel and a legend that is not needed, we are now going to remove those chart elements.
The marker type can be customized as well, I will describe how below.
- Click on the Legend and press Delete button to remove it from the chart. You can always get it back later if you change your mind. CTRL + z will undo your last action or go to "Chart Design" tab on the ribbon. Click on "Add Chart Element" button and click on the element you want back.
Now delete the chart title as well.
- Delete chart gridlines. You do that by clicking on the to select them and then press Delete.
- Change x and y axis minimum and maximum value to 0 and 100.
- Make sure x and y coordinates in the table are ok. If not, make appropriate adjustments.
- If you like, delete x and y axis.
- Select data series on the chart.
- Right click on data series.
- Click "Format Data Series...".
- Click "Marker Options".
- Select "Built-in".
- Select a type.
- Click "Marker Fill".
- Select "Solid fill".
- Pick a color.
- Click Close.
Final notes
First I thought of using longitude and latitude coordinates but I gave that up really quickly. The map is downloaded from Wikimedia Commons.
Tip! You can add a data label and use the series name to show the city name on the map.
- Right-click on the marker.
- Click on "Add Data Labels".
- Double click on the data label next to the marker to open the Format pane.
- Select "Series Name" and deselect the remaining check boxes.
Recommended links
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
I made a heat map calendar a few months ago and it inspired me to write this article. The heat […]
Excel 2016 owners with an office 365 subscription can now easily build beautiful map charts. Excel uses maps from Bing […]
Improve your X Y Scatter Chart with custom data labels
The picture above shows a chart that has custom data labels, they are linked to specific cell values. What's on […]
This picture below shows you a column chart with pictures (flags) below each column. Watch this video to learn how […]
8 Responses to “How to build an interactive map in Excel”
Leave a Reply to Sami Jumppanen
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
From where we will get the value of x and y
jitendra,
You can use longitude and latitude coordinates if your map has straight longitude and latitude lines.
https://www.worldatlas.com/aatlas/findlatlong.htm#.UZM85bWeNPY
Oscar, I am missing something here, are those x and y just longitute and latitude values? If I were to download a picture of Middle East, all I need is to change the x and y values according to the longitudes and Latitude?
chrisham,
are those x and y just longitude and latitude values?
No, this map doesn´t have straight longitudinal and latitudinal lines. I am not even sure if that kind of maps exist.
Example, here is a map with longitudinal and latitudinal lines.
https://www.mapsofworld.com/world-maps/world-map-with-latitude-and-longitude.html
This is excellent! I'm looking for ways to graph location points on a map, and this article clears the basic procedure: there is no map object, you just need to add the map and ensure correct scaling for the x and y. Thanks.
Sami Jumppanen,
Thank you for commenting.
I am trying to do this same example but for North America. I have Lat/long coordinates but want to build an excel formula to convert the lat long into x y coordinates as you have. Any tips?
Dear Sir,
I tried xy scatter charts with actual coordinates ( lat. & Long.) value for district map of Rajasthan india but it is not showing points on exact location on map. i have download map from website ( google maps) but i am not getting results as i require
pls help to get scale maps
Thnx
DPK