Simple Excel Dynamic Map Chart with Drop-down

...and conditionally formats the largest value on the chart

Learn how to create a simple but impressive dynamic map chart that enables you to visualize specific KPI’s by geography.

This method doesn’t use the relatively new Bing map feature, which means it is works on all Excel versions. The technique shown uses a scatter plot first to set up the respective points on the map and then transforms it into a bubble chart to be able to visualize the actual KPI.

I also show you how you can conditionally format specific data points to bring attention to certain categories, i.e. how to conditionally format the largest data point in the chart in a different color than the rest.

Watch the steps in this video:

Feel free to Download the Workbook HERE.

Free Excel Download

The new Excel Dashboards course is here!

Now available on Udemy

Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.

GET 75% OFF THE COURSE
Free Ebook

8 Comments

  1. Wes Wilcox January 31, 2018 at 11:29 pm - Reply

    WOW!!!

  2. Grevall February 15, 2018 at 5:29 pm - Reply

    Nice

  3. christelle March 11, 2018 at 9:50 pm - Reply

    exactly what I’m looking for!!! thanks a lot !

    • Leila Gharani March 13, 2018 at 12:41 am - Reply

      Glad this helped!

  4. Raymond March 29, 2018 at 6:44 pm - Reply

    oh I just send you an email about this. The advantage of Bing maps is that you can see the whole area of a country and you don’t have to create scatter plot. But a very big disadvantage is you need to accept that your data is send to Bings. I can not do that. So I used your idea, but I was wondering do you also have a solution to fill the whole country instead of only a dot? And use colors within the country based on values

    • Leila Gharani April 3, 2018 at 8:57 pm - Reply

      Agree – and also a lot of people are still on the old version of Excel. Regarding the other solutions. Yes, there are ways. It’s more complex though and requires separate drawings for each country…I’ll add this to my tutorial list.

  5. Dan G July 12, 2018 at 6:11 pm - Reply

    Hi Leila! This is by far my favorite Excel hack ever! Thank you so much!

    Btw, I agree with Raymond. I tried using Bing Maps with this hack, and the loss of control over the map (color and size of items) was too detrimental compared to the benefit of saving a few minutes, one time, to set up the scatter chart.

  6. Dan G July 12, 2018 at 6:15 pm - Reply

    Hi Leila.

    Now that I’ve got the map completed and the KPIs populating the map correctly, I would like to show the execs how we (they) can use this to save time while promoting conversations around the most important data points.

    Do you have any suggestions or lessons about embedding this in a PPT as part of a presentation? I can see our execs and managers doing just that if there is a way to do it w/out losing the dynamic functionality.

    Any advice or help would be much appreciated!

Leave A Comment

Share This