Interactive Excel Chart with Map

Interactive Excel Chart with Map

Today I’d like to share with you a video tutorial by Isaac Gottlieb on supply-chain decision making from his book Next Generation Excel.

Isaac teaches over 2,000 MBA students a year at Columbia, NYU and Temple U and over 3,000 undergrads on-line.

See how he uses the Solver to find out the ideal location for a manufacturing plant. He then takes the output and plots it on a scatter chart, adds some scroll bars and a sprinkling of magic dust and makes it do this (watch the red marker on the map):

Excel Solver

Here is the video:

Now I know it’s not every day you need to calculate the ideal location of a manufacturing plant, but take a few minutes to think about how you could use one or more of the tips in this video and apply it to your work.

Download the Excel workbook. Note: this is a .xlsx file, please ensure your browser doesn’t change the file extension on download.

If you’d like to learn how to incorporate interactive features like this in your Excel reports and charts check out my Excel Dashboard course.

Where’s the Solver?

The Solver is an add-in. If you don’t already have it on the Data tab of your ribbon you can add it by going to Excel Options (Office button or File tab) > Add-ins:

Excel Solver Add-in

Then you should have this:

Excel Solver Add-in

Where is the Scroll Bar?

On the Developer tab of the ribbbon.

Excel Solver Add-in

If you don’t have the Developer tab available you can add it:

File Tab (2010/13), or Windows button (2007) > Options > Customize Ribbon; check the Developer box under Main Tabs in the right selection pane.


Thank you Isaac for allowing me to share this tutorial.

If you liked this please share it with your friends and colleagues on LinkedIn, Google +1, Facebook or Twitter, or leave a comment below.

Leave a Reply

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

Current ye@r *


  1. Glenn Case says

    I really enjoyed this!

    I have a couple suggestions which might address issues some people may have with this:

    1) You can easily stretch the chart to match the dimensions of the original map, so it doesn’t look skewed.

    2) I was unable to figure out how to reorient the vertical slider control so that moving up resulted in a higher number; it always seems to work backwards. So I tied the slider to another cell, J9, and changed cell D9 to be =1600-J9. Now the motion is much more intuitive.

    3) You can adjust the size of the slider controls to match the graph dimensions, which also makes it easier to use. If you adjust the slider so that the max and min positions match the edges of the map, in combination with item 2 above, then moving the slide positions the red dot in the same horizontal/vertical position as the slider bars in the controls.

  2. Darryl says

    Great Help from you many thanks to you. Also our co in kSA is going to start E-commerce courses on MS office..can you assit me on anything like this if possible…best regd

    • Mynda Treacy says

      Cheers, Darryl. Glad you liked Isaac’s video.

      Please contact me at website @ about the E-commerce courses as I’m not sure what you mean.

      Kind regards,


  3. Yaa says

    Hi, do you know how to use the Index function in Google spreadsheets? I used it on an Excel spreadsheet that I needed to share with others, bu when I upload to Google Drive, the index function doesn’t work. This is the function I have: =INDEX($G$10:$CQ$10,1), =INDEX($G$10:$CQ$10,9), and so on. Increasing the digit one by increments of 8. Don’t know if that makes sense :/

    • Mynda Treacy says

      Hi Yaa,

      Your formula is missing the column number argument. It should be like this:


      See the second comma? This is a placeholder for the row argument. You don’t need a row argument in this case because your reference is only one row high so Excel knows you must be looking in that row, but you still need to put a comma in so that it knows the next argument is for the column.

      Kind regards,


  4. Brenda says

    Wow, incredible tutorial! I’ve never used solver before and this was a nice intro to using it. I didn’t understand why the weights were different; one would think they would be the same.

    • Mynda Treacy says

      Hi Brenda,

      On behalf of Isaac, thank you.

      The weights were all different as this is the amount each location requires from the plant. The materials produced by the plant aren’t evenly distributed.

      Kind regards,


  5. Nkhoma Kenneth says

    Hi Isaac,
    It is a great piece of genius thinking and a master-piece techinque and Solution.
    such people like you provide inspiration and motivation for others to also delve more into reseach and thinking to try and get close to your thinking.
    Iam delighted to see what you have done as i still try to grasp the whole idea.