By Annette Marquis, TRIAD Consulting

Your monthly department meeting is scheduled for next Monday from 1:00 P.M. to 2:30 P.M. Six people have already given you agenda items for the meeting, with more expected. Your supervisor is adamant that everything must get covered and that the meeting must end on time. It's your job to compile the meeting's agenda and balance the time allocated to each item.

In this article, you'll build a simple Microsoft Office Excel 2003 worksheet that automatically manages start and end times, even when you have to make adjustments.

To start, create the headings you want to appear at the top of the printed worksheet. These might include, for example, headings such as Title, Location, and Date. Then enter column headings for the agenda itself. Enter the first three column headings: Start, End, and Time (or Length). After that, it's up to you to include what you'd like. Other additional column headings might include Topic (or Item) and Contact (or Presenter). At this point, your Excel worksheet should look something like the following illustration.

Entering formulas

When this worksheet is functional, you enter a meeting start time in the first cell of the Start column. You then enter how much time each item is scheduled for in the Time column. Excel calculates the remaining start and end times for you. If you adjust the length of an item in the Time column, Excel recalculates all of the other start and end times. For this to work, you need to enter formulas in the Start and End columns. Follow these steps to format the worksheet cells for time entries and to enter the formulas:

1. Select the Start, End, and Time cells in the first row of the worksheet (in the previous illustration, that would be cells A8, B8, and C8).
2. On the Format menu, click Cells.
3. On the Number tab, click Time in the Category list.
4. In the Type list, click 1:30 PM, and click OK. The cells are now formatted to display time entries.
5. Click the first cell in the End column, and enter this formula:

   =IF(ISBLANK(C8),"",A8+C8)

   The formula adds the value in the Start column (cell A8) to the value in the Time column (cell C8) to display an end time. The IF function assures that the cell in the End column is blank if there is no entry in the Time column.

    Note   If you set up your worksheet differently from the one shown in the previous illustration, adjust the cell references accordingly.

6. Click the cell in the second row of the Start column, and enter this formula:

   =IF(ISBLANK(B8),"",B8)

   This formula copies the calculated end time of the previous row to the Start column of the next item, and the formula enters a blank value if the Time column is blank.
7. Copy (or fill) the formula in cell B8 to cell B9.

To test the worksheet, enter a start time, such as 1:00 P.M., in the first cell of the Start column. Enter a time value in the first row of the Time column; for example, enter 0:30 to indicate 30 minutes. Be sure to include the zero and the colon to indicate that it is a minute value. 1:30 PM should appear automatically in the End cell in the first row and in the Start cell of the second row.

Assuming that most meetings have more than two agenda items, you'll want to add formulas to subsequent rows. For this task, use an Excel list.

Converting the agenda to a list

Because formatting and formulas are so important on the worksheet, you don't want to encounter problems because a cell is not formatted as time or doesn't contain the appropriate formula. The best part of an Excel list is that it doesn't require you to know how many rows you need in advance in order to copy formatting and formulas to needed rows — the list automatically adds the appropriate formatting and formulas to the new rows, as you need them.

To convert the agenda to a list, follow these steps:

1. On the Data menu, point to List, and then click Create List.
2. Verify that Excel has selected the correct range and that your list has headers, and then click OK.

Now that the agenda is a list, a blue border appears around the cells of the agenda. This blue border does not print — it's there for your benefit only. In addition, the list automatically inserts a new record row, designated by the asterisk. The start time does not automatically appear in this row, and if you click the cell, it does not display a formula. However, as soon as you enter a value in the Time column, both the start and end times are displayed on the worksheet.

 Note   Be cautious when inserting rows into the middle of the worksheet. Formulas might not appear as expected. Double-check all of the relevant formulas and correct any problems before proceeding.

Adjusting times

After you get all the topics listed with their approximate times, you may find that you overshot the meeting's scheduled end time. To make adjustments in the schedule, just change the values in the Time column until you hit your mark. All of the other start and end times are adjusted accordingly.

Protecting the worksheet

If you plan to distribute the agenda for others to review and revise, it's a good idea to protect the worksheet before you send it out. Before you do protect the worksheet, select the cells that you want others to be able to modify (any cells that don't contain formulas) and unlock them. To unlock cells, on the Format menu, click Cells, and click the Protection tab. Then clear the Locked check box and click OK. To turn protection back on, on the Tools menu, point to Protection, and then click Protect Sheet.

 Note   With protection enabled, you are not able to add rows to the list, so make sure that you include all of the topics or add rows to accommodate additional topics before you protect the worksheet.

Finally, while you are working on an unprotected worksheet, you might want to add some shading or a pattern to the cells that contain formulas. This will keep you from overwriting the formulas by mistake. Just be sure to clear the formatting when you are ready to print the agenda for others.

Reusing the worksheet

To reuse this agenda worksheet, delete the numbers in the Time column and delete the numbers in the first cell of the Start column (you have to turn off protection to do this). This clears the Start and End time columns. Then delete any other meeting-specific information, topics, and contacts.

You might also want to delete additional blank rows. If you do this, don't forget to leave the first two rows, because that's where the formulas are.

When the worksheet is clean, save it as a template so that you can reuse it without having to clean it up each time.

About the author   Annette Marquis is a partner of TRIAD Consulting, the premier Microsoft Office System training firm for the International Association of Administrative Professionals (IAAP).