Finally my Excel classes in USA are over. It was a lot of fun traveling to new cities, teaching Excel & dashboards to enthusiastic crowds and making new friends. As if that is not fun enough, we (Jo, kids & I) are going on a 2,000 mile, 2 week road trip starting today.
Although I am enjoying all this, I also feel bad for not taking enough time to share new tricks, ideas & techniques with you here. So, I have a wacky, wild & awesome plan for you. Join us on our road trip.
That is right. You can join me on our road trip and see what I see, learn some pretty cool Excel tricks, all while sipping coffee and stretching legs in the comfort of your office cubicle. No oppressive summer heat, no driving thru a long turn-pike looking for next rest area while your daughter is screaming in the back; ‘daddy, I need to go now.’ or no drinking three coffees in a row so that you can access free wi-fi and check your email.
So buckle up. Err, I mean unbuckle and join us on this road trip.
What is going to happen?
Simple. While I am on road, each day I will be posting a new Excel tip or article. I will accompany it with a short snippet of what we are doing, include a photo or two of the beautiful things we are seeing.
Sounds Exciting, What should I do?
Nothing really. Just follow our journey for next 2 weeks. May be tell your colleagues or friends about it so that they too can enjoy. And if you happen to be in Raleigh or Pittsburgh or DC, ping me. We may be able to catch up.
So where are we going?
Well, I could tell you all about our Road trip using a map or several bullet points. Heck, I could even use a fancy image to tell you what we are up to. But you know that is not my style. So I have the right thing for you. So I present to you…,
Interactive Map of our Road Trip
That is right. First take a look at our road trip details:
How is this chart / map / interactive thingie made?
I wish I can sit here and type out all the little details about this. But I must rush now and pick up our rental car. So here is the recipe in a nutshell.
- First I created a Google Map with all the locations we are visiting. (link)
- Took a screenshot of the map. Embedded it in to Excel and cropped it.
- Assuming the top left of the map is (0,0) and bottom right is (2.2, 3.2) figured out the x,y co-ordinates for all the markers by trial and error. (Hint: apply grid lines at 0.1 so that you can easily find marker positions).
- Then created an agenda table with below structure.
- Added a scrollbar form control and set it from 1 to 16. Linked it to a blank cell. (related: Introduction to form controls)
- Using scrollbar selection, fetched corresponding row details from above table.
- Created an XY scatter plot with (x1,y1) and (x2,y2) values for selected row.
- Set cropped map image behind the chart’s plot area and resized things until they align ok.
- Using text boxes, fetched various details for selected scrollbar value (date, heading, details, Excel tip).
- Color and format so that everything looks good.
So there you go. Now, you know how to create your own map / chart / interactive thingie.
Download Road Trip Interactive chart
Click here to download the workbook and play with it. Examine the way chart is set up (ungroup it to move things) and various INDEX formulas to understand this better.
More on Maps & Excel
If you like to combine maps & data, then Excel is not going you help you much (unless you are using either Power View or Geo Flow). But there are a few ways to get maps in Excel. Check out below examples to learn more.
- Lithuania at a glance (Choropleth maps in Excel)
- Count-down timer on a map
- Visualizing Olympic medals by country
- Tracking Hurricane Sandy in Excel
So see you tomorrow, from Waynesboro (VA). Until then, stay in your lane and cruise.