Menu
Excel Dashboard Video Tutorial – Crash Course from Chandoo.org
- 0 comments
Friends & Readers of chandoo.org, I am very glad to announce my brand new video training on Excel Dashboards:
Excel Dashboard Video Tutorial
What you will learn in this course?
You will learn how to create three different dashboards shown above. Click here to see the enlarged images of the dashboards.
Who is this course for?
This course is very useful for anyone making dashboards, reports or using excel to generate charts that will be read by larger audience.
You will not enjoy this course if you are an absolute beginner of Excel.
What do you get with the course?
This course is an instant download. You get the following with it.
- 3 Dashboard Tutorials on,
- Creating a static website dashboard – 65 minutes
- Creating an interactive dashboard (non-VBA) to report a forum statistics – 45 minutes
- Creating an interactive dashboard (VBA) to report Cricket Worldcup statistics – 35 minutes
- You will learn these concepts from the lessons:
- Dashboard design principles
- Data arrangement & cleaning
- Using Pivot Tables to summarize data
- Word clouds in Excel
- Using Form controls to add interactivity to dashboards
- Using VBA to add interactivity to dashboards
- Using Picture links & conditional formatting to make an impression
- You will get 5 unlocked Excel Files,
- Website Dashboard – 2 files
- Interactive Dashboard (Forum Statistics) – 1 file
- Interactive Dashboard (World-cup Stats) – 1 file
- On-Demand charts in Excel – 1 file
- FREE Bonus: 90 minute lessons on Introduction to Excel
- FREE Bonus: 40 minute lesson on interactive charts with VBA
- 30 day money back guarantee
How much is this course?
The course is priced at $47.
Get a copy of Dashboard Tutorial Crash Course
Alternative link for payment (accepts credits cards and local currency payment too)
How does the purchase work?
Simple. Once you make the payment, you will receive an e-mail with download link. Just download the file. It is rather heavy (150 MB), so give it sometime to download.
Have questions?
Call me at +1 206 792 9480 or +91 814 262 1090 or E-mail me at chandoo.d@gmail.com. I will be able to help you right away.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
Sports Dashboards in Excel – A Tutorial
Charts and Graphs , Learn Excel - 20 comments
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
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
20 Responses to “Sports Dashboards in Excel – A Tutorial”
-
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? -
@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.
-
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. -
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. 🙂
-
[...] spending sometime with my initial test cricket statistics dashboard, I have created few alternatives. You can see them [...]
-
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 -
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.
-
[...] Test Cricket Statistics in an Excel Dashboard [...]
-
[...] Medals by Country | Survey Results Dashboard | Test Cricket Statistics | Dynamic [...]
-
[...] 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. [...]
-
Vinu says:
How to highlight the highest bar with different colour.
-
@Vinu.. In this case I selected the highest bar and manually changed its color to brighter shade.
-
[...] Sparklines in Excel Excel Dashboard [...]
-
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??-
@Rajranja... use =LEN(A1)-LEN(SUBSTITUTE(A1,"A","")) to count the number of As in a cell
-
-
Anna says:
Chandoo,
How to Protect all cells except scroll bar..??
-
Adnan Ahmed says:
Good Article
-
Abdullah says:
* I want to know the formula which gives me the list in new column by selecting xxx items from column A based on expire date in column B.
Leave a Reply
| « Excel Links of the Week – Birthday Edition | Reader Poll: Should the axis for bar charts always start at zero? » |
Meet Chandoo
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. Read my story • FREE Excel + Power BI Newsletter.
Connect:
Chandoo.orgAwesome stuff for you
Recently on Chandoo.org
- How to calculate WEEKNUMBER in Month / Quarter / Year with Excel?
- Automatically Format Numbers in Thousands, Millions, Billions in Excel [2 Techniques]
- How to compare two Excel sheets using VLOOKUP? [FREE Template]
- Sales Analysis Dashboards with Power BI – 30+ Alternatives
- Announcing Power BI Dashboard Contest (win $500 prizes!)