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.
31 Responses to “Celebrating India’s Worldcup Cricket Victory – In Excel Dashboard Style!”
Great dashboard Chandoo ! I loved going through it...
A few comments:
- very nice visualisation of the key statistics
- adding the MoM winner in the match-wise pop-ups would be the icing on the cake !
- in some of the pop-ups, the bowler's image is vertically crunched up (rows 2, 4, 5, 6 and 8)
And here are some tips for further protection of the data
- the VBA project can also be password protected
- the hidden worksheets' visibility property can be set to xlVeryHidden (in the VBA Project Explorer properties window)
- The workbook itself can be protected to prevent display of hidden sheets
- if you wish to prevent the cell contents from being displayed in the formula bar, you can set the cells' Locked AND Hidden properties to true (from the Format Cells dialog) and then protect the worksheet
Cheers!
Khushnood
Hi Chandoo,
The workbook is not opening in excel 2007 or below,please advice.
@Kushnood.. thank you so much for your feedback.
The image crunching is a bug in Excel 2010. Images (picture links) seem to work fine in Excel 2007, but then there are other problems.
I did not want to heavily protect my file. That is why I left it mildly locked 🙂
But very good tips on worksheet protection.
I too thought about MoM, but the idea came very late in the execution. So I left it out.
@Chandoo: I'm going to add a small head nod to your comment about deciding how to display the information.
I recently developed a very small dashboard for a job interview, and I had the toughest time figuring out exactly how to express a concept with the data.
-
I think I finally figured out WHY though: When we as practitioners develop intelligence reports, we are almost always developing a tool for someone to make business decisions. To develop an info system without any sort of feedback from an individual in need is incredibly difficult: You have to keep a specific 'role' in mind (in your example: Coach? Team Owner? Sponsors? Fans?), and make assumptions about what decisions that role is performing (Different training? Hire different players? How to market a product with endorsement from which player? Who's your new favorite player?).
-
I have no doubt most if not all of this, and more, went into your development process.
But this example alone should give some insight into what we should be doing: We SHOULD be confirming with the decision makers that the information is expressed in a way that is sufficient to make the correct decision, We SHOULD be making an observation of how the information is used and how often, We SHOULD observe past info systems to determine how and why they don't deliver on the new requirements. - A process made extra difficult when we have to fight with ourselves on each point.
Hi Sir
A very nice site to visit & learn Excel. Which wordpress theme you are using. Superb
Hi Chandoo... great work, but this dashboard is not too impressive ( pardon me if my expectations are too high from you) - Not too much of information was reflected in this dashboard. Maybe some more info like MoM would have added more value to it
Awesome!!
Nice one, As @Khushnood said MoM Pop up is the one really missing in the Dashboard. Everything else is perfect and the Match summary is really awesome , especially 3D effect given to it. Once again thanks a lot for sharing
Great dashboard Chandoo
Dashboards don't all have to be bells and whistles, they just have to tell a story, and yours has done that well.
It is a shame that this one is colored Blue, It should have been Green and Gold !
Next time maybe
Cameron said rightly that its very challeging to decide data to express in concept with excel or power point (i say)and such as your this beautilful example shows this can be done,but would you please share your though process for overall look concept or what ideas you take to plan our your dashboard-they may be helpful for excel user as tips while deciding and shaping dashboard or even for learner like me.
This your very example can be used for teams performace or task like such.well done. so your dashboard is art please share more - suggest if you can give some example with sample data and showing various dashboard and may allow people to vote.i want to learn the tricks you have.
@ all : this is only chandoo's love to our team which he want to share with us. So nobody has right to tell-"this should be there,this should like this, u have to do so and so,blah....blah"
Good work chandoo.
good job!! Thnks Chandoo for sharing this all with us... 🙂
I love some of the techniques highlighted via this dashboard. Loved the match summary and yeah as highlighted above MoM would have been great as well.
@Istiyak : Chill mate. I think the intention of all to make it more "awesome". We all know the contributions Chandoo makes and this is a forum where opinions should be welcomed as long as they are constructive. Chandoo has set high standards and hence the enormous expectations.
Can any one explain me what is MoM exactely?
Really like the interactive part...from experience people are much more likely to look at the info contained in the report if they can play with the data. I'm going to implement the on demand tip to one of my dashboards right now...! Thanks for the inspiration and keep the great ideas coming!
@Istiyak
MoM - Man of the Match
This isn't based on stats but is chosen by a panel as the person who made the biggest contribution to the game
Thanks hui.... I thought it is one of the technique in excel.
Good work, I agree with some user opinions here, saying this is not impressive is rude in my opinion - he didn't make this to win the excel dashboard of the year award, but it does exactly what he intended it to do. Nice one chandoo
@All: Thank you for your suggestions. I really appreciate your enthusiasm to improve my work. I certainly do not think this is the best dashboard or anything like that. It is just my narration of the worldcup story. I will keep some of these points in mind for next such project.
@Cameron: Very good points. I teach about this in Excel School too. Many of us jump to Excel when someone asks us for a dashboard. The best thing to do would be to spend a couple of hours with your audience, learn what they want, then go back to a paper, draw up some sketches and validate if that is what they want. This will save a lot of time during implementation phase.
Dear Chandoo,
I regularly follow your advice on Excel. Here I found a nice interactive chart & wanted to share with you: http://online.wsj.com/article/SB10001424052748704448304575195840951492422.html#articleTabs%3Dinteractive
Can we create this in Excel?
@Rajdeep
Yes, That chart can definitely be done is Excel, but the article doesn't list the data
.
For some great examples of what can be done in Excel have a look at: http://www.excelhero.com/blog/2011/03/excel-animated-business-chart---body-mass-index.html
especially the other examples listed at the bottom of the page
Wow...that's great one chandoo....
[...] Cricket World-cup Dashboard in Excel [...]
when i try to make similar summary boxes at the top they dont lineup and overalap on printing, any ideas why please?
select all the boxes and on the format tab you ll have align option,
that can be used to suit your needs .
Hello chandu sir,
Can I get a template to update the cricket score of my crickets tournaments.
We always arrange cricket matches but unable to keep the records is match. 🙁
Please help me with excel dashboard or a template.
If possible, plz provide.
Still awaiting plz help
PLease Help
Good Morning All,
Has anyone got 2019 Cricket World Cup tracker please.
Thanks
Manan
A very nice site to visit & learn Excel. Which WordPress theme you are using.
Superb