Select Page

Quite recently I was teaching a class, afterwards I got chatting to one of the participants and it turned out that she does a lot of coaching with runners. Of course I picked her brains about some stretching techniques and she gave me some great tips about pre-running stretching and then she asked me about using Excel to track the performance of her athletes. So this is what I came up with…

## Part 1 – the setup

• Name
• Date
• Hill Time
• Hill & Recovery Time

I began by entering the headings and some dummy data and converting the whole list to a table (Ctrl and T or Insert – Table)

The Hill Time and Hill & Recovery Time had to be entered as time (minutes and seconds). That was done by using the Format Cells option. I did that by highlighting the column and then pressing Ctrl and 1 to get the Format Cells option.

1. Highlight the column for Hill Time
2. Press Ctrl and 1 to get the Format Cells option. (I show you another way to get this in the video below)
3. Click on Custom on left
4. Locate the following format ([h]:mm:ss) – without the brackets…if it’s not there, click on one of the Time options (some variation of hh:mm:ss) and enter the [h]:mm:ss format
5. You need this format so that Excel will read your entries as minutes and seconds.
6. Repeat for Hill & Recovery Time column and Recovery Time column

Note that when you are entering the actual times in these two columns you will have to enter it as follows: 0:12:34 (using a colon to separate the hours, minutes and seconds)

Once you have that done, next step is just to enter a formula in the Recovery Time column to subtract the two times from each other. Because the list has been formatted as a table, as you enter the two times, the answer will appear automatically.

## Part 2 – Further analysis

It then occurred to me that it would be interesting to see if there was any patterns to this data (even though it was just dummy data) so I applied a pivot table to it and then a line chart from that.Of course this threw up a few interesting anomalies and dilemmas and you can view them in the video below..

The first steps for creating a pivot table are here.

## Completed File

View completed file here