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.
15 Responses to “Are you ready for 2,000 miles, 15 days & 10 Excel tips road trip?”
Enjoy the road trip! If it isn't already in your itinerary, consider stopping in Hershey, PA between Philly and Pittsburgh to tour the chocolate factory.
Safe travels!
I read John Walkenbach's excel books on vba,besides others. when i first time chanced upon your excel teaching on you tube, by your voice i wonder whether it could be some one from india. to my more surprise i got know that you are from Sujathanagar. I lived in Papayyarajupalem. though i came to hyderabad on transfer i have still my own house.
i am extremely proud to know that some one my neighbor has got MVP status from Miscrosoft. whenver i come to vizag i would love to meet you
Came through Pittsburgh but didn't offer us any classes in town? Shame! We'll be looking for you next time!!
Hey Chandoo, "Work with Fun" is what you mean seriously. I appreciate greatly, but limit your travel day to just one tip and rest with family for more fun..Have a safe and happy travel ahead!
Hey Chandoo
Enjoy the road trip!
Based on your itinerary, I'd suggest investing a little time to stop by Colonial Williamsburg near Richmond, Virginia. It's amazing to walk back in time to the colonial days, where many people are dressed and speak as if they lived in the 1700's. The best part is that the town walk-through is free. (That's "$-" using the Accounting format.) It's definitely worth a couple-hour detour from your drive from Virginia Beach to D.C. Your friends in Raleigh could give you more details.
So awesome that you are coming to Raleigh! Your little ones would love Marbles, which is a kid's museum in downtown Raleigh.
Enjoy your trip! If you need any Virginia Beach suggestions, I'm a local. You seemed to capture all the beach elements.
Wish you came to Charlotte. We are the largest city in NC and have a lot of folks who use Excel in this financial-rich town!
Hey Chandoo, Enjoy the trip......
Have fun
Excel Test Question Paper needed
Hi,
I need to prepare a MS Excel 2007 Test paper on the following things.
1. Vlookup
2. Pivot Table
3. Conditional Formatting
4. Filters
5. Sorting
6. General formulaes such as sum, average, countif etc
7.Validations
I need 2 questions for each point mentioned above. I have some knowledge of excel but preparing a question paper is very dificult for me. Please help me.
Excel Test Question Paper needed
Hi,
I need to prepare a MS Excel 2007 Test paper on the following things.
1. Vlookup
2. Pivot Table
3. Conditional Formatting
4. Filters
5. Sorting
6. General formulaes such as sum, average, countif etc
7.Validations
I need 2 questions for each point mentioned above. I have some knowledge of excel but preparing a question paper is very dificult for me. Please help me.
My mail ID :- nnetaji2007@gmail.com
How you create that working images which you demonstrate?. Please Rplly
@Amey
Refer: http://chandoo.org/wp/about/what-we-use/