Sports Dashboards in Excel – A Tutorial

Posted on October 20th, 2008 in Charts and Graphs , Learn Excel - 18 comments

sachin-tendulkar-thumbOne of my favorite cricket player, the GOD – Sachin Tendulkar has become highest test run scorer.

What do I get if Sachin becomes highest scorer, you may ask.

In order to celebrate this occasion I have created a cool sports dashboard in excel with some of the top test cricket players’ statistics. And, you get to learn how to make one. So read on and wish the little master many more successes.

Download the sports dashboards in excel and continue reading.

1. Find out which data you want to show in the dashboard

Not all data is important. Especially when you are creating a dashboard, it is vital to provide only data and insights that are necessary to draw conclusions. A simple rule of thumb is:

Your car is complex machine with thousands of parts, few micro-processors and tons of other stuff. But the driver dashboard shows only three (at most 4) data points at any time – speed of car, engine heat, tachometer showing how fast your engine is rotating. Few on/off indicators that wont bother you unless you need to notice: “seat belt sign, gear indicators, airbag status, batter status etc.”

So next time you have hundreds of data elements just use this analogy to cut down to the bare minimum and show only those.

For our tutorial, the data comes from this rediff article with statistics for various test cricket players:

test-cricket-sample-data-table

From the looks of it, there are just 4 things that are vital: total runs ever scored, highest score, average score per innings, Total number of centuries(and half-centuries).

2. Create one chart

First let us create a simple bar chart for the total score data. Just select the cells with total scores and click on insert chart icon and select “bar chart” as chart type.

Now we get a default excel chart. I have used the following steps to adjust the formatting:

  • Remove background
  • Remove grid lines
  • Adjust axis scaling:
    You may not want to adjust the axis scaling minimums. Read the follow up discussion here: Should bar charts always start at zero? Reader Poll

    Since the score are from 8001 (minimum) to 12027 (max) I have adjusted the axis scaling options set minimum value as 7500 and max. as 12500.

  • Remove axis
  • Add data labels and adjust their alignment, adjust font-scaling as well.
  • Adjust colors and change the bar color for maximum value
  • Adjust gap width (from 150 to 10 or something)

See this image with how the charts looked after each step:
making-sports-dashboards-in-excel-sml
[larger version]

3. Adjust the chart size / location so that it fits snugly inside the table with data

Just select the chart and adjust its size and location until it fits inside the table. You may want to use aligning chart objects on spreadsheet trick.

4. Repeat the steps 2 & 3 for remaining charts

Just copy paste the first chart you have created and change data source and scaling options. Adjust formatting if needed. Once they are all ready the dashboard should look like what you see below.

sports-dashboard-excel
[larger version]

Download and play with sports dashboards in excel

Have fun :)

More excel dashboard tutorials:
Creating KPI (Key Performance Indicator) Dashboards in Excel – 4 part tutorial
Making Visualizations for Dashboards
Too much data? Use tables instead

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

18 Responses to “Sports Dashboards in Excel – A Tutorial”

  1. Jon Peltier says:

    “Adjust axis scaling”

    Oops. Rule #1: Bar and column chart value axes should start at zero. Since the lengths of bars and columns represent their values, starting at a position other than zero will lead to a misleading display.
     
    If you use a scale which does not include zero, use a display technique that represents value by position along the axis. This is accomplished by using markers. Your charts would then be like Dot Plots (see Dot Plots and Tornado Charts and Dot Plots.
     
    This type of chart-in-a-table is also well served by In Cell Charting with Worksheet Formulas. Haven’t you written about these in this blog?

  2. Chandoo says:

    @Jon, you have raised an excellent point.

    I have actually dramatized the charts by adjusting the axis scaling to bring out the largest values. And since it could be misleading, I have added the data labels with values. Should I do it? I think this is a question that our data can answer. If you look at the numbers, they are all very similar (especially average scores, number of 100s and 50s), so if I handn’t adjusted the scaling they would have looked very similar making any analysis difficult.

    In fact I like your point so much that I am going to start another post just debating this rule. So chip in and let us see what other readers think

  3. Jon Peltier says:

    Chandoo -

    I understand why you adjusted the scale. I agree it’s often necessary to change the scale to resolve the data. Unfortunately messing with the scale of a bar/column chart axis distorts one’s perception of the bar’s length. To avoid the distortion, use a chart type that doesn’t rely on length, the way bar/column charts do. XY charts show data by position of a marker along an axis scale. It is easier for a reader to adjust to a new scale that repositions a marker than one that changes the bar/column length disproportionately.

  4. AlexJ says:

    Chandoo,
    I’m going to suggest that, for readability, numbers in the bars, like for “Total Score”, should be formatted as “Thousands”.
    Also, in this case, I’m thinking that they should be at the left side of the bar, not the right – but I’m not sure if that part is correct.

  5. Jon Peltier says:

    Chandoo -

    Alex makes a good point. Using a “thousands” format would improve readability. Using a consistent font size would also help, especially for us elderly users. (Sheesh, three years away from 30!) I can read all the text in your table except for the data labels.

  6. Chandoo says:

    @AlexJ: Great suggestions there. You are right, I should have used thousands and aligned the labels to left to ensure readability.

    @Jon: The text want that small btw, I had to scale it when taking screen caps to ensure it fits inside the post area. That is why I have added larger version links. But I agree that bigger fonts should be standard. I will ensure that in future the text is much more readable. :)

  7. [...] spending sometime with my initial test cricket statistics dashboard, I have created few alternatives. You can see them [...]

  8. Anne says:

    Chandoo,
    can’t get the data labels inside the bar. The always show right next to the bar. What is the trick there?
    Thanks

  9. Jon Peltier says:

    Anne -

    When you format the labels, there is an option for positioning them. In a bar chart, the default position is “Outside End”, but Chandoo has used “Inside End”.

    Select the labels, press Ctrl+1 (numeral one) to open the Format Data Labels dialog. In 2003 it’s on the Alignment tab of the dialog, in 2007 I believe it’s on the main tab, the first one that opens.

  10. [...] Medals by Country | Survey Results Dashboard | Test Cricket Statistics | Dynamic [...]

  11. [...] am fan of Sachin. Last time he became the highest scorer in test cricket, we celebrated that with a dashboard of test cricket statistics. Similarly, now too, I have prepared an info-graphic poster on Sachin showcasing his achievements. [...]

  12. Vinu says:

    How to highlight the highest bar with different colour.

  13. Chandoo says:

    @Vinu.. In this case I selected the highest bar and manually changed its color to brighter shade.

  14. [...] Sparklines in Excel Excel Dashboard [...]

  15. rajranja says:

    Is there any Formula which can count a particular alphabet from a string??
    Example : if cell A1 = Adsdfdfsfssfwaaasegrggrejgeigjklsfsgjksjgsgsafafsagerogbmdlb” then is there a function which can tell me how many time alphabet “A” is there into cell A1??

  16. Anna says:

    Chandoo,

    How to Protect all cells except scroll bar..??

Leave a Reply