Today, let’s learn how to make an impressive map chart with Excel. Something like this:
Step 1: Set up your Data for the Map Chart
You need geographical fields (district / county/ state or country), few numeric values to create the map chart in Excel. Here is the layout of the data I’ve used.
I will explain the logic for “Calc. Column” in Step 3.
Step 2: Create “selection mechanism” with Data Validation
Select a blank cell to set up “drop-down” selection mechanism. This is where we will select one of the products to show on the map.
Go to Data ribbon & click on “Data Validation”. Change the rule type to “List” and point to the list of products. You can set up this list elsewhere in the spreadsheet or directly point to the table headers.
Related: Learn more about setting up “drop-down lists in Excel”
Step 3: Calculate the values for “selected” product
I recommend using either XLOOKUP or INDEX MATCH to calculate the values of “selected” product for our table.
Here is the XLOOKUP formula I’ve used. This assumes the drop-down list selection is in cell G6.
=XLOOKUP($G$6,
Table2[[#Headers],[Mint Chip Choco]:[After Nines]],
Table2[@[Mint Chip Choco]:[After Nines]])The above formula looks up the G6 (selection values) against table headers and returns the corresponding product sales values to the calculated column.
Step 4: Make the Map chart
Select the geographical fields & the calculated columns alone, then go to Insert Ribbon & click on the Filled Map chart option (see below).
Customize the map chart colors & title if you want.
Place chart next to the selection cell and you will have a dynamic map chart in Excel, like below:
Download the sample file
If you need a hand with this technique, check out the example workbook.
Video Tutorial – How to make map charts in Excel?
I made a video explaining this process with a few more examples & tips. Check out the video below or my YouTube channel.
Known issues & problems with Maps in Excel
Excel map charts are not perfect. Here are some known issues with it.
- Not all geographical data is supported: In many countries, you can’t show data by district or city or regions. For example, if you try to make the same chart with districts of an Indian state, the map will not work.
- No support for LAT / LONG or street addresses: You can’t create map charts with specific point information (like addresses or coordinates).
- Incorrect maps: When a geographical boundary is disputed (due to wars or whatever), the map chart doesn’t show correct outlines or altogether omits the regions.
- Unable to zoom: You can’t zoom in to a specific region with the map charts.
Possible Alternatives:
You can use Power BI or Tableau other data visualization tools to make the maps. They allow working with more granular data or can let you control the zoom for your maps.
Got a question?
Do you have a question or doubt about using map charts in Excel? Leave a comment so I can help.
6 Responses to “Make VBA String Comparisons Case In-sensitive [Quick Tip]”
Another way to test if Target.Value equal a string constant without regard to letter casing is to use the StrCmp function...
If StrComp("yes", Target.Value, vbTextCompare) = 0 Then
' Do something
End If
That's a cool way to compare. i just converted my values to strings and used the above code to compare. worked nicely
Thanks!
In case that option just needs to be used for a single comparison, you could use
If InStr(1, "yes", Target.Value, vbTextCompare) Then
'do something
End If
as well.
Nice tip, thanks! I never even thought to think there might be an easier way.
Regarding Chronology of VB in general, the Option Compare pragma appears at the very beginning of VB, way before classes and objects arrive (with VB6 - around 2000).
Today StrComp() and InStr() function offers a more local way to compare, fully object, thus more consistent with object programming (even if VB is still interpreted).
My only question here is : "what if you want to binary compare locally with re-entering functions or concurrency (with events) ?". This will lead to a real nightmare and probably a big nasty mess to debug.
By the way, congrats for you Millions/month visits 🙂
This is nice article.
I used these examples to help my understanding. Even Instr is similar to Find but it can be case sensitive and also case insensitive.
Hope the examples below help.
Public Sub CaseSensitive2()
If InStr(1, "Look in this string", "look", vbBinaryCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub CaseSensitive()
If InStr("Look in this string", "look") = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub NotCaseSensitive()
'doing alot of case insensitive searching and whatnot, you can put Option Compare Text
If InStr(1, "Look in this string", "look", vbTextCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub