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.

1. Go to "Insert" tab on the ribbon.
2. Click "Scatter" button.
3. 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.

1. Select cell B14.
2. Go to tab "Data" on the ribbon.
3. Click "Data Validation" button.
4. Go to "Settings" tab.
5. Select "List".
6. Select source range: B3:B10.
7. 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.

1. Right click on the chart.
2. Click "Select Data" from the menu.
3. Click "Add" button.
   
4. Select a name, an x value, and a y value.
   
5. 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.

1. Right-click on the empty chart.
2. Click "Format Plot Area...".
3. Click "Fill".
4. Select "Picture or texture fill".
5. Click "File..." button.
6. Select a picture.
   
7. Click "Insert".
8. If you like, change "Transparency" value.
9. 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.

1. 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.
   
   
2. Delete chart gridlines. You do that by clicking on the to select them and then press Delete.
   
3. Change x and y axis minimum and maximum value to 0 and 100.
   
4. Make sure x and y coordinates in the table are ok. If not, make appropriate adjustments.
   
5. If you like, delete x and y axis.
   
6. Select data series on the chart.
   
7. Right click on data series.
8. Click "Format Data Series...".
   
9. Click "Marker Options".
10. Select "Built-in".
11. Select a type.
12. Click "Marker Fill".
13. Select "Solid fill".
14. Pick a color.
15. 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.

1. Right-click on the marker.
2. Click on "Add Data Labels".
3. Double click on the data label next to the marker to open the Format pane.
4. 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?