fbpx
Search
Close this search box.

Celebrating India’s Worldcup Cricket Victory – In Excel Dashboard Style!

Share

Facebook
Twitter
LinkedIn

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)

Celebrating India's Worldcup Cricket Victory with Excel Dashboard

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:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

31 Responses to “Celebrating India’s Worldcup Cricket Victory – In Excel Dashboard Style!”

  1. Khushnood Viccaji says:

    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

  2. Arnab Banerjee says:

    Hi Chandoo,
    The workbook is not opening in excel 2007 or below,please advice.

  3. Chandoo says:

    @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.

  4. Cameron says:

    @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.

  5. Abhishek says:

    Hi Sir

    A very nice site to visit & learn Excel. Which wordpress theme you are using. Superb

  6. Abhishek says:

    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

  7. MK says:

    Awesome!!

  8. Gobish says:

    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

  9. Hui... says:

    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

  10. ashish says:

    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.

  11. Istiyak says:

    @ 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"

  12. Istiyak says:

    Good work chandoo.

  13. Swapnil says:

    good job!! Thnks Chandoo for sharing this all with us... 🙂

  14. Ninad Pradhan says:

    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.

  15. Istiyak says:

    Can any one explain me what is MoM exactely?

  16. Prem Sivakanthan says:

    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!

  17. Hui... says:

    @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

  18. Istiyak says:

    Thanks hui.... I thought it is one of the technique in excel.

  19. Dan Snaith says:

    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

  20. Chandoo says:

    @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.

  21. Rajdeep says:

    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?

  22. Hui... says:

    @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

  23. Girish says:

    Wow...that's great one chandoo....

  24. mdeangelo says:

    when i try to make similar summary boxes at the top they dont lineup and overalap on printing, any ideas why please?

  25. samadhan says:

    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.

  26. Manan Patel says:

    Good Morning All,

    Has anyone got 2019 Cricket World Cup tracker please.

    Thanks
    Manan

  27. A very nice site to visit & learn Excel. Which WordPress theme you are using.
    Superb

Leave a Reply