This post describes how to add a map (background picture) to a chart. The following animated picture shows a scatter chart with a background picture. A drop down list let´s you select a city and the chart displays the location.

map in a chart6

Step 1 - Insert a scatter chart

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

insert scatter chart

Step 2 - Create 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

create a drop down list

Formula in cell C14:

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

Formula in cell D14:

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

Step 3 - Select chart data source

  1. Right click on chart
  2. Click "Select Data"
  3. Click "Add" button
    select data source
  4. Select name, x value and y value.
    edit chart series
  5. Click Ok.

scatter chart

Step 4 - Insert a background picture

  1. Right click on 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"

Format plot area

Step 5 - Chart settings

  1. Delete Legend and chart titel
    chart title and legend
  2. Delete chart grid lines
    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
    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. Right click on data series
  8. Click "Format Data Series..."
    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.

Format data label

Download excel *.xlsx file

Maps in a xy scatter chart.xlsx

Functions in this post

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value