Research and Infographics for Athletic Performance, Sport Science, and Health

What is this? It’s a video series where we build a dynamic, interactive team performance dashboard, from scratch! After downloading the dataset below and watching the videos, you’ll have:

An insanely dynamic and interactive team performance dashboard.
Developed a framework in which you can plug in your own data to fit the dashboard to your needs.
Gained a tremendous amount of Excel knowledge, including tips and tricks that will make your life in Excel exponentially easier.

This is an example end product. Created by Adam Virgile.

Dataset Download

Below, you can download the dataset we will be using for building this Ultimate Team Dashboard. I encourage you to download the dataset and follow along!

Video #1: Overview

We review the dataset we use for this video series and go over our dashboard plan. And yes, it’s extremely important to have a plan before trying to build your dashboard!

Video #2: The Big Table

We create the first item on our dashboard, which is one, very important, big table! We also go over a few data housecleaning tips.

Video #3: Dynamic Data

When we change the date and the past X number of days (e.g., 28-day average vs. current day), the data comes along for the ride.

Video #4: Group, There it is!

We learn how to pragmatically apply a cool Excel grouping feature to our table, but more importantly, we discover how to calculate averages and standard deviations with multiple conditions.

KEY NOTE: If your Standard Deviation (STDEV.P) function is resulting in 0 values, try holding down CTRL + SHIFT + ENTER (or COMMAND + SHIFT + ENTER for a Mac) when completing the formula. This would apply for all STDEV.P formulae. If you did it right, you will see squiggly lines at both ends of the formula (it’s an array formula) that look like this { }.

Video #5: Team KPIs

We generate team key performance indicators (KPIs) and annual averages. In prior videos, our focus has been on the individuals (or players), but now we shift our focus to providing a team overview.

Video #6: Conditional Formatting 1

We review a few conditional formatting options, how to dynamically and strategically set up conditional formatting rules, and we begin to apply conditional formatting to our dashboard.

Video #7: Conditional Formatting 2

We apply conditional formatting (e.g., automatic coloring) to the rest of our metrics based on standard deviations. We also calculate dynamic team and “position” averages to provide a table summary.

Video #8: Drop-down Lists and Reference Sheet

We learn how to create a few drop-down menus/lists to use to interact with our dashboard graphs and charts. We also set up a “reference sheet.” More details are provided in the video.

Video #9: Holy Formula! Super Dynamic Graphing

We discover how to write an incredibly powerful (and complex, but fun) formula that allows us to select the measures/variables in our charts/graphs. Make sure you drink your coffee before this one!

Video #10: Creating Dynamic Graphs and Charts

We learn how to create and style graphs/charts for our dashboard and link them to dynamic data sets, giving us the power to easily control the measures/variables within the graph. This is a fun one and, at the end, we really begin to see our dashboard come together, visually.

Video #11:Finishing Touches!

Now that all of our formulae and interactions are complete, we focus on the aesthetics. At the end of this video, you will have a fully-operational, dynamic and “sexy” team performance dashboard. Congratulations!
Videos beneath this one are advancements and alternative strategies to make your dashboard even more badass (pardon my French)!

Video #12 (LEVEL-UP): Adding a Date Scroller

Switching dates by typing them in each time gets annoying. We figure out how to add a scrollbar (which is really a spin button, for good reason) so you can seamlessly switch dates and add extreme functionality to your dashboard.

Video #13 (LEVEL-UP): Super Dynamic Graphing

We make all metrics available to select from for our graphs. In other words, we give ourselves the power to dynamically select any two metrics in our dataset to compare to each other in our graph/chart.

Video #14 (LEVEL-UP): Add/Remove Athletes and Dynamic Drop-downs

We go over how to add and remove individuals from the dataset and visualizations. We also go through how to make dynamic drop-down menus that automatically accommodate for changes made to your dataset.

So, how did it go? I’d love your feedback. Send me an email at adam.virgile@gmail.com to let me know how it went!

If you enjoyed your experience and got something out of it, show off your well-deserved new team dashboard and share the experience with your friends!