Ok people. Let me tell you that this post is almost not about Excel. It is about how one Excel blogger’s (yours truly) dream of long distance cycling came true. So sit back, grab your favorite drink and read between sips.
PS: if you just want to see the dashboard, click here.
So what is this all about?
Last Sunday (27th July) & Monday (28th), I finished my first ever 200KM bicycle ride. I rode for a little more than 12 hours, burned 5,179 calories & rode 206 kilometers.
It is definitely one of the most memorable, tiresome & uplifting experiences in my life. So naturally, I want to share the story with you.
Where it all began
Around late last year, I read a book called Free Country by George Mahood. It is a story of two young British lads cycling from Lands End to John O’ Groats (from one end of Britain to another) without spending a penny of their own money. While reading the book, I kept thinking, ‘I should do something mildly similar one day’.
But I did not do much cycling in the next several months. Until…,
I read truly inspiring story of how adc cycled from Manali to Leh (500 km). The story is special because the terrain between Manali & Leh is filled with high altitude passes, dangerous mountain roads, harsh weather, almost no habitat & breath taking scenery.
That travelogue motivated me enough to get out my dusty cycle from garage and start riding it.
Soon I was riding 20 KM per day. I started doing a few long rides (~40km) in the weekends to build strength.
Once I felt confident, I discussed my plans for a longer ride with Jo (my wife). She was a little apprehensive as I have never done something like that and long rides could mean increased chance for injury or accident.
Finally we agreed that doing a round trip to Annavaram (a small temple town 118 km away from our home) would be reasonable. The roads to Annavaram are in excellent condition. There is ample help available all the way (plenty of food stops, villages & towns along the way). Plus in case of fatigue or injury, I could easily hitch a ride back home.
I fixed on the dates 27 & 28th of July for ride. Weather app on Jo’s iPad said there is 70% chance of rain. I kept my plans fluid & wanted to ride only if there is no rain in the morning when I started.
Route Map
Here is the route I took (link).
Day 1 – Vizag to Annavaram (118 km, 7:34 hrs riding)
I woke up at 4:45 AM and unlocked the doors immediately to see if there is any rain. No rain and the skies looked ok. I got ready, had a glass of milk & woke up Jo. She gave me a farewell hug and I left the house at 5:24.
The initial 20 km was a breeze to ride. The route so far is familiar as I bicycle on it almost everyday.
After riding 25 km, I took a short water break.
I barely rode for another 4 km and stopped again, this time to enjoy the view from top of a flyover. The rolling hills, green rice fields, railway track, chirping birds & fresh morning air provided perfect setting for a few pictures.
Soon I left the state highway & joined national highway 45 (and 16 too). This is a 4 lane highway with wide shoulder. Unlike in US (and many other western countries), in India bicycles, pedestrians and many other forms of moving objects are allowed on highways. The best part of riding on national highway is that the gradients are better. Next 15 km felt like one long flat ride.
I crossed Anakapalli (a small town on the outskirts of Vizag) and stopped for breakfast at 41 KM mark. 1/3rd distance completed!
After feasting on Idly, Dosa & a tea (traditional breakfast in south India), I resumed the journey at 8:10 AM.
Still no rains. So I rode on.
At around 50 KM mark, stopped for a quick drink of water & electral (oral re-hydration solution made with essential salts & minerals).
Around 60 km mark I started feeling a little thirsty and wanted to stop for some tea. Soon (after 6km) found a road side tea stall. My original plan is to ride until 80k mark and stop for lunch.
But seeing that I finished 66km by 9:56AM, I have decided to ride until Annavaram (my destination for the day) before lunch.
After the tea stop, I rode for another hour to reach Nakkapalli (a small village on the highway). There I saw a fruit juice stall. Immediately stopped to have a full glass of banana juice. The shop owner told me that Annavaram is another 36 km from there. It started drizzling now. I am a little worried that it may rain heavily.
But my fears were unfounded. Soon the skies cleared and I was on a climb. After riding just another 10 km, I had to stop again to quench my thirst and relax a bit. As I was sitting by the road side, a mini truck stopped next to me. the truck driver was curious to know where I am heading & my story. After chatting for a while they left me.
At 95KM mark, I stopped again for a quick snack (snickers bar). Soon 2 kids on a cycle approached me. They are Siva & Siva Ganesh. Siva Ganesh (eldest one, studying 9th class) wanted to test ride my cycle. He was curious to know how the gears work. He took the cycle for a short spin and returned. They spend another 5 minutes with me asking several questions.
Once again I found myself on a steady climb. And then there are strong head winds. My speeds dropped to the range of 10-15kmph. After a lot of pedaling & sweat, I crossed Tuni at 12:30PM.
Selfie @ 100km mark
It took me another 30 minutes & 6 km of biking to reach a small temple village called Lova Gudi. By now my water bottles are emptied. So I stopped here to purchase a bottle of water. From here Annavaram (my destination) is 15 km. Normally I would have finished this distance in 40-50 mins. But after riding for 6 hrs, I find myself tired & sweaty. Plus the winds have picked up again. And to top it, almost all of this stretch is a climb.
I took a deep breath and resumed cycling.
The wind & gradient made my task all the more difficult. But I pressed on.
See how harsh the wind is. The coconut leaves are all bent.
About 5 km away from Annavaram (around 110 km mark), someone in a car was waiting for me. As soon as he saw me, he waved his hands and stopped me. Turns out he is a biker from Vizag. He got curious upon seeing the site of a lone biker and wanted to know about me. We chatted for a while and exchanged phone numbers.
Finally at 2:50 PM, I reached Annavaram village gate. Yay!. It took me more than 9 hours & 30 minutes to reach here. Out of this riding time is 7:30 (it might have been slightly less than this as my tracking app – MapMyRide was not set to autopause for the first 65 km).
Finally, at Annavaram village gate after 9:30 hours of riding.
Day 1 – At Annavaram
I went to the first hotel I can find and ordered lunch. While waiting for lunch, I called Jo & my mom to tell that I reached safely.
After lunch I asked them if they have any rooms to stay. They have vacancies and I immediately took a room. I quickly showered and took a nap. But I could hardly sleep as my thighs and wrists were aching. It looked like I could not ride back and may have to take a bus to my home.
Originally I planned to visit the famous Annavaram temple in the evening. But my legs & back felt too sore to do anything. So instead I went for a short walk to explore the village in the evening.
Later I ordered dinner and watched some football on TV.
Around this time, I have decided to skip the return ride & take a bus instead. I told the same to Jo when I called her to say good night.
I kept waking up in the night due to one pain or other. I turned off the alarm at 6:15 AM and went back to sleep again.
Finally when I woke up at 8:00 I surprisingly felt rejuvenated and ready.
At that moment I decided to ride back home. My plan is to ride as far as I can and then take a bus.
Day 2 – Annavaram to Anakapalli (88 km, 4:30 hrs riding)
After a heavy breakfast & some coffee, I checked out of the hotel and started my return journey at 8:43 AM.
Since I have climbed & gained elevation yesterday, the first 40 km ride from Annavaram was almost downhill mixed with few flats. So I could maintain speeds between 20-30 kmph. At one point I even reached 43.5 kmph, although barely for a minute.
I did not stop anywhere for first 25 km. But then the views by the road are too breathtaking to ignore. So I stopped for a few pictures at 25k mark & again at 27k mark.
At the later stop, I found several open billed storks in the rice fields. It was a pleasure watching them forage for food & fly elegantly.
Pretty soon I reached the toll plaza. And yay!, no toll for me. Another reason to bike.
Had to stop again at 10:55AM to appreciate the beautiful green & blue views. Took a few pictures and resumed ride. Reached 50 KM by now.
At this point, I changed my plan to ride for 83 km. This will make the total distance as 200km.
By 11:30, I reached Elamanchali (a small village 20 km away from Anakapalli, my destination for the day). By now it started raining quite a bit. My bike accumulated quite a bit of dirt & soot.
By 1:00 PM, I reached outskirts of Anakapalli and spotted a nice road side restaurant. Immediate stopped there for lunch. As soon as I stopped it started raining heavily. So I had a leisurely lunch spending close to an hour there. Finally the skies cleared at 2 PM and I resumed my ride.
When I was 3 km outside Anakapalli, a gentleman on motorcycle slowed down and rode with me for a few minutes. He asked me several questions about where I am riding, what I do, why I am riding etc. He even invited me to have lunch with him. But I had to decline as I already finished my lunch.
Finally, at 3:06 PM, I finished 88km and stopped my bicycle ride.
An empty mini truck going towards Sabbavaram (half way between where I stopped & my home) offered me a ride.
From Sabbavaram I took an Auto (a 3 wheeled taxi, also called as tuk-tuk) to my home.
And that marked the end of my first ever 200 km bike ride.
My impressions:
Growing up, I used to be the least fit kid in my school. I sucked at all sorts of sports. I got kicked out of football, hockey, badminton, cricket and all other sporting teams in school.
So naturally I assumed that I am not good for athletic activities.
Once I completed my studies and got a job, I realized the importance of living an active life. So I started jogging, cycling and playing sports.
Ever since I quit my job to start my own business, I became even more fitness conscious. Every year (since 2010), I have challenged myself to break personal barriers. When I first considered biking, I could not imaging anything beyond 50km per day. But with practice and motivation, I could finish this ride.
Few thoughts:
- Do a few practice rides: I have been riding 20-30km per day for almost a month before taking up this challenge.
- We underestimate our body: We can take on big challenges, endure pain & perform at our peak so much more than we estimate.
- Drink & Eat well: I call my diet really healthy. We eat home cooked food 90% of the time. Most of our diet is veggies, fruits, milk, nuts, rice & wheat. Almost half of the vegetables & fruits we eat are grown in our back yard. We rarely drink and eat meat once a week.
- Enjoy the ride: Stop and enjoy the scenery. Talk to fellow travelers and take ample breaks. Any distance can be covered easily.
Thanks to
- Jo: for supporting & encouraging me.
- My mom: for not freaking out when she learned about the ride.
- Free Country & ADC: for the motivation
- You: for supporting Chandoo.org so that I can chase my passions.
Now for an Excel Dashboard visualizing the ride
Soon after I got back, I started thinking about analyzing all the ride data. I did what I do best. I created an Excel dashboard to tell the 200km ride story.
Here is a snap shot of the dashboard (click to expand).
Click here to download the Excel Workbook.
It is unlocked. So go ahead and examine the file. Break it apart to understand the logic & formulas. Learn something new & be awesome.
How is this dashboard made?
Here I am going provide only the highlights. Please visit Excel Dashboards page for fantastic tutorials & explanations on how to make dashboards.
- Tables: for structuring ride data. All the ride data came from MapMyRide. I used Doogal.co.uk to get elevation profile of the route.
- Column & line charts: for the splits, speed & elevation.
- Form controls: to make the charts dynamic & to control the Ride pics slideshow.
- Picture links: to show one of the many ride pictures thru scroll bar form control.
- Ample dose of formulas: Mainly INDEX, MATCH, SUMIFS & COUNTIFS.
More personal stories
If you are in the mood to know Chandoo.org & me a little more, check out these:
- Story of Chandoo.org startup
- Thank you, we have a home
- Thank you, we have a car (and another one too)
- Travelogues from my visits to Maldives & Australia
- Confessions of a dad
See you again with more awesome Excel stuff.
49 Responses to “Interactive Pivot Table Calendar & Chart in Excel!”
Excellent post again from awesome chandoo.org
This is one of the post to evident, without using macros we can create excellent charts using available excel options.
Slicer is one of the useful option in excel 2010 .. excited to see more options in excel 2013.
Regards,
Saran
http://www.lostinexcel.blogspot.com
Nice one chandoo............... great work done.....
Cool article. Only downside was that I didn't see at first that I needed 2010. Guess I still have to wait awhile before getting to try this out myself.
I consider myself an Excel expert, but you constantly amaze me with posts like this. Fantastic calendar!
Good post, like this little trick!!
How to not show the value in the cell
format the cell to custom with the below
;;;
Could you add lists of holidays to be transferred to the calendar days?
Two lists would be needed: 1) for the holidays that stay fixed (eg, CHristmas), and 2) for the holidays that move around (eg, Thanksgiving).
Such lists would be prepared externally, and the program would transfer their information to the appropriate days.
Wow! This is something amazing. I am going to do some practicals with this and show a sales trend on this. As we have our sales plans weekly basis, this should impress by boss when put in dashboard. Cool.
And thanks1
Chandoo you have a knack of getting on to these great looking very creative ideas!
One thing with calendars I have seen before is not catering for able to enter notes or appointments or project milestones. But with this one it's easy enough to add the extra lines as you have done for the chart concept and link to this other type of info.
For 2003 we could replace slicers with a validation style dropdown couldn't we?
Chandoo, you are awesome;) i was using calender to show my reports, but i had made all months and then underneith date shows the value, man its really awesome . i am going to use this format for my reports.. only draw back for me is i am using 2007. hence no slicer.. may be have to modify with out slicer.
Why not use =weeknum() for the weeknum column?
Great tricks! I love trying to reproduce the charts myself to get the hang of 'em. This one was great.
My only issue is getting the VBA in the year object to refresh the data. I used the VBA provided at the link, and, I can see it in the Macros tab, but, when I click the spinner the data does not update. Any tips?
Thx!
3G
^^Ignore this! NOT ENOUGH COFFEE YET.
I forgot about the "Assign Macro" option
:-s
Just started at chandoo - this is great!
I opted to use the formula =IF(F6>F5,G5,G5+1) for my weeknum - worked for me (I didn't get all the way through the example, since I'm running Excel 2007 - so don't know if that'll affect anything later in the example). I'm open to comments on this alternative approach.
Thanks for creating this website!
VC (Excel student).
Very cool - but now I'm even more excited for the new time controls for Excel 2013!
Great calendar...
I wonder whether we can make a school calendar (Class, subjects, teachers) using this calendar, assuming the weekly plan is duplicated across the year.
I would love to be a part of creating a class schedule...I'm attempting to help a friend (gratis) to do just that - can you point me in the right direction or provide a sample of sorts?
[...] Wow – what do you think of the interactive calendar chart demo above? To achieve this impressive effect you must have Excel 2010 because it utilises slicers, which is a feature introduced in Excel 2010. Find out how this treasure was created on Chandoo’s page. [...]
Hello Chandoo,
Great works! I learn a lot from this website. Here is the problem I met when I follow your tutorial: once I run and save this cool pivot calendar chart , the size of excel file will increase every time. Could you let me know how to figure it out? Thank you for your time in advance.
An excel chart-fan from China.
I already figured it out.
wow, love the calendar, i'm a newbie, found this site and it's amazing.
Got it mostly figured out, but could do with help with your named range 'tblchosen'
I can build the pivots, link the calendars together but can't see how to use index(tblchosen...) to pull through the productivity figures
appreciate any help
thanks
Great. Miss the Today button. Will try and figure a way to add this to the file.
I want to start the week on Monday, not Sunday (MTWTFSS). Re-arranging the calendar tab works however, any month where the 1st is a Sunday starts on the second and totally omits Sun 1. I have been tinkerign for a while, but can't seem to figure this out.
Changing F2 on the 'Calcs' tab to 2 so that the week starts on Monday works.
Cutting & pasting Sunday on the 'Pivot Calendar' tab and moving all cells up 1 row works.
However, using April 2013 for example, you lose the 1st off of the pivot calendar so that the month starts on 2 April. What should happen is the first row should only show Sun 1 April and then the next row starts Mon 2 April. Still can't fugure out where the problem lies.
"Further Enhancements:
Adjust week start to Monday: Likewise, you can modify your formulas to adjust weekstart to Monday or any other day you fancy."
I have tinkered with this previously with no success, does anyone know which formulas require tinkering, I have only succeeded in breaking this in an effort start a week on a Monday.
[...] Interactivo Artículo original var dd_offset_from_content = 50; var dd_top_offset_from_content = 0; Tags: 2013, calendario, [...]
Completely off topic, but how do you create those animated pictures in your tutorials? It is not a movie (like the Youtube movie), so what software do you use to create such high quality "animated" pictires? Thanks
Jeroen
the animated pics are called Animated Gifs
they are made using Camtasia
Refer: http://chandoo.org/wp/about/what-we-use/
This is fairly easy to do just using calendar formulas, which would be quicker, and doesn't need VBA? Am I missing something?
[...] on how to generate an interactive calendar using pivot tables. Please check out Chandoo’s Interactive Pivot Table Calendar & Chart in Excel before reading this, as I want to go through how I used his method to adapt a calendar which was [...]
Great tip shared by you... howevr would appreciate if you could mention in your tricks about excel version. The example above would work only in excel 2010 and above I believe. Please help me if there is any way we can use the tip in excel 2007 as well..
Many Thanks,
Regards,
FK
Hi, I'm going to give this a shot, but one small question before I do. Can a linked cell be updated based on the date that is selected from the calendar? The calendar is really cool and this would make is especially good to use (and easy and fast).
Regards,
swissfish.
This post is awesome, and using your instructions, I was able to get this to work with a pivot table that pulls directly from a Project Server database. It was a bit complicated to get the day to sum correctly, but I managed to finagle it. I hope you don't mind if I link back to you when I post my instructions.
Thanks for giving me a starting point for this!
[...] http://chandoo.org/wp/2012/09/12/interactive-pivot-calendar/ [...]
This is great, and pretty much everything I was looking for.
However, I already have a large spreadsheet, and I want to include your worksheets in it. I copied all the worksheets and the Module 1, but I can't get it to work. What else do I need to transfer / update please?
Hello there, is it possible to use this pivot to produce a calendar style chart, with returns multiple data per date, which on the calendar then, when clicked links to the data to provide more background information? What do you think? I'd love if I could pivot when i need. thanks, m
Hi, did you ever figure out how to do this? I would love to find a way...
This is amazing and will work well for my calendar project! My question is, how can I expand the calendar to fit a standard sheet of paper?
Wow - this is so creative. I'm taking the basic idea and building a reservation calendar.
Question: How do you get the month and year slicers on a different page than the pivot tables? I'd like to have my final calendar on a separate page from the pivot.
[…] http://chandoo.org/wp/2012/09/12/interactive-pivot-calendar/ […]
This is perfect...is there a way to add notes/tasks to the individual days?
Excel will not let me insert blank rows between lines in the pivot table. I am use Excel 2013 - is there a pivot table tools command that must be used?
I can create the pivot table calender with a year spinner & month slicer but I do not see how to display the the attendance information that I have in the original data table.
Thank you for the wonderful post and I am sorry for my lack of understanding...
Excellent!
Please show me how to add an alternative calendar to this calendar, Chinese or lunar calendar (and by lunar I don't mean phases of the moon), like what they still use in Asia
Thanks
Christopher
[…] Wow – what do you think of the interactive calendar chart demo above? To achieve this impressive effect you must have Excel 2010 because it utilises slicers, which is a feature introduced in Excel 2010. Find out how this treasure was created on Chandoo’s page. […]
Hello my name is Maurice, excuse me for my further request, but believe me, without your help priprio not know how to solve this problem.
So: always using a chart positioned on an excel sheet I wanted to match each square (series) to a single cell, to create a perpetual calendar.
Now everything works fine; except that for a fact, and it is this: In the calendar as you well know some numbers may not be apparent until certain conditions, which I solved by writing this "= O code (AA5 = DATE ( $ H $ 1; MONTH ($ AD $ 12) +1; 1)) and the game and done.
Now I would like to achieve the same thing using the Chart; How can I do to make this happen! let me also just a practical example so that I can understand all the rest then I'll do; Thanks Greetings from A.Maurizio
Link Program : Link: https://app.box.com/s/lhqva3eji0xcf2nmk8lxyki88tt1mi5t
Great info, thanks for sharing
Hi,
I love your calendar however I am modifying it for use in displaying employee performance metrics on a day by day basis.
I see where tblChosen and tblDates are named ranges however I cannot find them anywhere.
Are they assigned to specific cells because I cannot tell.
I see both of them in the Name Manager, which tells me what they refer to but does not give a value or cell location.
@Mike
With the Names in the Name Manager
Simply select the name
Then click in the Refers To: box at the Bottom
Excel will take you to where the Named Range is referring to
[…] Wow – what do you think of the interactive calendar chart demo above? To achieve this impressive effect you must have Excel 2010 because it utilises slicers, which is a feature introduced in Excel 2010. Find out how this treasure was created on Chandoo’s page. […]
Hi, Chandoo
This Pivot Calendar is an excellent idea. I’ve done one for myself using your guidelines. I just need something I’m not being able to do. I need that when I open the file the default date is set to today’s date. I know how to do it with conditional formatting. But I think I’ll need some vba coding for this. Can you please help me with this. Thanks in advance