6 Easy Steps to Making an Interactive Map Chart in Excel




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Creating interactive map charts in excel is one of the easiest things to do…..that is if you know how. I recently created this map chart showing the top 50 tropical storms to hit the Unitef States since 1900. As you move ahead in time from 1900 to 2005, the map shows the states that were hit by the storm, the damage caused, storm category and rank. It takes about 5 lines of code per map unit (in this case a state) to render this effect.

You can download the file here.




Steps to create this interactive chart map in excel:
1. Get relevent data and lay it out clearly.



2. Prepare a placeholder for the map and the information points (Dates, Names, $’s etc.)



3. Introduce a dynamic control (in this case a horizontal scrollbar) that the user can use to interact with the graphic.























4. Insert formulae and charts so that when the user moves the dynamic conrtol, values get updated.



5. Inroduce the map chart (in this case the WMF map of the U.S.)



6. Write VBA code to make the relevent map objects behave in accordance with the user inputs.




(Data taken from Report by Pielke, Gratz, Landsea, Collins, Saunders and Musulin )

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. ralph wrote:

    is there a step by step interactive map charts

  2. BeGraphic wrote:

    To go quicker, use the free tool BeGraphic.com

    BeGraphic is an advanced data visualization add-in working inside Excel and PowerPoint. Its free version has dynamic maps and gauges.

    10 000 additional maps are given away to business users (if they ask them using their pro email).

  3. Ashish Chauhan Youngy wrote:

    I have edited your code to make it shorter and eisier (And a little bit efficient too):

    Private Sub ScrollBar1_Change()
    Application.ScreenUpdating = False
    On Error Resume Next
    ActiveSheet.Shapes(i1).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52

    ActiveSheet.Shapes(i2).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52

    ActiveSheet.Shapes(i3).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52

    ActiveSheet.Shapes(i4).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52

    ActiveSheet.Shapes(i5).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52

    On Error Resume Next
    i1 = Application.WorksheetFunction.VLookup(Cells(27, 17).Value, Sheet2.Range(“stormlist”), 7, False)
    ActiveSheet.Shapes(i1).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45

    i2 = Application.WorksheetFunction.VLookup(Cells(27, 17).Value, Sheet2.Range(“stormlist”), 8, False)
    ActiveSheet.Shapes(i2).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45

    i3 = Application.WorksheetFunction.VLookup(Cells(27, 17).Value, Sheet2.Range(“stormlist”), 9, False)
    ActiveSheet.Shapes(i3).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45

    i4 = Application.WorksheetFunction.VLookup(Cells(27, 17).Value, Sheet2.Range(“stormlist”), 10, False)
    ActiveSheet.Shapes(i4).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45

    i5 = Application.WorksheetFunction.VLookup(Cells(27, 17).Value, Sheet2.Range(“stormlist”), 11, False)
    ActiveSheet.Shapes(i5).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45

    Application.ScreenUpdating = True
    Cells(27, 18).Select

    End Sub

  4. Someone wrote:

    This is cool… But where is live example ?

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel