SubscribeSports Dashboards in Excel – A Tutorial
One 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:
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 PollSince 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:
[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.
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
|
Trackbacks & Pingbacks
- Pingback by Sports Statistics Dashboard in Excel - Few More Alternatives | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on June 9, 2009 @ 6:49 pm
- Pingback by A Brief History of Microsoft Excel – Timeline Visualization | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on January 13, 2010 @ 9:47 am
- Pingback by Flu Trends Chart in Excel [Yes, we can edition] | Pointy Haired Dilbert: Learn Excel Online - Chandoo.org on February 24, 2010 @ 6:02 am
- Pingback by Sachin Tendulkar ODI Stats – an Excel Info-graphic Poster | Pointy Haired Dilbert: Learn Excel Online - Chandoo.org on February 26, 2010 @ 9:38 am
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
“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?
@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
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.
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.
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.
@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.
Chandoo,
can’t get the data labels inside the bar. The always show right next to the bar. What is the trick there?
Thanks
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.