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
- Create a Map chart in Excel
- How to make a killer map using Excel in under 5 minutes with PowerMap plugin
- How to create an interactive Excel dashboard with slicers?
Built-in Charts
Combo Charts
Combined stacked area and a clustered column chartCombined chart – Column and Line on secondary axis
Combined Column and Line chart
Chart elements
Chart basics
How to create a dynamic chartRearrange data source in order to create a dynamic chart
Use slicers to quickly filter chart data
Four ways to resize a chart
How to align chart with cell grid
Group chart categories
Excel charts tips and tricks
Custom charts
How to build an arrow chartAdvanced Excel Chart Techniques
How to graph an equation
Build a comparison table/chart
Heat map yearly calendar
Advanced Gantt Chart Template
Sparklines
Win/Loss Column LineHighlight chart elements
Highlight a column in a stacked column chart no vbaHighlight a group of chart bars
Highlight a data series in a line chart
Highlight a data series in a chart
Highlight a bar in a chart
Interactive charts
How to filter chart dataHover with mouse cursor to change stock in a candlestick chart
How to build an interactive map in Excel
Highlight group of values in an x y scatter chart programmatically
Use drop down lists and named ranges to filter chart values
How to use mouse hover on a worksheet [VBA]
How to create an interactive Excel chart [VBA]
Change chart series by clicking on data [VBA]
Change chart data range using a Drop Down List [VBA]
How to create a dynamic chart
Animate
Line chart Excel Bar Chart Excel chartAdvanced charts
Custom data labels in a chartHow to improve your Excel Chart
Label line chart series
How to position month and year between chart tick marks
How to add horizontal line to chart
Add pictures to a chart axis
How to color chart bars based on their values
Excel chart problem: Hard to read series values
Build a stock chart with two series
Change chart axis range programmatically
Change column/bar color in charts
Hide specific columns programmatically
Dynamic stock chart
How to replace columns with pictures in a column chart
Color chart columns based on cell color
Heat map using pictures
Dynamic Gantt charts
Stock charts
Build a stock chart with two seriesDynamic stock chart
Change chart axis range programmatically
How to create a stock chart
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