Hands on: Excel for simple project management

Whatever job you need to do, in the office or at home, it’s usually beneficial to plan in advance how long it will take and when each participant needs to get involved. This example shows a work schedule for remodelling a kitchen or bathroom using Excel.

The anticipated jobs are entered in the range B4 to I4 - in this case, stripping out the room, plumbing, electrical, plastering, carpentry, flooring, installing fixtures or appliances and decoration.

The number of days each job is expected to take is entered in the cell beneath each job label, along row 5. By laying out the worksheet this way, it makes it easy to instantly change the schedule if any trade falls behind.

The holidays, or any days when work is not expected to take place during the year, are entered along row 8. In the example, it’s the bank holidays for 2009.

The start date of the job is entered in cell B2. Again, if the start day should change, the entire project can be rescheduled automatically.

The only function needed is WORKDAY, which excludes Saturdays and Sundays by default, plus any other days you want to take off work. It is included in later versions of Excel and is in the Analysis Toolpak add-in with earlier versions.

It has three arguments: a start date, the number of days and holidays. In cell B6, enter
=WORKDAY(B2,B5,$B$8:$I$8)

You can enter the holiday range as B8:I8, then in the Formula Bar, click on B8 and press F4 and it will change to $B$8, signifying an absolute reference. Do this again with I8 in the Formula Bar.

This is more important with the entry in cell C6, =WORKDAY(B6,C5,$B$8:$I$8)
when you drag that formula along the row to cell I6, which will read =WORKDAY(H6,I5,$B$8:$I$8)

The references for the first two arguments will change, but the third argument won’t.
Enter the other labels in column A, give all the dates the custom format of your choice, and the job’s done. If you would like to show the total number of days taken for the job overall, enter =I6-B2 in cell K6 and give it the custom format #” days”.

This is a simple worksheet, but it’s flexible enough to allow you to change the start date, the days to omit - such as unexpected sick days - and vary the number of days for any job segment.

Smart tags
Excel users are probably most familiar with Smart Tags appearing when they have an error in a cell or when they paste data into a cell or range. But there’s also an optional version that provides links to other data.

Under the AutoCorrect options is a Smart Tag tab for activating them. In Excel 2007, the keyboard shortcut to the tab is Alt & F then I ,P or go to Office\Excel Options\Proofing\ AutoCorrect Options\Smart Tags.

If you want them to appear, check ‘Label data with smart tags’. In the Recognisers box, the number of items available will depend on the settings on your computer. In the illustration, only Financial Symbol and Person Name are shown, but you may also display others such as Date, Measurement Converter, Telephone Number or Time.

The Smart Tags appearing in your workbook will depend on the items checked here.
Smart Tags can appear with an indicator and a button or a button only. With financial symbols selected as an option, if you type IBM in a cell and click on the automatically displayed Smart Tag, you’re given various choices.

Stock prices shown are from the New York Stock Exchange and are up to date, with only a 20-minute delay.

Microsoft and Worldlingo offer a small 67KB file to download that will help create Smart Tags for accessing translation services and country-specific data about international business practices.

But you’re not limited to links to Microsoft-affiliated sites. Just click More Smart Tags in the AutoCorrect dialogue box and you can obtain software from companies such as Avery for creating labels or Connection Concepts Incorporated’s Dataportal for linking to any enterprise database.

At the risk of infringing on Tim Anderson’s territory, I would make mention of the fact that software developers capable of writing Component Object Model (COM) call-back-style add-ins can create their own Smart tags.

Smart tags are COM dynamic data libraries (DLLs), not scripts. They recognise Microsoft Office security settings. You can get more information from the MSDN website or try Add-in Express.

Cash formats
There are only small differences between the Currency format and the Accounting format in Excel, but once you’ve settled on one, you’ll want to stay with it throughout one document for consistency.

In Excel’s history the Currency format came first. The chosen symbol, like a pound or euro sign, is close to the left of the number. The accounting format displays the symbol to the far left of the cell with space between it and the amount. There’s also a padding space to the right.

Both formats show the fixed number of decimal places that you choose and both insert a comma after the thousands. If you wish to use parentheses instead of a minus sign for negative amounts, you can choose this under the Currency tab of the Control Panel’s Regional Options. It will be recognised by the Accounting format, but not the Currency one.

Another anomaly is that the button on the Formatting toolbar, called Currency, doesn’t offer the Currency format but the Accounting one. This is no problem with Excel 2007, because there’s a box above with a dropdown arrow for quickly selecting formats, but if you have an earlier version, you may wish to correct this.

Enter a number in a cell. Click on the cell and then on the Currency button. On the Format menu, choose Style. Click the Modify button and choose Currency. The button will now produce the Currency format on any worksheet in that workbook.

Better name calling
Imagine having an Excel workbook with lots of formulas that included 17.5 per cent to calculate Vat. And supposing the Government changed the rate to 20 per cent.

Nightmare! But if you had used Vat as a Name in all those formulas, all you’d have to do is change the definition of the Name and everything would be hunky dory.

A Name can be used instead of a reference to a cell or range, or a formula or value. By default, a Name applies throughout a workbook. It can be useful, although to have a Name means different things on different worksheets.

For example, you could have a workbook with four worksheets labelled Spring, Summer, Autumn and Winter. And you might wish to apply a different discount in each of the four seasons.

On the Spring worksheet, for example, the Name ‘Discount’ might refer to 20 per cent. The same Name might mean five per cent on the Summer sheet, 15 per cent on the Autumn sheet and 10 per cent on the Winter sheet. The Vat Name could represent 17.5 per cent throughout the workbook.

In Excel versions prior to 2007, to define a Name for one worksheet all you have to do is precede the Name with the relevant sheet name and an exclamation mark. So, to create the Name for the Spring discount, you would call it Spring!Discount and give it the value of 20 per cent. Excel will store the value as 0.2.

Excel 2007 makes it easier to set the boundaries of Names. It introduces the term ‘Scope’ to define whether a Name applies to a worksheet or throughout a workbook.
To create a Name, you can use the Name Manager: either click on its button under the Formula tab on the Excel ribbon or use the shortcut Alt & M, then N.

Click the New Name button to display the New Name dialogue box. In the ‘Name:’ box, enter Spring. In the ‘Scope:’ box, it will say ‘Workbook’. But there’s a down-arrow that will display all the file’s worksheet names as they appear on the tabs. Select Spring.

In the ‘Refers to:’ box, enter either 20 per cent or 0.2. Click OK. Repeat this simple procedure for the other required Names.

Back in the Name Manager box, the Names will be listed with their values. If you just wish to see the worksheet Names, click on the Filter button and choose ‘Names Scoped to Worksheet’.

You can also display the Names that apply throughout workbook. To show all the Names without restriction choose ‘Defined Names’.

Back on the Spring worksheet, enter 1000*Discount in a cell and it will display 200. Do that on the Summer worksheet and it will display 50.

For all spreadsheet articles click on the tag below