How exactly do you go about gathering training and athlete data? Excel compiles data using tables to make searching for it much simpler. You can quickly find and filter information using them.
Creating a Table
You must offer a framework before you can begin creating, analysing, and filtering. The first step is to add headers for each item you plan to track. I'm going to make it simple by using the Strength Coach Nause multi-athlete lift log book as an example. These are how the headers appear.
Date achieved
Athlete name
Lift performed
Reps achieved
Weight lifted
Estimated 1RM (this column will actually contain a formula to auto-calculate e1rm)
Simply type your headers into the first row of your spreadsheet.
Now that you have your headers, you can make a table.
Select all your headers + 10-20 rows
Click Insert > Table
Tick "My table has headers"
To make life easier, you can name your tables. This will make it much easier to reference them within your functions later on.
Click "Table" on the top menu
Type your desired name into the box (no spaces)
Press enter
Inputting Your Data
The wonderful thing about tables is that they typically recreate any changes you make to a cell for a column. This could include adding a drop-down menu; a great way to add athletes' names and exercises (see our blog post on drop-down menus).
When it comes to the estimated one rep max, I typically use the Brzycki (1993) equation:
Estimated 1 Rep Max = Weight used ÷ (1.0278 -(0.0278 × Reps))
To add this to the column, I am going to use the SUM function and simply set the weight and rep column as cell references.
Insert Picture
Press enter and you should see it auto fill the rest of the column.
Understanding how to create your tables and input data is one thing. Now you can begin to reference them in formula and build out responsive dashboard and analytic tools.
Comments