Author: Oscar Cronquist Article last updated on January 31, 2019

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

 

Related articles

Dynamic data labels in a chart

My fascination for charts and vba continues (see previous posts). This post demonstrates how to highlight a group of values […]

Change chart data range using a Drop Down List [VBA]

In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

Quickly change chart data source

In excel it is not easy to change a chart´s data source without manually changing the data source in chart […]

Heat map using pictures

I made a heat map calendar a few months ago and it inspired me to do this post. The heat […]

How to add horizontal line to chart

This tutorial shows you how to add a horizontal/vertical line to a chart. Excel allows you to combine two types of […]

How to graph an equation

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 […]