Technology Q & A.

Q. Some years ago, when I used WordPerfect (my favorite word-processing software), I could print watermarks (light print-overs) with words like DRAFT or CONFIDENTIAL that did not cover up the text beneath. I would like to do the same with the spreadsheets. At work, I prepare multiple spreadsheet "scenarios"--that is, business proposals with different cash flow, sales, expense and profit premises--and when I distribute them I want to be sure the readers know these are drafts--not final statements. I could, of course, write the word "Draft" across the top of the sheet, but it looks more professional when the word is printed. But, I can't get the watermarks to work in Excel. Even if it looks right in Print Preview, it doesn't print correctly. Can you help?

A. No wonder you can't figure it out. For reasons I can't explain, Microsoft makes it a challenge. Actually, there are two ways to do it.

Your first is to print the watermark the easiest way: outside of Microsoft Excel, through your printer drivers. To get to the printer driver, click on Start, Settings, Printers and then right click on your default printer icon. Browse around that screen and search for the fields Effects or Watermark. If your printer driver doesn't show those fields (and many older drivers lack them), you've got to go to the second option--a slightly more complicated method, which, however, gives you more control over the appearance of the watermark.

On the toolbar, click on View, Toolbars and then on Insert WordArt, which brings up the WordArt Gallery toolbar (see exhibit 1, below). Click on the far left icon (Insert WordArt) and pick any one of the single-color styles. Click OK and then, in the text area, type in your watermark text, adjusting the type size and font (controls are on top of the WordArt Gallery screen).

[Exhibit 1 ILLUSTRATION OMITTED]

Next, click on the Format WordArt icon, which brings up a screen (see exhibit 2, below), and under Fill, Color, pick No Fill or, if you have a color printer and you want to print it in color, pick a light shade and then check the Semitransparent box. Now click OK and then, with your mouse, move the type to the location on the spreadsheet where you want it to appear (see exhibit 3, page 78).

[Exhibits 2-3 ILLUSTRATION OMITTED]

If the spreadsheet contains multiple pages, copy (Ctrl C) the watermark and paste (Ctrl V) it in position on each page.

Q. I work on many Excel workbooks at the same time. Occasionally I'll have as many as 12 open. Closing the workbooks without closing Excel entirely is a tedious job because I have to close them one at a time. I heard there's a quick way to shut all of them with a single click. Is that a myth?

A. No, it's a little-known shortcut. Here's how you do it. Go up to the File menu. Notice that one command is Close, which, if you click on it, would close just one workbook. But, if you hold down the Shift key while evoking the File menu, see how the Close command changes to Close All--the command that shuts all your open workbooks in one step.

Q. When I work on spreadsheet projects I frequently have to calculate the number of days between two dates and then use that figure in a later calculation. Can Excel calculate the number of days for me? It would save lots of time.

A. Yes, it can, and the formula is easy to use.

= end-start

For example, if you want to calculate the number of days between June 5 and June 1, the formula would be

= 6/5/2000- 6/1/2000

But be careful: There's a not-so-obvious step here you need to take. The cells in which the start date and end date are entered must be formatted as dates (to set that format, right click on the cell; then click on Format Cells, Date), and the cell in which the answer appears must be formatted as Number.

By the way, if you're working on several date-difference formulas, you can make the work easier by using labels for the dates (such as "End" and "Start") in the formulas rather than alphanumeric cell addresses. For example, if you label one column End and the next one Start (see exhibit 4, page 80), the above formula will work perfectly. Otherwise the formula would have to read: = C3-B3.

[Exhibit 4 ILLUSTRATION OMITTED]

Now, if you try this trick and discover that the labels won't work in the formula, your Excel is probably not defaulted to accept labels. To correct that, open the Tools menu in the toolbar and click on Options and Calculations. Under Workbook options, check the Accept labels in formulas box. You'll probably have to close Excel and reopen it before the new default is evoked.

There are many ways to use the various Excel date functions. For example, you have a December 31, 2000 deadline for completing a project. You can use the =DATEVALUE and NOW() functions to tell you how many calendar days there are between the day you open the workbook and the future deadline (see exhibit 5, above). The formula would look like this:

= DATEVALUE ("12/31/2000") - NOW()

[Exhibit 5 ILLUSTRATION OMITTED]

Once again, there are some not-so-obvious steps you need to take for the formula to work correctly. You must format the answer cell to Accounting, remove the $ symbol and set the decimal places to 0.

Do you have a technology question for this column? Send them to Senior Editor Stanley Zarowin via e-mail at [e-mail] or regular mail at the Journal of Accountancy, Harborside Financial Center, 201 Plaza Three, Jersey City, NJ 07311-3881. We regret that we cannot answer letters individually.