Office For Mac

How to train for a marathon Excel style with a Stacked Chart

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.

Click here to download the sample file for this example!

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.

5. Repeat step 4 for ‘Friday’ and click OK. The updated legend should look like the one below:

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

January 17th, 2013
Bookmark and Share
8 Comments
— @
  1. D. W. says:

    In the figure for Step 6: why are only some of the data values in the selected Data Series showing grab handles? i.e. Weeks 7 and 13 don’t have them. Why not?

    A similar thing happens in the figure for Step 7.

    • Office for Mac Team says:

      Thanks for your keen eye! Although the edits will affect the full Data Series, you’re right – the grab handles are inconsistently rendered. I’ve brought this to the attention of our engineers. Thanks again!

  2. Paul says:

    Thanks for Info.

    PCSupportRobo

  3. vero says:

    Great tricks!

  4. bnice says:

    Wow, it nice to see a blog entry again :-) I was starting to worry.

  5. Jean-Yves says:

    Nice tutorial – thanks. I’ve been looking at stacked charts recently for one of my workbooks and this helped clarify some things.

    On a completely unrelated topic – could you let us know whether MS are planning on providing an update at some stage that incorporates an uninstaller that removes the license usage info? The current process is a complete pain abd rarely works – I have 3 licenses on 2 Macs. Both Macs needed now hard drives late last year. On my MBP, both reinstalls worked fine. On my MacBook though, it refused to reinstall. Calling MS activation just results in the line dropping and never getting a new code.
    I’ve ended up having to get another license which is frankly unacceptable – not a single other commercial Mac app that I reintalled had this issue, including apps from Adobe, Apple, AgileBits, etc etc

    I’m not knocking Office – I *love* Office. But you need to supply an uninstall app that saves us form these frustrations. If you are not aware of what I mean, do a web search for Uninstall Office 2011 license and see what others are saying.

    Whilst I am at it: why do I need to use 2 licenses on 1 Mac when wanting to access it via 2 accounts? I don’t have to do that on my Windows copy of Office.

  6. Jeff Talbert says:

    I know you all said there is no word on when the next version will come out, but I do have a question about that as well as another question.

    MS is running a promo that if you purchase Office between now and March 31st, on either a PC or a Mac you get Office 2013 for free. Does that mean that there will be a new version of Office for Mac in 2013?

    Also I work for a large company who is integrating Mac’s into our enterprise environment. We are running into some serious challenges with PSTS. If you import PST’s and your Identity Folder gets to be above 20GB in size Outlook becomes seriously unstable and constantly rebuilds. Is Microsoft Aware of this issue and is this something that a fix is currently being worked on and can possibly see as an update to 2011 or will we have to wait for Office for Mac 2013/2014/2015, whichever date the next version comes available.

    Beyond this serious issue Office for Mac has integrated into our enterprise environment decently. Why does Microsoft continue ignore the serious issues with Outlook on the Mac. How do you think a company can seriously consider using this product in an enterprise environment.