I often have to engineer a workshop or presentation to fit into a few hours with topics divided into segments, and times set up for breaks. That’s just hard enough to do that I sat down the other day and looked up the Excel functions so I could set it up and then shuffle and juggle the different items. Is the break two early? Change it. Does somebody say you need to change the order, or change the time allotted for the session? Change it. With the right spreadsheet, it’s a lot easier. You don’t have to retype.

It depends mainly on one formula with nested time-related functions. Look at the illustration here first, and you can assume that once it’s done, then as you type the minutes in column C and the titles in column A, the start times will adjust automatically. If you insert a new row, you have to use the fill down feature to copy the formulas again from B2 through the last row. To make your own, you can follow my step-by-step directions below. And I’ve put a simple one, that you can modify, where you can download it (see my last paragraph).

1. Just type the starting time as a time. I typed it into cell B1 as 1:00 PM. And with that entered there, you can change the whole thing just by entering a different start time. Just type the time. Excel understands that 1:00 PM is a time.
2. The important part is the formula to cell B2. I’ve highlighted it in yellow. Make that formula as shown there and it will show you a strange number. Don’t worry; take the next step.
3. With that cell highlighted, go to your Format Cells command (there are multiple ways to get there; check help if you have to) and change the Number format to show up as h:mm AM/PM as you can see here to the right. You’d think that would be in the Time Category, but it’s in custom.
4. Then you just fill in the sections in column A and the times allotted in column C, as shown, and you copy that formula in cell B2 down to the other cells in B3 through (in this case) B9. And you’ve got something you can work with now.
5. By the way, I like that last calculation, in cell C9 where I’ve highlighted it in the main worksheet illustration above, because it tells me exactly how long the event will be (that is, if I stick to my timing plan). Its formula is =B9-B1. And its format is “h:mm” from the custom format list we’ve already shown you.

You can use it with PowerPoint too.

You often want that schedule or agenda as part of a presentation. For that, you can open up an Excel object in PowerPoint and set it to show just the first two rows. You can see an example here. PowerPoint opens up Excel and I do the same as above, but I set the fonts larger. It’s also interactive, since it’s just embedded Excel, so you have the same functionality. To the right there’s an example of the resulting slide.

I’d like to give you more details about how you work with the Excel object in PowerPoint, but that’s too much detail for this post. But the executive summary is that you find the feature to insert an object into the slide, and then a dropdown menu lets you choose among objects including an Excel worksheet. And then you use the same formulas and functions and formatting as above. And if you insert a row, you have to adjust the formulas in the second column with a fill down command.

By the way, if you’re interested, I’ve left a simple version of that XLS file in my public dropbox where you can download it. To do that, just right-click this link and use your browser’s commands (usually that’s save as, but it depends on the browser) to save it to your disk as an Excel file, then run it.  Change it, modify it, it’s yours to use and enjoy.