Author: Oscar Cronquist Article last updated on March 02, 2020

Maps in an x y scatter chart

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

insert scatter chart

The following steps describe how to place a scatter chart on a worksheet.

  1. Go to "Insert" tab on the ribbon.
  2. Press with left mouse button on "Scatter" button.
  3. Press with left mouse button on "Scatter with only markers" button.

A blank chart shows up on the screen.

How to build an interactive map in Excel scatter chart

You can press and hold with the left mouse button on the chart and then drag to the desired location.

How to build an interactive map in Excel size handles

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

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. Press with left mouse button on "Data Validation" button.
  4. Go to "Settings" tab.
  5. Select "List".
  6. Select source range: B3:B10.
  7. Press with left mouse button on OK.

Formulas

How to build an interactive map in Excel formulas 1

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:

=INDEX(C3:C10, MATCH($B$14, $B$3:$B$10, 0))

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:

=INDEX(D3:D10,MATCH($B$14,$B$3:$B$10,0))

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

How to build an interactive map in Excel 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. Press with right mouse button on on the chart.
  2. Press with left mouse button on "Select Data" from the menu.
  3. Press with left mouse button on "Add" button.
    select data source
  4. Select a name, an x value, and a y value.
    edit chart series
  5. Press with left mouse button on Ok.

How to build an interactive map in Excel chart data source1

Insert a background picture

How to build an interactive map in Excel background image

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. Press with right mouse button on on the empty chart.
  2. Press with left mouse button on "Format Plot Area...".
  3. Press with left mouse button on "Fill".
  4. Select "Picture or texture fill".
  5. Press with left mouse button on "File..." button.
  6. Select a picture.
    Format plot area
  7. Press with left mouse button on "Insert".
  8. If you like, change "Transparency" value.
  9. 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.

How to build an interactive map in Excel chart aspect ratio

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

How to build an interactive map in Excel background image

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

Final notes

map in a chart6

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.

Format data label

  1. Press with right mouse button on on the marker.
  2. Press with mouse on "Add Data Labels".
  3. Double press with left mouse button on the data label next to the marker to open the Format pane.
  4. Select "Series Name" and deselect the remaining check boxes.

How to build an interactive map in Excel add data label

Recommended links