Sharpen spreadsheet skills.

A little more than a decade ago, the only productivity tools available to accountants--aside from an endless supply of black coffee--were a calculator, a sharp pencil and an eraser. Today, thanks to the personal computer, scores of programs can help CPAs carry out most of the diverse projects they regularly undertake.

But if truth be told, few accountants make full use of the available software tools. While most use spreadsheets and word processing frequently and database and presentation software less frequently, few use any of the applications to their full potential. In fact, few CPAs are fully aware how effectively these applications work together--each doing its special function and then passing along a processed file to another application for it to do its special function.

In an effort to help accountants get up to speed on technology, the Journal of Accountancy this month inaugurates a series, called Technology Workshop--tutorials on the use of the basic software tools: spreadsheet, database, word processor and presentation applications. Our goal is to help practicing accountants not only learn how to more efficiently handle some software functions but also discover new functions within the applications they use.

For some who will find these articles too basic, we suggest they collect them and pass them along to colleagues who would benefit from them.

This month the focus is on spreadsheet tools. In subsequent months, we'll examine more effective ways to use your word processor and how to make your spreadsheet work with your database and word processor--performing jobs in minutes that would otherwise take hours, if not days, to accomplish.

Spreadsheet software is to an accountant what a hammer is to a carpenter. Used correctly, both tools perform effectively and efficiently. Used incorrectly, they can botch a job--or at least make it a tedious project. For many CPAs, the information in this article will illustrate some of the less-known spreadsheet software functions that can sharpen your skills--and even take you beyond simple number-crunching.

If you're an experienced spreadsheet user, don't assume you're familiar with all the tips in this article. Check each out; you may be surprised by a function that will advance your spreadsheet work.

This article focuses on Excel 97 because it is by far the profession's most widely used spreadsheet application; however, its use does not imply our endorsement of the product. Be aware, also, that earlier versions of Excel may lack some of the functions described here.

Shortcut: As you know, underlined letters in menu commands (such as File) indicate that the function can be summoned by holding down the Alt key and then clicking on the specified underlined letter on the keyboard. For example: Holding down the Alt key and then striking the F key will bring up the File menu. In this article, we underline all the appropriate letters on menu commands.

THE PORTAL TO CUSTOMIZATION

Whether you've recently started using Excel or are an old hand at it, it's unlikely that you've examined the scores of functions tucked behind the many Excel toolbars. The toolbars are the portals to a wide assortment of buttons that trigger functions and customize the way the application looks and works. In addition, many of the buttons provide automation features that not only speed your work but also add new information dimensions to a spreadsheet. Let's examine some of the most effective buttons and their underlying functions.

CUSTOMIZE TOOLBARS

While Excel comes with a default set of buttons in its many toolbars, you're not limited to either the default toolbars or their functions. Each toolbar can be customized in myriad ways. In fact, you even can create new toolbars, each with customized buttons; that's particularly handy when you're working on unique projects that need special functions.

To begin toolbar customization, click on View and then Toolbars, and a list of currently available toolbars will pop up (see exhibit 1, below). If you click on them in turn, you'll discover each has a different set of related functions.

[Exhibit 1 OMITTED]

To make a totally new, customized toolbar, click on Customize at the bottom of the menu and another menu pops up (exhibit 2, at right), with a New button in the upper right corner. Click on New and you'll be asked to name the new toolbar (exhibit 3, page 41). Once you've done that, click on the Commands button (on top) and you'll see an array of available Categories and Commands (exhibit 4, page 41).

[Exhibit 2-4 OMITTED]

To add a function to your new toolbar, steer the cursor over the desired icon or descriptive words and hold down the left mouse button. Then drag the icon or words to the new toolbar on top of the screen and release the button. (That sequence is caned drag and drop.) If you want to remove a button from the toolbar, just drag it out. Don't worry; removing a button from a toolbar doesn't drop it from the program--it just removes it from its position in the toolbar. Also, you should be aware that many of the button commands can be accessed only from the Commands menu--they aren't shown in the standard toolbar pull-down menus, which is why this customize feature is so important.

It's a good idea to familiarize yourself with the available functions: Check out each area under the various Categories (File, Edit, View, etc.) and you'll see buttons for functions you probably never heard of. To find out what each does, click on Description below. For more details on how to use them, click on Help in the standard toolbar on the very top of your screen or hit the F1 key on the keyboard.

By adding functions that appeal to you, you'll be making them just a mouse click away. Remember, you can always add or subtract functions. So experiment-it's worth the investment in time.

Some very handy functions for CPAs are in the Insert category of the Commands menu: for example, Rows (which adds a row to a spreadsheet), Columns (which adds a column) and the +, - and = signs (for putting those functions into a formula). And from the Format menu there are Double Underscore, Merge Cells and Unmerge Cells (more about the Merge/Unmerge functions later). The Unmerge Cells button is a special time-saver because it lets you bypass the rather complex commands necessary to unmerge cells--Format, Cell, Alignment and then uncheck the merge cells box.

While you're in the Commands menu, add the Camera button from the Tools category; chances are you're not familiar with it, and we'll show you its power later, too.

Note: Excel provides multiple ways to launch several of the more poplar functions. For example, you can get into the toolbar customization menu through an alternate route from your standard toolbar: Tools, Customize. We don't have space in this article to cite all the multiple routes, but we invite you to experiment; you may find routes that are particularly convenient.

ADD SPECIAL GRAPHICS

If you distribute copies of your spreadsheet to clients or others, you probably want to include an identification of your organization, such as a logo or a letterhead graphic. Excel allows you to add a digitized copy of any graphic. It will accept many popular graphic file formats as picture files. The only significant exception is the PhotoShop file format, or PSD. If that's the only graphic software you have, use PhotoShop's "save as" command and save the PSD file in another format--JPG or WME If you don't have a digitized copy of your artwork, you can scan it; and if you don't have a scanner, most office supply stores can scan the art for you.

Once you have a digitized copy of the graphic, go to the Insert menu, then Picture and From File to insert the logo file into the spreadsheet (see exhibit 5, below). Once you find the picture, click Insert and resize it to fit your particular needs.

[Exhibit 5 OMITTED]

SIZE PRINTOUTS

All spreadsheet users have experienced the problem of sizing a spreadsheet to fit on a selected number of pages. Here's the solution: While you're in Page Setup, click on the tab for Page. Then click on the Adjust to: button and then set the % normal size button to the value you want. Remember, you do not have to adjust the font size, column width or row height when using this function--the adjustment proportionally scales the whole spreadsheet. You can confirm that you've selected the right size by clicking on Print Preview. Tip: You also can get to the Page Setup menu (only now it's just called Setup) through File, Print Preview.

While you're in the Page Setup menu, notice that you can switch the format from Portrait to Landscape, which prints your spreadsheet on its side as a way of fitting wide material across one page.

ADD COLOR

In recent years, color printers have gotten better and cheaper and somewhat faster. And since color adds an effective visual dimension to any spreadsheet, consider using it in your work. Two Excel functions add color to a spreadsheet: Fill Color (fills all highlighted cells with the color of your choice) and Font Color (colors the font). You'll find those functions in the Customize menu under the Format category.

However, if you do add color to your spreadsheet and you typically print on a machine that can print in color, the output ways will be in color. But most color printers are still relatively slow compared with black-and-white printers and their operating costs are higher. So, when you are just printing proof copies, you may only want a quick copy in black ink. That's easily done. Go to File, Page Setup and then go to Sheet and check the B Jack and white box (exhibit 6, at right).

[Exhibit 6 OMITTED]

While you're in the Page Setup menu, familiarize yourself with all the other customizable page setup functions--from printing page order to the appearance of gridlines.

AUTOSAVE 

If you've ever worked on a spreadsheet for a few hours and had your computer freeze, you don't have to be convinced of the importance of AutoSave, a function that automatically saves an open file. You can establish how frequently you want the computer to evoke the save feature. Tip: AutoSave should be activated on all machines in your office--and for all the applications that provide such a function.

Frequently the AutoSave feature is set as a default. To check, select Tools: If it's not in that pull-down menu, click on Add-Ins. (Don't get anxious when the Add-Ins feature doesn't load immediately; it generally takes a few seconds.) Then check the AutoSave box and click OK (exhibit 7, at right).

[Exhibit 7 OMITTED]

Now, to set the saving frequency, click on Tools again: This time AutoSave will be in the menu. Click on it and set how frequently you want open files to be saved (exhibit 8, at right). To avoid being prompted to approve each save, consider turning off the Prompt Before Saving option in the menu.

[Exhibit 8 OMITTED]

ADD COMMENTS TO CELLS

To add little electronic notes, like Post-Its, to a spreadsheet cell, put the cursor in the cell where you want the note to appear and right click. Then click on Insert Comment and a small box with your name (if you're the named user of the software) pops up adjacent to the cell--with an arrow pointing to the cell. Type in your comment (exhibit 9, at right). Note that when you move the cursor out of the cell, the comment will disappear, leaving a red tick mark in the upper righthand corner of the cell. Each time the cursor passes over the cell, your message will reappear. To delete the comment, right click on it and click on Delete Comment.

[Exhibit 9 OMITTED]

You can control the appearance and operation of the comment function by selecting Tools, Options and then click on the View menu. Three buttons appear under the category Comments: None (both the comment and the red tick mark will be hidden), Comments indicator only and Comment & indicator (exhibit 10, at right).

[Exhibit 10 OMITTED]

ALIGNING TEXT

Often column descriptions are too long to fit into one cell. Rather than cutting back on the size of the description or using two or more rows to complete the heading, consider the Wrap text function, which adjusts the column height so that all words in the cell will fit. If you add or eliminate a word from the heading, the cell height automatically adjusts, but you may have to adjust the column width (which can be adjusted automatically by going to Format, Row or Column and then AutoFit).

To activate Wrap text, right click on the cell and then left click on Format Cells (exhibit 11, at right). In the menu that appears, click on Alignment and then click on the Wrap text box (exhibit 12, page 47).

[Exhibits 11-12 OMITTED]

Another way to adjust space in cells is to use the Merge cells function, which also is in that Format Cells menu. You may have used the center across cells button (exhibit 13, page 47) on the standard toolbar. However, you also can merge cells down rows as well as across columns with the cell merge function. These two functions help you place text or numbers exactly where you want them. Your heading will look better and take up less room on your spreadsheet.

[Exhibit 13 OMITTED]

The Format Cells menu and Alignment tab offer yet another way to get more text into a cell. Notice that on the right side is a section called Orientation. Turn the line at the right of the word Text, which is aligned horizontally. As you move it, an indicator below shows you the angle of the text in the cell. In exhibit 13, the column heads 1st Quarter through 4th Quarter illustrate the effect of such an alignment.

AUTOCORRECT

Even if you're an excellent speller, enable the spell check feature with the F7 key or click on the ABC button in the toolbar. It will identify typos and any occasional spelling lapses. An even handier related feature is AutoCorrect--a standard feature in Word also. AutoCorrect will correct errors such as double capitals at the start of a sentence (because you may have held down the Shift key too long) or failure to capitalize a day of the week. It also will automatically fix words you commonly misspell or mistype.

To customize AutoCorrect so it "learns" what errors to fix automatically, click on Tools, AutoCorrect and then add whatever words and defaults you wish (exhibit 14, at right). If you use the Office suites, AutoCorrect words you add in Excel also will be added in Word.

[Exhibit 14 OMITTED]

You also can use AutoCorrect as a "macro" by adding common accounting and auditing words to the Replace:...With: option in the menu. For example, you could add "JJ" so that when you type "JJ" the program will spell out "Johnson & Jones, CPAs, LLP." This way you can use AutoCorrect to speed your typing as well as reduce the chances of typos.

FREEZE PANES

When a spreadsheet gets beyond one page, it's hard to find column or row headings. A quick solution is to freeze panes--actually locking part of a spreadsheet so that no matter where you scroll, the frozen section (it could be a row or a column) will remain visible. Say you want to freeze column A, which has identifying headings. Begin by clicking on the B in the margin (which will highlight the entire B column) and then click on Window and Freeze Panes. Likewise, if you want to freeze row 1, put the cursor on the 2 in the margin of row 2 and evoke Freeze Panes. To unfreeze, return to Window and click on Unfreeze Panes.

Similarly, when printing the file you may want row and column headings to print on multiple pages. To accomplish this, go to File, Page Setup and then the Sheet menu. Place your cursor in the Rows to Repeat at Top: or Columns to Repeat at Left: and then highlight the row(s) or column(s) you want repeated and click OK. The Page Setup menu also gives you the option of adding footers or headers to the page.

CAMERA

Here's a function that may surprise even experienced spreadsheet users. We earlier asked you to place the Camera button on your toolbar during the customization process because Camera is not available from the regular pull-down menus. Now let's use the function.

Suppose you have a set of properly formatted financial statements and you want to put them into a Word document. Highlight the first financial statement you want to transfer and click on the Camera button. Place the cursor anywhere on the spreadsheet and click the left mouse button to paste the picture on the spreadsheet. Click on the picture and use the regular copy command (Ctrl C) to copy the picture. Open your Word document and paste (Ctrl V) the financial statement picture into it. Notice that all the Excel formatting (fill colors, text colors and fonts) is retained. Another benefit of the Camera function is that you can resize, move and wrap text around the picture just like a piece of clipart. Using the Camera function eliminates redundant footing and proofreading. This command will really improve your productivity.

CONDITIONAL FORMATTING

Say you want to examine all amounts in an accounts receivable spreadsheet file that are greater than $25,000. However, if the file contains thousands of items, that would be a Herculean task. With Conditional Formatting you can easily identify all the target accounts for further examination by placing a unique color format in all cells where the amount is greater than $25,000.

Begin by highlighting all the account balances and click on Format, Conditional Formatting (exhibit 15, below). Then set the conditions of the search. In this case, set the condition so that cell value is "greater than or equal to" and enter 25000 in the last option. Now, change the format to something that will catch your eye (say, make the background yellow). When you click OK, all of the cells that contain an amount of $25,000 or more will be shown with a yellow background. Bonus: You can test for up to three relationships at a time.

[Exhibit 15 OMITTED]

An Invitation

We hope this tutorial has introduced you to the many unheralded functions of Excel and that once you begin using them you will discover other functions to make your work go faster and easier. If you have a special how-to technology topic you would like the Journal 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--telephone: [phone]; e-mail: [e-mail].

RELATED ARTICLE: Spreadsheet Shortcuts 

CUSTOMIZE TOOLBARS

View, Toolbars, Customize, Commands (tab) To create a new toolbar: Click New, name the toolbar and click OK Pick one of the Categories and click on Commands and then drag and drop a command onto a toolbar

ADD A GRAPHIC

Insert, Picture, From File Select the graphic from menu, click Insert

PRINT IN BLACK AND WHITE

File, Page Setup, Sheet (tab,) Check Black and white option, click Print

RESIZE PRINTOUTS

File, Page Setup, Page (tab) Scale printout by percentage Click Print

AUTOSAVE

Tools, Add-Ins Check the AutoSave option, click OK Select Tools, AutoSave to set preferences

ADD COMMENTS TO CELLS

Right click on the desired cell and select Insert Comment To view Comment, rest mouse pointer in cell To remove: Right click on the cell and select Delete Comment

TEXT WRAP

Choose Format, Format Cells, Alignment (tab), check Wrap text, click OK

MERGE CELLS

Highlight cells to be merged Click on Merge cells button

AUTOCORRECT

Tools, AutoCorrect Type misspelled or abbreviated words and then correct or add letters for full spelling

FREEZE PANES AND PRINT HEADINGS

For a horizontal pane, select the row below where you want the split For a vertical pane, select the column to the right of where you want the split To freeze both panes, select cell to right and below the split Click on Window, Freeze Panes To remove: Window, Unfreeze Panes To print headings: Click File, Page Setup, Sheet (tab) Place cursor in the Rows to Repeat at Top: or Columns to Repeat at Left: Highlight the row(s) or column(s) you want repeated, click OK

CONDITIONAL FORMATTING

Highlight cells to format Choose Format, Conditional Formatting Fill in the blanks Click OK

CAMERA

Highlight range you want duplicated Click Camera button Copy (Ctrl C) the "photo" and paste (Ctrl V) to any application

SUSAN COOMER GALBREATH, CPA, PhD, is an assistant professor of accounting at Tennessee Technological University in Cookeville. Her e-mail address is [e-mail]. JON A. BOOKER., CPA, CIA, PhD, is professor of accounting at the university. His e-mail address is [e-mail]. BRADLEY C. ADAMS is a senior accounting major at the school. His e-mail address is [e-mail].