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 get 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.
- Press with left mouse button on "Scatter" button.
- Press with left mouse button on "Scatter with only markers" button.
A blank chart shows up on the screen.
You can press and hold with the left mouse button on the chart and then drag to the desired location.
Press 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.
- Press with left mouse button on "Data Validation" button.
- Go to "Settings" tab.
- Select "List".
- Select source range: B3:B10.
- Press with left mouse button on 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.
- Press with right mouse button on on the chart.
- Press with left mouse button on "Select Data" from the menu.
- Press with left mouse button on "Add" button.
- Select a name, an x value, and a y value.
- Press with left mouse button on 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.
- Press with right mouse button on on the empty chart.
- Press with left mouse button on "Format Plot Area...".
- Press with left mouse button on "Fill".
- Select "Picture or texture fill".
- Press with left mouse button on "File..." button.
- Select a picture.
- Press with left mouse button on "Insert".
- If you like, change "Transparency" value.
- Press with left mouse button on "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 press with left mouse button on the chart.
A format pane shows up on the right side of the screen, press with left mouse button on the Size and properties button and then press with left mouse button 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.
- Press with mouse 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. Press with mouse on "Add Chart Element" button and press with left mouse button on the element you want back.
Now delete the chart title as well.
- Delete chart gridlines. You do that by press with left mouse button oning 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.
- Press with right mouse button on on data series.
- Press with left mouse button on "Format Data Series...".
- Press with left mouse button on "Marker Options".
- Select "Built-in".
- Select a type.
- Press with left mouse button on "Marker Fill".
- Select "Solid fill".
- Pick a color.
- Press with left mouse button on Close.
Final notes
First I thought of using longitude and latitude coordinates but I gave that up really quickly. The map is geted from Wikimedia Commons.
Tip! You can add a data label and use the series name to show the city name on the map.
- Press with right mouse button on on the marker.
- Press with mouse on "Add Data Labels".
- Double press with left mouse button on the data label next to the marker to open the Format pane.
- Select "Series Name" and deselect the remaining check boxes.
Recommended links
Charts category
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. […]
You can easily change data labels in a chart. Select a single data label and enter a reference to a […]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
The picture above shows a chart that has custom data labels, they are linked to specific cell values. This means […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
I made a heat map calendar a few months ago and it inspired me to write this article. The heat […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
Drop down lists category
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
Interactive category
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
This article demonstrates how to change chart series while hovering with mouse cursor over a series name. The image above […]
I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]
What if you want to show a selection of a data set on a chart and easily change that selection? […]
Maps category
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 […]
Scatter x y chart category
The picture above shows a chart that has custom data labels, they are linked to specific cell values. This means […]
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]
The picture above shows the following equation x^3+3*x^2-3 plotted on an x y scatter chart. Here are the instructions on how […]
This tutorial shows you how to add a horizontal/vertical line to a chart. Excel allows you to combine two types […]
The chart above is built using the NORM.DIST function and is called Normal Distribution or Bell Curve chart. This curve is often […]
The scatter chart is great for charting numeric values in pairs, for example, coordinates. It lets you compare multiple data […]
Functions in this article
More than 1300 Excel formulas
Excel categories
8 Responses to “How to build an interactive map in Excel”
Leave a Reply
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 use 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 the exact location on the map. I have received a map from website ( google maps) but I am not getting results as I require
pls help to get scale maps
Thnx
DPK