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:
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.
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 firstname.lastname@example.org 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!