Happy New Year! Perhaps you made a resolution to run a marathon. I know I did. And I’m going to use Excel to create my training plan.
Many of the advanced features in Excel are best explained by example, so I’ll use marathon training to show you some great tricks, like how to use different kinds of tables for various situations, and how to alter them for your needs By showing you how to use Excel to keep track of a sports routine, you’ll learn advanced Excel features with a simple example, and hopefully it will inspire you to analyze your data more efficiently. Although analyzing data isn’t always the most fun of tasks, this post will help you to see how you can make your data more effective. Using this example, you’ll be able to see other types of data in a new light, by using charts to see the trends and answers you’re looking for.
My town has an annual marathon, and I made a resolution to participate. I want to see how my mileage increases through the weeks, and how much mileage I’ll have for each type of run, so I decided to build my own marathon training plan, which I started below:
Ok, so I definitely have everything prepared on my spreadsheet, but I want to know, visually, how many painful joyful runs I’m going to suffer happily run through. A chart will do the trick! I’m going to turn the data above into this:
To create my chart, I need to decide what I want it to display. It sounds obvious, but spending some time on this question will save time later. Honestly, five miles of marathon pace is much different than five miles of easy runs… I browse the chart types and choose the Stacked Column chart—it’s an easy visual indicator to show how much work the runner has to do each week and how that work breaks down into different categories each week.
Create a Chart
1. Select the data for the chart, from B4 to I23. I don’t want to chart rest days, but we can remove them after creating the initial chart. I’m also not including week 20, because it’s race week.Note: Because Monday and Friday don’t contain numerical data, you’ll see those days in the legend, but not represented in the chart you create. Don’t worry – we’ll remove that later.
2. Click the Charts tab and choose the Column>Stacked Column chart.
Remove the Rest Days
3. Control-click the chart and click Select Data….
4. In the Series box, click ‘Monday,’ then click Remove.
Add Data Labels (optional)
Now we’ll add Data Labels to better understand what we’re looking at. You can skip this step if you’d like, because we’re going to remove the Data Labels later—this is just to demonstrate how to do it.
6. Control-click a Data Series in the chart, then click Add Data Labels. When you select a Data Series, the corresponding data in the spreadsheet will be highlighted.
7. Repeat step 6 with each Data Series in the chart.
Rename the Data Series
But wait! The chart above isn’t showing us what we want to see!
This chart currently shows how much I have to run each day for each week, but not what type of run (Long, Tempo, etc.). We can fix this, though.
8. Control-click the chart, then click Select Data….
9. Click ‘Tuesday,’ then in the Name field, type ‘Easy Run.’
10. Click ‘Wednesday,’ then in the Name field, type ‘Tempo Run.’ In the Y Values field, click the Select Data button to the right of the field, delete the existing text, then click and drag and/or use Command-click to select all the ‘Tempo Run’ data for both Wednesday and Thursday.
11. Click ‘Thursday,’ then in the Name field, type ‘Marathon Pace.’ In the Y Values field, click the Select Data button to the right of the field, delete the existing text, then click and drag and/or Command-click to select all the ‘Marathon Pace’ data from both Wednesday and Thursday.
12. Click ‘Saturday,’ then in the Name field, type ‘Easy Run 2.’
13. Click ‘Sunday,’ then in the Name field, type ‘Long Run.’
Remove Data Labels
We no longer need the values in the Data Labels, since we only want to see the visual data, so let’s remove them. If you skipped adding Data Labels earlier, you can skip this step as well.
14. Control-click the chart, click Format Data Labels, uncheck the Value box, then click OK.
Format the Chart Colors
Let’s make the colors of the chart match the colors I already selected for my data.
15. Control-click a Data Series to open the menu, then click Format Data Series.
16. In the Fill section, select a color that matches the color of the same type of run in the table.
17. Click OK and repeat for each series.
Combine the Easy Runs
I’d rather not see two easy runs each week separated in two chunks. I would like to see them together, because that is the overall time I’m going to be spending running at an easy pace.
18. Control-click the chart and click Format Data Series.
19. Click the Order section in the left navigation, and rearrange ‘Easy Run’ and ‘Easy Run 2’ to be next to each other. Click OK.
20. Finally, click the Legend on the chart, click to select the ‘Easy Run 2’ label, then press Delete.
Our final results have the data with a graph that visually represents how much am I going to run each week and how much of each type of run I am going to do.
This is all for today, but stay tuned to find out how you can use this same data to create an exercise log!
–Marta for the Excel for Mac Team