Timing your training – why you should build your agenda in Word (not Excel)

Posted on by

Close-up of a watch face resting on a portrait photo of a young Paul McCartney, from The Beatles, whose eyes seem to be looking at the watch.

(In a rush?
Jump to the tips)

Suppose you’re putting together your agenda for a workshop or training session.

If it’s pretty simple, your agenda might look something like this:

Topics Mins
Welcome 5
Icebreaker 5
Main content 75
Wrap-up 5
End: 1 hr 30 mins

In this post, you’ll see:

  • How to make Word (or Outlook) calculate your session’s length, as shown above.
  • The top 3 reasons why you might use Word or Outlook for that, rather than Excel.

And in my next post, you’ll see how you can take that further by making Word or Outlook tell you each topic’s start time.

 

BackgroundScroll to Contents ↓

Let’s take a step back for a moment. How do you adjust your session’s timing?

Do you start with a total length in mind, then tweak each topic’s timing – until it all fits?

Or, perhaps you do the opposite:

  • You assign a length to each topic, based on its content.
  • Then you find what the total duration comes to.

Either way, you might change the timings a lot while you develop your agenda – especially if there are many topics.

So, to avoid repeatedly having to add up the times yourself, you might put your agenda into Excel. (Which of course was made to add up numbers!)

But rather than using Excel, I suggest you use Word (or Outlook) to build your agenda – for these 3 good reasons:

  • Word and Outlook are surprisingly good at simple maths. (That is, if you use the desktop version – not the browser or mobile versions, and not Word in Teams.)
  • Being built for writing, Word and Outlook help you easily integrate your agenda into messages to your audience, or to other stakeholders.
  • They make shuffling the order of your agenda items far easier than Excel does. (See How to easily reorder your topics, below.)

Let’s explore using Word to work with an agenda for a ½-day workshop. In this case, each topic has timing, and we’ll make Word work out the total time for us.

You’ll find these topics below:

 

Wanna experiment? Copy this sample agendaScroll to Contents ↑

To follow along, start by copying the agenda below into Word’s desktop app.

Note: For the steps in this post to work, your agenda must be in a table.

To copy the agenda below, drag horizontally from about 1 cm (½ in) left of the table, until you’re to the right of the table – when your browser selects all rows. Then press Ctrl+C.

When you paste the agenda into Word, if it’s just a line of text instead of a table, please select the agenda again in your browser, this time dragging from further left.

Topics Mins
Welcome 5
Icebreaker 10
Topic 1 15
Exercise 1 30
Debrief 5
Break 10
Topic 2 30
Exercise 2 60
Debrief 10
Q&A 10
Wrap-up 5
TOTAL  

 

Calculate the total time – in almost 3 clicks!Scroll to Contents ↑

To work out the total time, you can use just 3 simple steps:

  1. Click in the empty cell at the bottom of the Mins column.
  2. From the ribbon, choose (Table) Layout > Formula.

    Note: This only works in Word’s desktop app – not the version in your browser or in Teams or on your phone.

    Word opens the dialog box below, and suggests the formula =SUM(ABOVE).

    3. The Formula dialog box, showing a SUM ABOVE formula.

  3. To accept the suggested formula, choose OK.

That’s all there is to it – as long as you note these constraints (all of which you’ll see how to overcome, later in this post):

  • If you change the timings, the total will be wrong until you update it.
  • The Mins column can only contain numbers, with no blank cells.
  • The total’s shown in minutes (190 in our case), rather than as something more readable, like 3 hr 10 mins.

 

How to update the total (after timings change)Scroll to Contents ↑

As I mentioned, if you change the timings, Word won’t automatically update the total.

The simplest fix? Manually update the total when needed:

  1. Select all text in the total time cell.
  2. Either press F9, or right-click the selected text and choose Update Field.

 

How to easily reorder your topicsScroll to Contents ↑

Luckily, shuffling your agenda is a breeze in Word or Outlook!

To move one or more rows up or down in the table, just click anywhere in the row (or select some text in several adjacent rows) and then press Alt+Shift+Up Arrow or Alt+Shift+Down Arrow as many times as needed.

 

Why you might want to add “blank” cells – and how to do soScroll to Contents ↑

Because the sample agenda above is quite long – and visually uniform – it’s not very readable. So let’s add some blank cells to break it into digestible chunks.

For instance, adding a blank row before and after topics 1 and 2 makes it all much easier to read:

Topics Mins
Welcome 5
Icebreaker 10
 
Topic 1 15
Exercise 1 30
Debrief 5
 
Break 10
 
Topic 2 30
Exercise 2 60
Debrief 10
 
Q&A 10
Wrap-up 5
TOTAL  

However, that causes a problem when Word adds up the numbers in the Mins column. Namely, Word stops at the closest blank cell to the formula (or the closest cell that doesn’t contain just a number).

Want to see what I mean? Delete one of the times in your sample agenda, leaving the cell blank, and then update the total.

In our example, Word now adds up just the length of the Q&A and wrap-up, making the total 15 mins – instead of over 3 hours!

To fix that, rather than using completely blank rows, you can put a 0 (zero) in each “blank” cell in the Mins column.

To make those cells look blank – for aesthetics or readability – you can colour each 0 to match its background.

Note: Another way to hide the zeros is to format them as hidden text (by selecting them and pressing Ctrl+Shift+H). If you do that though, the formula treats them as blanks, making the total incorrect. That’s why I suggest colouring each zero to match its background instead.

 

How to show the total in hours and minutesScroll to Contents ↑

Up to now, we’ve been using Word’s default behaviour, which is to show the total as minutes. (That is, 190 in our case.)

But if your session’s over an hour long, a total in minutes can be quite hard to interpret. So it’s better to show the total in a format like 3 hr 10 mins instead, which we’ll do next.

Because that format contains 2 numbers, we’ll need 2 formulas:

  • In the 3 hr 10 mins, the 3 is the number of whole hours in the total.
  • The 10 is the number of minutes left over after subtracting those 3 hours (or 180 minutes) from the total of 190 mins.

So, to convert the total time you inserted earlier to hours and minutes:

  1. Click in the total time at the bottom of the Mins column.

    Note: As these steps are quite involved, please follow them closely. If they don’t work, feel free to leave a comment, and I’ll try to help.

  2. From the ribbon, again choose (Table) Layout > Formula.

    Note: As before, this only works in Word’s desktop app – not the version in your browser or in Teams or on your phone.

  3. Delete all the text in the Formula box.
  4. Copy the following line, and paste it into the Formula box as shown below: 
    =INT(SUM(ABOVE)/60)

    The Formula dialog box, showing an INT (or integer) formula.

    That divides the number of minutes by 60, which converts it to a number of hours. Then the INT part rounds down the result to the nearest whole number of hours.

  5. Choose OK.

    If all’s well, in our example Word will display the number 3 (meaning 3 hours).

  6. Copy the number of hours to the clipboard for later reuse.
  7. To the right of the number 3, type a space followed by the abbreviation hr and then another space.

    The total should now say “3⚊hr⚊” (where “⚊” represents a space).

  8. To the right of the 2nd space, paste the number you copied.
  9. Select that number (which you just pasted at the end of the line).
  10. Choose (Table) Layout > Formula.
  11. Delete all the text in the Formula box.
  12. Copy the following line, and paste it into the Formula box as shown below: 
    =MOD(SUM(ABOVE),60)

    The Formula dialog box, showing a MOD (or modulus, meaning remainder) formula.

    To confirm, this step replaced the letters INT in the previous formula with the letters MOD (standing for modulus, meaning remainder), and replaced the slash (/) with a comma (,).

    This formula calculates the number of minutes left over when the number of whole hours is subtracted from the total time.

  13. Choose OK.

    In our example, Word should now display the number 10, so the total time should say “3 hr 10”.

  14. To the right of the 2nd number, type a space followed by the abbreviation mins.

    The total time should now say “3 hr 10 mins”.

Great job! You’ve done well to get to this point 🙂

After you change any timings, you can still update the total in the same way as before.

 

Over to youScroll to Contents ↑

Did you try working with the sample agenda in Word? How did you find it?

Or, maybe you use an entirely different approach to building your agenda…

In any event, by all means share your thoughts in the comments.

Using the steps above, I find Word very easy to work with when building an agenda. Mind you, I’ve been using Word for several decades – for more than ½ my lifetime! So I’d love to hear your viewpoint.

 

Also check out…Scroll to Contents ↑

What’s YOUR view?

This site uses Akismet to reduce spam. Learn how your comment data is processed.