I know I am late to the party, but better late than…, uh! forget it.
As the keen readers of our blog knew, I like cricket and I show my enthusiasm by making an excel dashboard (or infographic) whenever Indian team reaches a major milestone. So naturally, I was super excited when we won the ICC Worldcup 2011. Last time Indian won the event was in 1983 and my idea of a dashboard at that time was a bottle of milk and jingo-bell, my favorite shake-to-make-annoying-noise toy. I think our latest world-cup victory deserves something more than that. So here we go.
Excel Dashboard to Celebrate India’s World-cup Victory (2011)
(Click here to see larger version)
How is this Dashboard constructed?
This dashboard was one of the most difficult ones I built, because I did not know what to put in the dashboard. I know that the dashboard should reflect our team’s hardwork, journey, outstanding performances but I had no clue which format & layout exposed these qualities. So I took a lot of time drawing up sketches of possible dashboards before hitting on the present layout. Once I came-up with the layout, the actual dashboard took me about 4 hours to make (and may be another 4 for polish).
Here are some of the techniques used in the dashboard:
- The dashboard is divided in to 3 areas – Highlights, our journey to the victory and best performances.
- Highlights: This section shows overall summary of all the 9 matches India have played. It shows some interesting statistics, how much our top players contributed to our victory etc.
Techniques used: All parts of this are made with text boxes and simple text formulas. - Our Journey to victory: This was the most time consuming & intense part of the dashboard as I made this portion interactive. The left side shows all the matches we have played by date and the results. When you select a particular match, the right side portion shows a match summary. This includes match venue, result, toss details, India’s top 3 batsmen, top 3 bowlers, photos of India’s best batting & bowling performers, oppositions score, best batsman, bowler, their stats. It also shows the country flags etc.
Techniques used: The click to select as described in on-demand charts article, conditional formatting, picture links, more picture links, LARGE formula.
The most difficult part of this was to get a moving arrow that would change its position based on which match is selected. I did this with picture links, offset formula and a dynamic named range. (Examine the named range movingArrow). - Best performances (top 10): In this area, I showed the best batting, bowling, catching, partnership performances for all the matches in World-cup (not just India’s matches).
Techniques used: All the charts are made in Excel 2010 using solid bar conditional formatting & picture link based techniques. Later, I just copied them and pasted as images so that they look same in Excel 2007 also.
Colors & Fonts:
- I choose the blue color as it is team India’s jersey color. I used orange to contrast the best performances.
- The fonts are Bookman Old Style & Meriyo UI.
Download the Cricket World-cup Dashboard Excel file:
Click here to download the locked workbook. [mirror]
Why lock it? I am giving away unlocked version of this workbook + a 36 minute lesson to all the customers who buy Excel Dashboard Tutorial or Excel School Dashboards. So if you want an unlocked copy of this, go ahead and get either of them. (If you have previously bought one of these products, you will receive an email with instructions on downloading your bonus.)
Credits:
All the data for the dashboard came from espncricinfo.com.
Special thanks to Ravindra, my assistant, for compiling the data.
How do you like the Dashboard?
I was afraid whether I can do justice to our team’s glorious world-cup victory in a dashboard. So I kept on delaying this. But in the end, I am happy with the dashboard. It tells the story of our team’s journey and highlights best performers.
What do you think? Did you like this dashboard? How would you have designed it?
As an aside, Many of our readers know only about cricket that chirps. So I want to ask, did this dashboard make any sense to you?
Other Awesome Resources on Excel Dashboards:
- Excel Info-graphic Poster to Celebrate Sachin’s 200 runs
- Excel Dashboard with 10007 comments data
- Excel Dashboard to prove that Hui is awesome
- More on Excel Dashboards
- Want to make dashboards using Excel – Join Excel School.
23 Responses to “Learn Top 10 Excel Features”
What it looks like if excel without formula?? 🙂
It would be not excel it would just be fancy tables in which you could just use power point. (Chandoo) would Access be an alternative?
Awesome piece of work!!!
Great article.
Chandoo - my biggest interest in the article was the awesome word-graphic at the top - where did you go to get it done into a shape?
@Rich.. thank you. I used http://www.tagxedo.com/ to generate this word cloud. I took all the comments in the original post, pasted them in tagxedo website and set up the shape etc.
Awesome Chandoo.. You need always needs coffee to start up with. BTW , how did u created the Heart Shaped picture filled with High Repetitive text in it .. Please put it on your Next blog ...
Chandoo, good article. I’ve added a link to it from Connexion – our collection of the most useful and interesting spreadsheet-related articles from the web. See http://www.i-nth.com/resources/connexion
Hi,
Just one small question. Where the hell have been I in the past for not discovering this website sooner?
I've lost a job interview recently where even though I had the subject knowledge, I was not upto their mark in Excel.
Thank you for all the free tips, guidance and for creating this forum environment.
[PS: I've just been through the site for the 1st time, and have signed up for the newsletter. You can expect pretty stupid questions from me soon]
Hy Chandoo, you always inspire me with to explore something new in excel. This data structure table is only for excel 2007 or compatible to 2010. I recently installed latest excel version 2013 in my System and experience problems regarding operating according to previous one. I'm waiting your article relates to that excel version.
Thanks
Awesome article Mr. Chandoo and that is a awesome heart shaped pic you created. Great tips as well.
[...] Learn Top 10 Excel Features | Chandoo.org – Learn Microsoft Excel Online. [...]
Chandoo is awesome..
Thanks, i got better, And i always get 90.50 in my grade card but now i get 96.50 i improved because of the tutorials you gave, Thank You Very Much Chandoo Guy.
Hi chandoo, i am intersted in seeing the video or step by step done procedure of analysing the comments and presenting in the data percentage steps. I think this one would be first step in finding out how generally happens data calculation. Thank you.
As well i would like to know how to get that black shape art of your face which i see in chandoo. I am interested in making it for me.
Nice to see the features considered by Excel users to be most useful. It might be a good idea to also analyze StackOverflow Excel questions to see what keywords appear most often.
Here are my top 10 Excel Features (for advanced users):
http://www.analystcave.com/excel-10-top-excel-features/
Thanks a ton for this it totally helped with my homework ????
Very good effort
Thank you for this. Lots of learning in the links you've provided for this septuagenarian.
Pls send me new post
Dude, your humor ? ?
Loved your work.
Hello Sir,
I am Sanjeev Khakre and i from Indore City, India , I am your big follower and i have watch your videos and learnt a lots of excel trick or function and many more . thanks so much for all of your excellent support.
Your excel knowledge is real awesome.
Thanks
Sanjeev
Your work is excellent but pls willing to know more details about the features of microsoft excel
Chandoo Would Access be a better alternative than VB?