Click a Map To Filter Excel Data

In the steps below, I’ll show how to 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. There’s also an Excel sample file that you can download, and try this map technique for yourself!

Click state on map to get list of fictional doctors
Click state on map to get list of fictional doctors

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, I named the shapes, using the two digit state abbreviation.

  • Click on a state shape, e.g. Washington
  • Click in the Name Box, and type the two digit code – WA
Map of USA with state shapes
Map of USA with state shapes
  • 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 fictional doctor names, state codes and number of patients.

list of fictional doctor names
list of fictional doctor names

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.

Use an Excel Advanced Filter

To get a list of doctor names for a specific state, I’ll use an advanced filter, and a macro to run the advanced filter.

To use an advanced filter, I’ll set up two ranges in the workbook:

  • 1) Filter Criteria Range
  • 2) Filter Extract Range

Those two steps are explained in the sections below.

1) 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.

type Criteria in Name box
type Criteria in Name box

2) Set Up the Filter Extract

Next, to create an extract range, 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 fictional doctors will appear, after you click a state in the map.

create an extract range
create an extract range

Add the Filter Macro

To “magically” create the list when the map is clicked, I needed an Excel macro.

So, I added a module to the workbook, and entered some VBA code, shown below.

The macro. named GetDoctorList, uses 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 State shape in the USA map.

You could do this manually, if there were just a few shapes:

  • Right-click on each shape
  • Click the Assign Macro command
  • Select the GetDoctorList macro
  • Click OK

Macro to Assign Macros

It might take quite a while to assign the GetDoctorList macro for all 48 states!

So, I wrote another macro, to do the work for me.

You’ll only have to run this macro once, while setting up the workbook.

The macro is only a few lines long, and it assigns the GetDoctorList macro to each shape on the worksheet named Map.

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, you can click on a state in the map.

When the GetDoctorList macro runs, it does the following steps:

  • First, it puts the state code in cell A2
  • Next, the macro runs an advanced filter, which puts the list of doctors in the extract range, starting in cell B18.

The criteria range, and the extracted list are shown in the screen shot below.

The state shape for California (CA) was clicked, and the filtered list shows the fictional doctors from California.

click on a state in the map
click on a state in the map

Get the Sample File

To get the Excel sample file with the USA map and list of fictional doctors, go to the Excel Filter Sample Files page on my Contextures site.

  • On that page, go to the section named: Filter Files FL0011 – FL0020
  • In that section, look for the sample file named FL0012 – Map Based Filter, and click its download link.
  • The zipped Excel contains macros, so be sure to enable those, if you want to test the map filter macros.

More Excel Filter Examples

Excel FILTER function

Advanced Filter Basics

Advanced Filter Criteria

Advanced Filter Macros

Advanced Filter Criteria Slicers

_________________

0 thoughts on “Click a Map To Filter Excel Data”

  1. 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. 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. 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. 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. 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. 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.

  9. […] 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 Contextures Blog » Excel Calorie Burning Calculator Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.