Exercise tracker using Excel. Utilising conditional formatting, data validation and effective formulas to draft a tracker that is useable for all.
Introduction
Recently reviewing social media, I came cross a channel called Easesence, who had made a beautiful tracker logging her finances. Inspired by this post, I attempted to replicate this file, focusing on exercise rather than finance.
I also wanted to explore whether key aspects of the file could be created in to ensure that it performs effectively when used by an individual with limited data literacy.
Overview
The file has several key aspects:
Monthly calendar
Table to list the tasks undertaken
Summary table of tasks
Visualisation of the tasks
Calendar
Whilst Easesence highlights a method of creating a calendar, for the file to be used by an individual with limited data literacy, it should ideally be quick to update.
Following research the following method was utilised.
Using data validation and list to create drop down lists for month and year.
Using the values selected here, identify the month start.
=DATEVALUE(1&Tracker!F4&Tracker!D4)The weekday can also be identified using this date
=WEEKDAY(DATEVALUE(1&Tracker!F4&Tracker!D4),2) Using these two dates the calendar can be drafted though the following formula
=SEQUENCE(6,7,'Exercise Tracker.xlsx'!startdate-'Exercise Tracker.xlsx'!Weekday+1)Improving the visualisation
Although the above functions create the calendar, as the overlapping dates are visible it can be difficult to read. In addition, functions which single out the current date as well as expected days away may be ideal for this type of tracker.
The conditional formatting are:
today's date
=D6=TODAY()Overlapping months
=MONTH(D6)<>MONTH(DATEVALUE(1&$F$4&$D$4))Expected leave
For this function, the user is able to list the dates in the key data tracker and the information will be channelled though
=ISNUMBER(MATCH(D6,'Key data'!$A:$A,0))Summary table
Next task was to create a summary table.
The ideal method would be to create a pivot table. However, to keep sizing correct and ensure that the visualisations adapt to the information, the issue has been tackled from a different angle.
The active table lists all the tasks that the user suspects they will undertake including the rest days.
To track the frequency and the scale of exercise undertaken, two columns log the data.
The user can also use the target column to establish over the month how many times they wish to attempt the exercise and rest days.
Visualisations
The visualisations are relatively straightforward and uses the summary table to compiled appropriate graphics.
There are two graphs.
No of sessions records the variation in exercises. It includes a target line which the user can manually adjust
Minutes spent calculates how much time was spent on each exercise and does not include data concerning rest days. If the user wishes to establish targets for this parameter they can do so with minor alternations to the summary table
It should be said that whilst the original version included a pie chart. Whilst it was considered for this use, it can be complex to read and compare month on month. As such, the more accessible bar charts were used.
Log
The log consists of a table listing all the dates of a specific month. The user access a drop down menu tied to the summary table to select their activity. As some may wish to include details on the exercise undertaken, there is a description cell available to the user.
As rest days do not incur any minutes nor description, when this option is utilised these cells will appear greyed out.
Conclusion
This project explored how an existing idea can be developed and transformed to track tasks. Projects like this challenges one to explore creative ways to tackle key aspects of usability.
It should be said that this project can be developed further. Provided that the vulnerabilities of conditional formatting is overcome, the user can keep a log of month on month of their exercise routine. In doing so, it opens up the opportunity to create a dashboard summarising the entire year of progress.
In addition, there may be other aspects such as weight that one may wish to track.
The file can be accessed here: https://github.com/Lostinworlds/Excel-Templates/blob/main/Exercise%20Tracker.xlsx
ความคิดเห็น