I love doing sports, especially running, but also biking and gym activities. I always record my runs and rides and like to analyze them afterwards in Garmin Connect and Strava. Sometimes, however, the results are obviously wrong. For example, when my heart rate monitor, which is connected to my watch, is running out of battery. In such case, the heart rate simply looks wrong and sometimes even drops down to my resting heart rate – although I’m still moving with the same pace and perceived effort. That’s why I’ve looked for a way to correct the data, especially the heart rate. I have come up with a small Excel tool to correct Strava and Garmin activity data. Let’s take a look.
The problem with wrong data in Strava and Garmin
Don’t get me wrong: I would never use this to cheat. It’s simple that when my heart rate is recorded too low, my VO2max would be evaluated too high. At the same time, Garmin would estimate my calories burned too low. Strava does similar things: If the heart rate is recorded too low, the relative effort and the training progress are analyzed wrong. That’s why I use this tool.
Please see the example above. The red heart rate line is always dropping. The speed, however, remains mostly at a similar level. Changing the battery of my heart rate monitor after this ride has solved the problem. But I would still like to correct the data in order to my get a more smooth and straight line – which is more realistic and resembles my feeling during the ride.
Disclaimer and your responsibility
It’s your data, your profile, your activity and of course your responsibility. So, just one word of warning: Use this tool considerably. Don’t use it to cheat. I hate foul play and like to emphasize the importance of “true sportsmanship.“
Anyway, the data and functionality to download and upload activities is available in Strava and Garmin, so it’s basically just a matter of the users effort to abuse them, if wanted.
That said, let’s get started.
The Excel tool to correct wrong Strava and Garmin activities
Purpose of the Excel tool to correct Strava and Garmin activities
What does the Excel tool do? With the Excel tool (download link below), you can adjust the following parameters of you activity:
- The heart rate: You can correct all heart rate data points by defining the new average and maximum heart rate.
- The time started: This does not happen too often. It’s still quite rare that start times are recorded wrongly. So, if your phone or watch had a wrong time (maybe even wrong time zone?) you can adjust it.
- The total time needed: You can also extend or shorten the total time needed for the activity.
Please note: Strava has a built-in function to correct the distance of your activity. It recalculates the distance with just one click. Read more here.
As you can see in the screenshots above, the changes are quite obvious. Especially correcting the heart rate leads to a much higher “Historic Relative Effort”. Also, start time and total time have changed according to the settings in the Excel tool. Here are the settings:
Interface and structure of the Excel tool
The Excel tool for correcting Strava and Garmin data has three worksheets:
- Instructions: This worksheet only contains a description of how to use the tool. You can also find it below.
- Activity Overview: On this worksheet you can see an overview of your activity, including outlining the route and showing the heart rate chart. On the right-hand side you have the options to adjust the data.
- Data: You paste your GPX data in column A and copy later the results from column B.
Technical background of the tool
I don’t want to say too many words about the technical background of the tool. It regards most basic modelling rules and keeps data separated from the global variables. It works mostly with VLOOKUP(), MIN()/MAX() and IF() functions. I only used functions that are available in Excel 2010 and up (so I haven’t used MINFS()/MAXIFS(), IFS() or XLOOKUP()). Further it has basic buttons, charts and conditional formatting. Besides that – with mainly two worksheets it’s fairly simple and straight-forward. Check it out if you like…
How to use the Excel tool to correct Strava and Garmin activity data
Step 0: Download Excel tool
You need our Excel tool. The download link is provided below. Open it with Microsoft Excel.
Depending if you want to use Strava or Garmin connect data, either continue with steps 1a or 1b next.
Step 1a: Export and download Strava activity data as GPX file
Start with these steps:
- Log in to your Garmin account on a computer. Navigate to the activity you want to adjust.
- Click on the three dots to open the further editing functions.
- Click on “Export GPX”.
- Open the downloaded file with the Notepad on your computer.
- Select everything (for example by pressing Ctrl + A on the keyboard) and copy it (by pressing Ctrl + C on the keyboard).
Step 1b: Export and download Garmin activity data as GPX file
For correcting Garmin activities, log in to your Garmin Connect Account.
- Navigate to your activity. Usually there is a link on the start page after logging in.
- Download the GPX file. Therefore, click on the small gear symbol in the top-right corner of the window (it might also be located somewhere else, depending on your screen size and resolution). Click on “Export to GPX”.
- Open the file, either from your download folder or by directly clicking on the download in the browser.
- Open the file with “Notepad”.
- Select everything (for example by pressing Ctrl + A on the keyboard) and copy it (by pressing Ctrl + C on the keyboard).
Step 2: Copy activity data into Excel tool
Now switch to Excel and go to the “Data” worksheet.
- Select cell A2 on the worksheet “Data”. It should say “Paste GPX data here”.
- Paste data by pressing Ctrl + V on the keyboard.
Excel now probably needs some seconds to extend the table and copy down the existing formulas. The time depends on your computer and the activity length. Please be a little bit patient here…
Please ignore columns C to R. They are used for calculation only and for displaying the data in the charts on worksheet “Activity_Overview”.
Step 3: Correct Strava and Garmin run / ride by adjusting the activity settings in Excel
Go to the settings worksheet.
On the left side, you can find information of your activity.
- The outline of the route mapped in a scatter plot. This is just for your information.
- Basic data of your activity, such as the name, date and time. This data is from your original file and doesn’t reflect your adjustments.
- Heart rate over time. The green dots show the original values, the orange ones the new, adjusted heart rate values.
On the right-hand side you can adjust your activity values.
- Please confirm that you don’t cheat by setting this checkmark. You acknowledge the rules of true sportsmanship.
- In the first block, you can adjust the heart rate. Set the checkmark accordingly.
- Enter the change in points in cell H15. If the average heart rate shown should change from 166.4 to 171.4 (for example), enter “5” here.
- Because in reality the heart rate won’t exceed a certain value (which is your personal value), you can set a maximum in cell I16.
- Your clock or phone was set wrong? In such case, you can change the start time. Enter the offset to your original time in minutes in cell H27. If you’ve started one hour later (maybe because of a different time zone…), enter 60. All time points in your activity will be adapted accordingly.
- Adjust the total length by typing a new length in cell H37 in minutes.
Step 4: Copy new data into GPX file
Go back to the Data sheet. Select the complete column B (“Result”). Fastest would be go to cell B2 and press Ctrl + Space on the keyboard. Now, copy the data by pressing Ctrl + C on the keyboard.
After you’ve copied the data, go back to your GPX file. It should still by open in the Notepad window. Replace all the data there. If the data is still selected from step 1 before, just press Ctrl + V on the keyboard.
Now, save the file. It’s probably a good idea to save it under a new name.
Although you type the file extension “.gpx”, Notepad usually adds another ending “.txt”. You have to remove it manually. In order to do this, navigate to the file in the Windows explorer and just delete “.txt” from the end of the file name. Confirm with “Yes” to next warning message.
Step 5: Upload the adjusted activity to Strava / Garmin Connect
In the last step, you upload the activity to Strava.
Go back to your Strava account and click on the small “+” button in the top right corner of the window. Click on “Upload activity”.
Click on File on the left and then on the “Choose Files” button in the center of the screen. Now, follow the steps on the screen.
If you want to upload the file to Garmin Connect, log in to Garmin Connect and click on the cloud symbol with the small upward arrow in it. Follow the steps on the screen.
Download the Excel tool to correct Strava and Garmin activity data
Please feel free to download the Excel tool here.
Also: Why don’t you subscribe to our free monthly newsletter or support me by following me on social media?
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Add more than 120 great features to Excel!
