Make Your Mark in Spreadsheets

hat 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 ).

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.

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.

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 Comment	Finds and edits the previous comment.
	Next Comment	Finds and edits the next comment.
	Show/Hide Comment	Shows or hides the comment in the active cell.
	Show/Hide All Comments	Shows or hides all comments on the worksheet.
	Delete Comment	Deletes the comment in the 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 ).

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 .

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.

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 .

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

The Relative Reference button.

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

The Fill Color button.

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 the 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.

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 ).

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 Mark s 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 .

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 ).

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.

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 shows the button image for a symbol often used to denote footing.

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.

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.