Click a Map To Filter Excel Data

We’ll set up an Excel workbook so you can click on a map, and see a list of fictional doctors in the state that you selected.

MapUSA

Set up the Map

First, I found a clip art map of the continental USA, with a separate shape for each state, and pasted that into a workbook.

Next, named the shapes, using the two digit state abbreviation.

  1. Click on a state shape, e.g. Washington
  2. Click in the Name Box, and type the two digit code – WA
    MapUSA_NameWA
  3. Press the Enter key, to complete the naming.

This will be a good test of your geography knowledge and state abbreviation skills.

Set Up the List of Doctors

On a sheet named Doctors, I entered a list of doctor names, state codes and number of patients.

MapUSA_Docs

I named the range, DoctorList, using the formula:

=OFFSET(Doctors!$A$1,0,0,COUNTA(Doctors!$A:$A),COUNTA(Doctors!$1:$1))

as described in my Dynamic Named Ranges tutorial.

Set Up the Filter Criteria

On the Map sheet, I set up a criteria range for the filter, with the State heading in cell A1, and a State code in cell A2.

To name this range, I selected cells A1:A2 and typed Criteria in the Name box, then pressed the Enter key.

MapUSA_Crit

Set Up the Filter Extract

Next, I copied the headings from the Doctor list, and pasted them below the map.

With the pasted cells still selected, I named that range StateList.

This is where the list of doctors will appear when you click a state in the map.

MapUSA_Extr

Add the Filter Macro

I added a module to the workbook, and entered some code to use an Advanced Filter, to extract a list of doctors for the selected state.

Sub GetDoctorList()
On Error Resume Next
Dim strState As String
Dim sh As Shape
Dim wsMap As Worksheet
Dim wsDoctor As Worksheet
Dim wsList As Worksheet
strState = Application.Caller
Set wsMap = Worksheets("Map")
Set wsDoctor = Worksheets("Doctors")

wsMap.Range("A2").Value = strState

wsDoctor.Range("DoctorList").AdvancedFilter _
  Action:=xlFilterCopy, _
  CriteriaRange:=wsMap.Range("Criteria"), _
  CopyToRange:=wsMap.Range("StateList"), Unique:=False
wsMap.Activate
wsMap.Range("A1").Activate
End Sub

Assign the Macro to Shapes

The final step is to assign the GetDoctorList macro to each shape. You could right-click on each shape, click Assign Macro, and select the macro, to do this manually. That might take quite a while for all 48 states, so I wrote a little macro, to do the work for me. You’ll only have to run this once, while setting up the workbook.

Sub AddMacro()
Dim sh As Shape
For Each sh In Worksheets("Map").Shapes
    sh.OnAction = "GetDoctorList"
Next
End Sub

Run the Filter

Now, to run the filter, click on a state in the map.

The state code appears in cell A2, and the list of doctors is in the extract range, starting in cell B18.

MapUSA_Filter

_________________

You may also like...

11 Responses

  1. Robert says:

    Hi Debra,

    an interactive map is a nifty little feature, but there are a couple of drawbacks:

    1. A map requires considerable real estate on a dashboard. From my point of view you should use this for visualizations of data and results instead of using it for an interactive feature.

    2. The users / readers have to be familiar with the geographical location of all regions.

    3. It is difficult to click on the very small regions (e.g. Rhode Island on the US map).

    I described a similar idea here:

    http://www.clearlyandsimply.com/clearly_and_simply/2009/01/approach-with-caution.html

    Anyway, a nice article. Thanks for publishing.

  2. MnM says:

    Well, color me impressed (and purple with a streak of magenta, for all you Psych fans out there).

    I might be able to do something with this for our sales rep’s territories…

  3. MnM, thanks, and I hope your sales reps like the idea.

    Dave, I used a map from the Microsoft Clip Art Gallery. I copied it into Excel, then used the drawing tools to ungroup it.

  4. Dave says:

    Any advice on where to look for good (hopefully free) clip art maps? All the “free” sites I’ve tried so far have been less than impressive or not quite free.

  5. Robert, those are good points, and thanks for the link to your article. For a dasboard, a small regional map, with labelled regions might work, but a large map with some tiny sections would be a problem.
    I like your idea of combining the map with a drop down list. In the original version of this, posted on contextures.com about 8 years ago, I only had two states, and the code coloured the state that had been selected. A variation on this could colour the state if selected from the drop down list, or clicked.

  6. Steve says:

    Howdie all.
    I’ve tried posting my question about filters on the newsgroups, and other forums, and I either don’t state it correctly, or no one can answer it.

    Howdee all.
    Well, it appears my last post never got any response, so I’ll modify it to see if that helps. I did however figure out that particular issue a couple of weeks later.

    I’m using autofilter to compare data on two worksheets.
    I’ve found that there are instances where my filter will jump to text filter if it cannot find the exact text being sought from my criteria option.
    In general this causes the issue of the macro not accomplishing my goal of comparing numeric values based on other criteria. As such, I’ve now decided that I’d like to modify the macro to do a more generalized comparison– using: if …. like…. then.

    Please see my post from March 5, 2009 for the code.
    Autofilter, multiselect, 2007

    My goal is to have the filter look at a column’s values, based on a worksheet’s name (as my criteria). If the value in a given cell is like the sheet’s name, then I want that to be my filtered value.
    Presently, Autofilter will only select the exact match for that value, and if it cannot locate the exact value, it will jump to text filter.
    Here is the present configuration that I do have working.

    [code]
    Sub FilterSteveA()
    Dim fname As String
    Dim wks As Worksheet
    ‘ok, this macro is still not working correctly.
    ‘for some reason that I’m unable to identify it selects a text filter instead of
    ‘the name in the list below text filter.
    ‘One thing that comes to mind is that since I’m using the name as a filter criteria,
    ‘ the names are in fact different– because what the permit database can handle differs from
    ‘ what the print outs from the excel spreadsheets can handle.
    ‘Something to consider…….
    fname = ActiveSheet.Name
    fname = Right(fname, 3)

    mv = Range(“f2”).End(xlDown).value ‘ this sets the criteria for the ChgAppl#.

    mv1 = Range(“a2”).End(xlDown).value ‘this is my add-on to set a second criteria filter- Name of owner.
    ‘mv2 = fname

    For Each wks In ActiveWorkbook.Worksheets
    If LCase(Left(wks.Name, 3)) Like “sum” Then

    With wks

    Select Case fname
    Case “76a”, “187”, “718”

    Sheets(wks.Name).Range(“A15:g15”).AutoFilter field:=2, Criteria1:=fname ‘this is to take into account the claim number
    End Select

    Sheets(wks.Name).Range(“A15:g15”).AutoFilter field:=1, Criteria1:=mv ‘, this takes in to acct the chg appl# for a filter.

    Sheets(wks.Name).Range(“A15:g15”).AutoFilter field:=4, Criteria1:=mv1 ‘, ‘this takes in to account the owner name for a filter.

    End With
    End If

    Next wks

    End Sub

    [/code]

    As you can see, the criteria will set a filter and look at specified columns. Then it looks at the criteria, and looks that up on my “sum” sheet. If there’s not an exact match, it sets the text filter.
    I’d like to use the if …. like….. then to check against.

    Is there a way to do this?
    Thank you.

  7. Martin says:

    Hi Debra, I’m new at this forum, and must say I’m quite impressed !!!

    I was delighted by this article, and wanted to use it in a similar way, but with a twist: i wanted to display something within every shape, for instance, revenue. But I was not able to find the right property, if possible.

    I played around with .textframe.characters.text, but the text still doesn’t appear.

    Any suggestions?

    Kind Regards,

    Martin

  8. Robert says:

    Martin,

    in a comment on a post called “Choropleth Map Template USA by Counties”, Harold points to a map of Australia displaying the exact data in textboxes.

    You will find his comment here:

    http://www.clearlyandsimply.com/clearly_and_simply/2009/08/choropleth-map-template-usa-by-counties.html

    and his workbooks for free download here:

    http://www.xlnumerics.com/images/stories/HG-XLN/Australia-by-state.xls

    I hope this will be helpful.

  9. Martin says:

    Thanks Robert for your comment. I just downloaded the file and it looks like what I need. I’ll let you know my advances then.

  10. StephenSibert says:

    Thank you for your article.

  11. Contextures Blog » Excel Advanced Filter Update says:

    […] Click a Map To Filter Excel Data – set up an Excel workbook so you can click on a map of the USA, and see a list of fictional doctors in the state that you selected. […]

Leave a Reply to MnM Cancel reply

Your email address will not be published. Required fields are marked *