Make your mark in spreadsheets.

What do you do if you need to include explanatory comments on a complex spreadsheet, or if your audit client has given you a schedule on a spreadsheet and you want to document the audit procedures? Fortunately, Excel contains tools that perform such chores, and this article shows you how to use them.

As you know, it's possible to attach an electronic comment to any cell in a spreadsheet by placing your cursor in the target cell and clicking the right mouse button; then, in the menu that comes up, click Insert Comment (exhibit 1).

[ILLUSTRATION OMITTED]

A box instantly appears addressed with your computer's default user name. Simply type your comment in the box, using typical word processing commands (exhibit 2). You can adjust the size of the box by dragging any of the eight small boxes on its edges.

[ILLUSTRATION OMITTED]

When finished, click outside the comment box and it disappears, replaced by a small red triangle in the upper right corner of the cell, signifying an attached comment. If you plan to add many comments, it's more efficient to activate the Reviewing toolbar by clicking View, Toolbars, Reviewing (exhibit 3). The toolbar then hovers on the screen and is available for, among other things, creating new comments, editing existing ones and reading one after another.

[ILLUSTRATION OMITTED]

You'll notice that once a comment has been entered, the New Comment button on the toolbar changes to Edit Comment, allowing you to alter the text and resize and move the box. To delete a comment, simply click on the desired cell and the Delete Comment button.

Here's what each command function of the Reviewing toolbar does:

Button    ScreenTip        Function

??        New Comment      Enters a new comment in the
                           active cell.

??        Edit Comment     Edits text and reshapes an
                           existing comment; replaces New
                           Comment icon when the active
                           cell contains a comment.

??        Previous         Finds and edits the previous
          Comment          comment.

??        Next Comment     Finds and edits the next
                           comment.

??        Show/Hide        Shows or hides the comment in
          Comment          the active cell.

??        Show/Hide All    Shows or hides all comments on
          Comments         the worksheet.

??        Delete           Deletes the comment in the
          Comment          active cell.

If you wish to move the box to a different location without losing the arrow and line connecting to the cell, select Edit Comment and position your cursor on one of the box's borders. When four tiny intersecting arrows appear, drag the box to the new location. The line connecting to the cell will stretch to accommodate the new position (exhibit 4).

[ILLUSTRATION OMITTED]

REVIEWING COMMENTS

There are two ways to review comments: on a printed page or on the screen. If you wish to see the printed copy, click File, Page Setup. From the menu options select Sheet, and on that screen place checks in boxes for both Gridlines and Row and column headings, and press the down arrow at Comments until At end of sheet appears, as shown in exhibit 5.

[ILLUSTRATION OMITTED]

When you print the spreadsheet, the comments will appear as they do in exhibit 6, with the spreadsheet data on top and the comments at the end.

[ILUSTRATION OMITTED]

By default, comments appear on the screen only when the cursor is positioned over the related cell. Thus, the simplest way to examine each comment is to position the cursor over each cell containing a red triangle. There are options as well. When you click the Next Comment button, Excel searches across each row and down the spreadsheet displaying the next comment. Likewise, the Previous Comment button allows you to review the comments in reverse order.

While reviewing the comments, you can permanently display a single comment for later viewing by clicking the Show Comment button. If you wish, the comment can be hidden by clicking the same button, which reverts to Hide Comment.

MACROS ENHANCE REVIEWS

The linear approach provided by the Next Comment and Previous Comment commands may not suit your needs. When reviewing a loan amortization schedule, for example, you may prefer the flexibility to review the comments for interest rates first, then see comments for the cells using these interest rates. But that creates a problem: How can you be sure that you've reviewed all the comments?

A simple method is to change the comment's background color after it's reviewed. An efficient way to do that is to create a macro to automate the task. Start by ensuring the Reviewing toolbar is in place. Position the cursor on any cell containing a comment. Click Tools, Macro, Record New Macro. Assign an appropriate macro name, such as Review_Comment, choose an easy-to-remember shortcut key, such as R for review and make sure Personal Macro Workbook is selected in the Store macro in: box, as shown in exhibit 7, page 32.

[ILLUSTRATION OMITTED]

After pressing OK, click on the Relative Reference button on the Stop Recording toolbar; that enables the macro to work for any cell.

Select the Show Comment and Previous Comment buttons, then select a background color using the Fill Color button on the toolbar.

To complete the macro, click the Hide Comment and Stop Recording buttons. A completed comment box with color looks like this:

Now you can review the comments in any order. After pointing to a cell and reading the related comment, press Ctrl + Shift + R to change me comment's background color. When you have completed your review, use the Show All Comments or Next Comment commands to scan for any items not colored--an indication that you have not yet reviewed them.

THE TICK MARK APPROACH

Although comments are an effective tool for documenting spreadsheets, you may be more comfortable using traditional tick marks. Exhibit 8, shows an amortization schedule documented with tick marks.

[ILLUSTRATION OMITTED]

Electronic versions of tick marks can be created with any graphics program or copied from any clip art library. We'll use Microsoft Paint, which is built into Windows, to create them and then place them on a customized toolbar and attach a macro that copies the symbols to the spreadsheet.

We'll be placing the macro on the personal.xls file so it'll be available on any spreadsheet in your computer. Although the file is hidden by default, it opens every time you load Excel.

Begin by using Explorer to locate your personal.xls file in the Windows / Application Data / Microsoft / Excel/Xlstart folder. If personal.xls isn't there, look in Program Files / Microsoft Office / Office/ Xlstart. Create a new folder in Xlstart called Symbols to store your tick mark images.

Now open a new Excel workbook. Display the personal notebook by clicking Window, Unhide. (If personal.xls isn't listed, open the file from your Xlstart folder).

Load Paint (click Start, Programs, Accessories, Paint). Once in Paint, change the background color by clicking on the Fill With Color button and the yellow color box (since we're making the background yellow) and then click once inside the drawing area.

To enter the GL (general ledger) symbol in black text, click on the Text Box button, select Image, remove the Draw Opaque check and click on the black color box. Then click and drag within the drawing area to make a small box, and type in GL. Change the font to Arial Narrow 10 pt., a good typeface and size for Excel (exhibit 9).

[ILLUSTRATION OMITTED]

Click on the Select button and make the box around the image as small as possible.

Using the Edit and Copy To menu commands, save the image to the Symbols folder. You're now ready to create a macro that will insert the image at any location on your spreadsheet.

Return to Excel and view the personal.xls file. Click Tools, Macro, Record New Macro. Enter an appropriate macro name, such as General_Ledger and click OK. Make sure to select the Relative Reference button on the Stop Recording toolbar. Click Insert, Picture, From File, and select the file name. The worksheet would look like this:

Now click on Stop Recording.

The final step is to attach the macro to a button on a toolbar. Copy the GL symbol to the clipboard (Ctrl C), click Tools and Customize. Create a new toolbar called Tick Marks by selecting the New button on the Toolbar tab. The shell of your new toolbar will appear on the screen. Select the Commands tab of the Customize dialog box and click Macros in the Categories box. Click and drag the Custom Button command from the dialog box to your toolbar and your spreadsheet will appear as shown in exhibit 10.

[ILLUSTRATION OMITTED]

Now click on Modify Selection to view the Button menu. Select Assign Macro and the General_Ledger macro name. Click Modify Selection again and select Paste Button Image to change the image from the smiling face to GL. Click on Modify Selection once more and change the button name to Traced to general ledger.

Be aware that converting the Paint image into an Excel button may change the shape of some characters. You can improve the button's appearance by selecting Edit Button Image and using the Button Editor (exhibit 11).

[ILLUSTRATION OMITTED]

Close the dialog box. You now have a toolbar with a single tick mark. Position your cursor at any cell and point to the GL button to display the Traced to general ledger description. Click on the button to insert the tick mark in the upper left corner of the cell. You can move the tick mark as needed. If an explanation of a tick mark is required, insert the symbol again under the schedule or on another worksheet, adding your comments in the adjacent cell.

A COMPLETE TOOLBAR

You can develop a custom tick mark toolbar that contains a variety of named tick marks (exhibit 12), such as GL, for Traced to general ledger, and IM, for Immaterial.

[ILLUSTRATION OMITTED]

The second group contains tick marks created from clip art. Another group can contain tick marks created in the Button Editor. The process uses commands similar to those presented earlier, although the order is different. Start by placing a macro command (the smiling face) on the Tick Marks toolbar, then create a tick mark image using the Button Editor. For example, exhibit 13, at right, shows the button image for a symbol often used to denote footing.

[ILLUSTRATION OMITTED]

Now select the Copy Button Image option on the Button menu and paste the image into Paint and select and save the image. Then create a macro to insert the image and attach the macro to the button.

The final grouping on the Tick Marks toolbar contains four callout styles. A callout is an Autoshape (which can be found in the Drawing toolbar) consisting of a connecting line and a text box. When reviewing a spreadsheet, you can use a callout to communicate explanations or messages to readers (exhibit 14). For more on using callouts, see "Flowcharting Made Simple,"JofA, Oct.2000, page 77.

[ILLUSTRATION OMITTED]

A WORTHY EFFORT

As you can see, while it takes time and patience to create special symbols and to automate their presentation, in the long run the time saved and the improvement in communication are well worth the effort. Once you develop the tick mark toolbar you can share it with colleagues, enhancing their productivity, too.

EXECUTIVE SUMMARY

* IF YOU WANT TO PLACE explanatory comments on a complex spreadsheet, there are tools in Excel and Windows that perform such chores.

* YOU CAN LINK COMMENTS to spreadsheet cells with a single mouse click. And there are effective ways to review the comments in sequence or to print them. You can move comments to different locations in the spreadsheet without breaking their link to the targeted cell.

* IN A COMPLEX WORKSHEET, you can track which comments have been reviewed by changing the comments' background color. A macro can automate that process.

* ALTHOUGH COMMENTS ARE AN EFFECTIVE TOOL for documenting spreadsheets, you may be more comfortable using traditional tick marks that are available in any clip art program. Creating custom tick marks is very easy. You can use Microsoft Paint or any other graphic application tool to make them.

* ALTHOUGH IT TAKES TIME and patience to create the special symbols and to automate their presentation, in the long run the time saved and the improvement in communication are well worth the effort. Once you develop the tick mark toolbar you can share it with colleagues, enhancing their productivity, too.

An Invitation

If you have a special how-to technology topic you would like the JofA to consider for inclusion in this series, or an application shortcut you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin. His e-mail address is [e-mail].

MARK W. LEHMAN, CPA, PhD, is all assistant professor of accounting at Mississippi State University at Starkville. His e-mail address is [e-mail].