Sports 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 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:

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

[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