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.
12 Responses to “Speeding up & Optimizing Excel – Tips for Charting & Formatting [Speedy Spreadsheet Week]”
Usually when I dump data into my files to update values, the formatting sometimes go to all rows or columns. So what I typically will do is go to the last row and then the last column and use Ctrl + Shift + end and then delete the cells highlighted. this will remove all unknown formats in the worksheet. Also, after you have done this, you won't see the benefit until you save the document. Sometimes I even have to close and reopen. The direct sign that this has improved is the size of the scroll bar and range.
I have some comments on a couple of the points.
1. Camera objects
Tip: I use defined names in conjunction with camera tool objects.
Each camera object gets a name like so:
CameraItem01
Referring to: =IF(PicsOn=1,Sheet1!$C$2:$S$5,"")
By setting the PicsOn name to 1, the camera objects become "live", by setting the PicsOn name to 0, they become static. That improves performance enormously.
4: Conditional formatting
Lots of CF rules can slow down your workbook a lot. And it does not show the calc progress a "normal" recalc does on slow workbooks.
5. Format whole columns/rows
as far as I know, there is no problem with formatting entire columns/rows performance-wise, on the contrary, Excel is more efficient when you format an entire column than when you format a couple of 100 rows of a column.
6. Styles.
Here I wholeheartedly disagree. I say: Use styles. And use them religously.
I mean: if you have applied a (custom) style and you need to change a small piece of formatting to make that one cell look right, force yourself to create a new style just for that cell. It forces you to really think about your spreadsheet design and try and streamline it. It also makes it much, much easier to change your sheet's appearance later on. See http://www.jkp-ads.com/articles/styles00.asp
Very good insights Jan..
Camera objects: I often use similar technique to turn off images in my dashboards.
Formats: Thanks for clearing this. Do you think formatting larger ranges has any impact on macro speeds or it does not matter?
Styles: Thanks for telling us about this. As I mentioned, I am not sure about the styles, but I am under the impressions that excessive use of styles can bloat the file size.
@Chandoo:
If you stick to formatting entire rows/columns I don't expect macro speed is affected. Better: try it!
If you use styles properly AND as a replacement of ad-hoc cell formatting, I expect you'll see that the file actually is smaller in size.
This is because the cells now only have a reference to a single style instead of a reference to a custom cell formatting style.
Many cell formatting combinations get created if you format your cells in an ad-hoc manner, which was responsible for the dreaded "Too many different cell formats" error in Excel 2003 and older. Excel 2007 and 2010 have a higher limit there, but it does slow down your file with many of them.
Style bloat in my point of view is what you get by copying and pasting a lot from various other files and thus get Normal 1, Normal 1 1, Normal 1 1 1, ... I have seen workbooks with as many as 6000 styles, all caused by copying and pasting from various differently formatted workbooks.
Excel 2007 and 2010 have fixed a number of issues regarding copying of styles, but for workbooks with a long editing history, the trouble is already in the workbooks.
Cant emphasise the importance of reducing the amount of formatting in a workbook - this has a suprising impact on workbook size. I've always kept to one font, and no more than three colours - this has worked well for me. Keeping things clean and simple should be the motto when designing any type of report/dashboard that is going to be distributed around the organisation.
You can also save a few MB's by saving as an xlsb file.
Has anyone else mentioned that only the first item in the "more ..." section is hyperlinked.
Prem, have you confirmed by trial that XLSB file size is smaller than same XLSX file? Sorry, I just tried it with a small, simple XLSM file. I was surprised to see you are correct. File went from 40kb to 37kb. I thought that the compression of the new file would make the new file smaller.
@Ron
All Excel files have a minimum overhead that they have to include which is around 8KB, just to store a simple number or letter.
So with a small file of 40KB you will not see a huge improvement in file size
With files greater than 10MB you will see large improvements in size.
The compression gained also depends on what the contents of the file include. That is straight numbers, text and formulas can be greatly compressed whereas files that contain a lot of objects especially pictures gain very little from using *.xlsb files.
@Ron.. the other articles are yet to be published. All the links will be updated by Tuesday (27th March).
Hi,
I have a need for x,y scatter chart to have arround 30 data series.
like this:
http://i65.tinypic.com/jra8lc.jpg
Also I have multiple of such charts in one excel file.
Is there any way to make excel faster, because it is irritatingly slow?
(though my PC config. is quite on the level)
Thanks in advance!!!
@Mil
30 series won't be the issue
It is the number of points in the series
Also remove all fancy modifications, like shadows, fancy fills etc
I'd suggest asking the question in the Chandoo.org Forums http://forum.chandoo.org/
Attach a sample file with an example of what you are after
@Hui
I've already removed all fancy mod. The problem is there are also a lot of data points in one series.
Thanks for the advice!
@Mil
Do you really need every data point ?
Where is the chart being presented Screen or Report
On a screen you are unlikely to use more than 800 pixels for the chart area
So using any more than about 250 points is not adding values
On an A4 chart in landscape lets say the chart area is 6" long and at 300dpi that is 2000 pixels
Once again using more than 800-1000 points will not add any value
I have seen charts with 30,000+ points and when this is explained and a work around shown people appreciate the speed up
For a work around try setting up an area where you select say every x'th point using an Offset or Index Function
Then plot that data
I'd suggest asking the question in the Chandoo.org Forums http://forum.chandoo.org/
Attach a sample file with an example of what you are after