Creating a map in excel and inputting a number to make a dot appear?

[fmoaveni]

Hi there,
I am using excel 2013 and I am wondering if there is a way to make a dot (location of a building) appear on a picture of a map I inserted into excel if I type a specific number in a cell range.

For example:

Range A1:A120 would be the numbers for the buildings (A1:1234, A2:1235, A3:1236, A100:12345, etc.) If I use a data filter and select cells that contain specific building numbers, I would like to be able to have the locations for the buildings show up on the picture of the map.

Is there a way to format the map so that I can make a small point or circle appear where each building is geographically located? And then, correspond the building number in cells A1:A120 to these points on the map?

That way if I delete any of the cells from the range A1:A120 the dots will also disappear on the map?

If you need more information or if this is not clear please let me know. Thank you!

 

[elmer007]

I think you can make this happen, but you will need to put some more data in your sheet. Use a scatter plot chart (the normal version- top left in the list of scatter charts); right-click on the chart and format the plot area and set the fill to be a picture (your map). In columns B and C (or you can make another copy of your data in columns farther over if you need to), you need to put X,Y coordinates for the buildings such that they appear in the correct places on your map. Also, remove the axis labels and any horizontal or vertical lines.

I would recommend going to Layout>Axes>More Options (for both horizontal and vertical) and changing the maximum values to something large like 100 so that you will have the level of detail you need in order to not use a ton of decimals to get the dots to appear in the right places.

This is not the quickest process ever, but I think it's what you're looking for.

 

[RickXL]

Or you could use shapes and some VBA.

The "map" is an imported shape that has to be renamed "myMap" using the box near the top left hand corner of the Excel window.

Then you need to add names and locations in a list. The locations are percentages of the way across and down the map.
You can filter the points if desired.

The code below needs to be placed into a standard macro Module.

Sub myMap()
    Const Dia   As Single = 10
    Dim sh      As Shape
    Dim c       As Range
    Dim L       As Single
    Dim T       As Single
    Dim W       As Single
    Dim H       As Single
    
    With ActiveSheet
        For Each sh In .Shapes
            If sh.Name <> "myMap" Then sh.Delete
        Next
        For Each c In .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).SpecialCells(xlVisible)
            If c.Offset(0, 1) <> "" Then
                With .Shapes("myMap")
                    L = c.Offset(0, 1) * (.Width - Dia) / 100 + .Left
                    T = c.Offset(0, 2) * (.Height - Dia) / 100 + .Top
                End With
                With .Shapes.AddShape(msoShapeOval, L, T, Dia, Dia)
                    .Fill.Visible = msoFalse
                    .Line.ForeColor.RGB = RGB(255, 0, 0)
                    .Line.Weight = 3
                End With
            End If
        Next
    End With
End Sub

It copes fairly well with moving and re-sizing the image. You will need to re-run the macro each time, though.
(The image was picked at random from the internet.)

 

[fmoaveni]

Awesome! Thank you both so much for the help! I actually tried a different route which seemed to be a bit less complicated for me and a little more accurate. I found the GPS coordinates using good and created a longitude and latitude column for each building. Then I used the powerview map option and was able to see the exact coordinates and sort through the stores. I will try this macro later though. I have a feeling I will need to use it soon with another project I am working on. Thank you!