Excel Programming for Dummies

For the last 5 years, I've worked primarily in small group settings where I've had the ability to guide athlete decisions on loads for any given exercise. As a result, our program templates rarely included percentage-based load recommendations. This season I took on the added responsibility of serving as the Strength and Conditioning Coach and Manual Therapist for the Philadelphia Flyers Junior Team. This is an exciting opportunity for me, as I missed being in the team setting and going through the grind of a season in pursuit of a common goal. This opportunity also meant that I needed to revamp our program template and get a quick excel tutorial.

After I decided on a general layout for the template, I wanted to set up two things:

1. A name field drop-down menu that auto-populated certain data at the top of the program.
2. Percentage guidelines that referenced the auto-populated data at the top of the program.

As a quick preface, this article is titled Excel Programming for Dummies because I spent a couple hours alternating between google searches, entering faulty formulas, and staring blankly at the screen (majority of time spent here). If it wasn't for the help of my friends working in college settings, Devan McConnell (UMass Lowell), Brijesh Patel (Quinnipiac), Sarah Cahill (Northeastern), and Mike Zawilinski (Northeastern), I'd likely still be pulling my hair out.

I posted the first phase of our in-season program, including the formulas, last week, so you can see exactly how I set things up. Briefly, I wanted to have an alphabetical list of players and their testing numbers on one sheet, which the program template could then pull from. In order to set this up, the first step is defining the name dropdown menu.

If your testing data is on a separate sheet like mine, you'll need to “define” the name list first. To do this

1. Highlight all of the names
2. Click Insert -> Name -> Define
3. Define that list as whatever you want (e.g. “Name”)
4. The “Refers to” section should already be filled with the column you highlighted, but if it's not, you can highlight the column again.
5. Click OK.

Now excel will know to refer to this data when you specify to look for “Name”.

To set up the dropdown menu on the program:

1. Click the cell you want to have the name dropdown menu.
2. Click Data -> Validation
3. Set the Criterion to: Allow “List”; Source =Name (or whatever you titled your list above); and select “Ignore blank” and “In-cell dropdown”.
4. Click OK.

Now you should have a functional dropdown menu with all of your athletes in it. In order to auto-populate their testing data, you'll need to use the “Index” formula, and then fill in the specific criteria for that test. But before you do that, you'll need to define the database, as you did with the athlete list above. This time, highlight the athletes and all of the testing data, and title this “Testing”. This will tell excel to reference this list using the formula below.

If you refer to the program I posted last week (See: In-Season Training Program: Phase 1), let's look at the “Lunge” box.

1. “Testing” tells excel to look at the list that you just titled
2. “Match(B3,names,0)” tells excel to pull specific data from the testing sheet based on the name in the B3 cell, which is the one with the dropdown menu.
3. “2” refers to the column number of the specific test you want to go in that cell.

Moving from left to right, you'll notice that the only difference in the equation is the column number, which corresponds to the testing data you're interested in.

Prescribing loads based on the now auto-populated testing data is fairly straightforward. You could simply highlight the cell you want to put a load into and enter “=LOADPERCENTAGE*TESTINGCELL”. For example, if I wanted to put 50% of a lunge test, which I have in cell E3 on the program, I'd simply enter the formula “=0.5*E3” into the cell and hit enter. The only problem with this approach is that these percentages aren't always round numbers that translate into usable weights (e.g. 238 lbs). You could simply tell your athletes to round up or down depending on your philosophy or give them the option to choose based on how they're feeling.

An alternate option is to use the “Ceiling” function, which rounds up to the next number based on your specification, typically either to the nearest “5” or “10”. Using the same example as above, this new formula would look like “=CEILING(0.5*E3, 5)”. The “5” simply tells excel to round this number up to the nearest 5. If you substitute “10”, it would round up to the nearest 10.

While this is far from an in depth look at all of the ways to use excel, it should provide a base to make printing programs with individualized loads for the S&C coaches working in team settings. If anyone has other excel functions that they use frequently, please share them on the forum!