During the summer of 2022, I developed this symptom tracker template while participating in an early-stage health study. The study involved wearing a Fitbit Charge 5 24/7 to collect various metrics including heart rate and blood oxygen levels.

Why I designed this template

The goal of this phase of the health study was to research long COVID and other chronic fatigue disorders. My data made it to the research team as raw sensor data, which they could visualize using charts and graphics. However, this didn’t give them any context for internal and external factors, including:

• “Rolling the dice” for my chronic illness symptoms. Symptoms are unpredictable, even on good days.
• My own perception of how well and how long I slept
• Environmental factors: weather, humidity, smoke from wildfires, air pressure
• Social factors: gatherings and events that take a significant amount of energy and need a recovery period
• Hydration
• When and what I ate
• Work and personal stressors

Raw data tells one story, but my lived experience provides more complete information for the data analysts.

Frustrations with fitness device algorithms

Devices such as the Fitbit and Apple Watch work fantastically… for most people. People like me often don’t get useful feedback from them. Various sleep disorders (insomnia, light sleep, poor sleep, etc) skew data and I often find myself confused by my reports. How can it say I got seven hours of sleep when I distinctly remember laying awake most of the night? Wrist-based monitors simply don’t collect enough of the correct type of data to accurately measure sleep.

My symptom tracker template allows us to see everything all in one place, and puts equal value on qualitative experience and quantitative data.

Logging more complete data

For the duration of my time with the study, I logged every single non-sensor metric I could think of. This was decidedly tedious; I had to remember to update the spreadsheet multiple times per day. If I forgot, I had to remember or reconstruct previous data, which meant it was less accurate. Once I got into a routine, though, it became easier.

Gaining a better understanding of my own health

Conditional Formatting

I used Google Sheets’ Conditional Formatting feature to dynamically color cells based on their value relative to the rest of their row. This allowed us to see trends over time within a single metric, as well as relationships between metrics. By assigning colors to values, I turned each month’s symptom log into a heat map.

Each metric had its own range. For some, high numbers were good, and for others, high numbers were bad. I switched the coloring on these to better represent the data range.

Color accessibility

Changing the color palette for impaired color vision is simple and straightforward. I can either have the user select two colors they can easily distinguish, or simply make it grayscale. This template is flexible by design for this exact reason.

Identifying trends in my data

One of the first things I noticed was that I typically log more than enough exercise to meet the American Heart Association’s recommended exercise minutes (roughly 150 minutes per week). However, it hasn’t had a noticeable impact on my resting heart rate, heart rate variability, or estimated VO2max. This makes sense, given what I know about my chronic illness; connective tissue disorders and chronic fatigue often have this effect on patients.

I also confirmed that external events such as stressful political news, high-energy social gatherings, and other tiring situations have a noticeable impact. My fatigue and pain levels were worse in the days following, but this often didn’t show in my raw data.

Blood donation and its impact on my data

I chose to donate blood during the study (and notified the research team first), but unfortunately I had a mishap where my draw site started bleeding again and I nearly lost consciousness. This did show up in my data, most noticeably in my resting heart rate and blood oxygen levels for several days. I also reduced my exercise intensity while my body replenished the blood I’d donated (mostly so I didn’t pass out while walking).

Why I use this spreadsheet instead of an app

There are absolutely apps that do this for you, for both Android and iOS. They usually have more functionality as well, namely tracking dietary triggers for symptoms down to base ingredients.  But they can also be overwhelming, easy to forget to update, and results are often buried in menus that can be hard to navigate.  The spreadsheet I designed isn’t as powerful as many of the symptom tracking apps, but it meets my needs and I find it easier to use. It gives me the data I need in a simple dashboard, and helps me make better decisions about my health.

Using this template for yourself

After opening my Google Sheets symptom tracker template, use File > Make a copy to save a version into your own Google Drive that you can edit and use as you see fit.  Conditional formatting will automatically highlight cells based on where they’re positioned within a range from 0 to 10; sleep is rated with 10 being best, whereas tiredness and symptom ratings have 10 being the worst. Sample data to help show how to use the template are in a Sample sheet, and there’s a clean Template sheet ready for you to modify for your own needs.

The conditional formatting will help visualize trends and patterns in your symptoms, and hopefully help you make more informed decisions about your own health too.

Please reach out if you have any questions or feedback!