Sharpen Your 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, well 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.

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; thats 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). If you click on them in turn, youll discover each has a different set of related functions.

To make a totally new, customized toolbar, click on Customize at the bottom of the menu and another menu pops up (exhibit 2), with a New button in the upper right corner. Click on New and youll be asked to name the new toolbar (exhibit 3). Once youve done that, click on the Commands button (on top) and youll see an array of available Cate gories and Comman ds (exhibit 4).

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 called 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 doesnt 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 arent shown in the standard toolbar pull-down menus, which is why this customize feature is so important.

Its a good idea to familiarize yourself with the available functions: Check out each area under the various Categories (File, Edit, View, etc.) and youll 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, youll be making them just a mouse click away. Remember, you can always add or subtract functions. So experiment—its 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 1, 2 and 5 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 well 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.

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 significan't exception is the PhotoShop file format, or PSD. If thats the only graphic software you have, use PhotoShops "save as" command and save the PSD file in another format—JPG or WMF. 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). Once you find the picture, click Insert and resize it to fit your particular needs.

All spreadsheet users have experienced the problem of sizing a spreadsheet to fit on a selected number of pages. Heres 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 youve selected the right size by clicking on Print Preview. Tip: You also can get to the Page Setup menu (only now its 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.

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 will always 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 Black and white box (exhibit 6).

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

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.) The check the AutoSave box and click OK (exhibit 7).

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 the open files to be saved (exhibit 8). To avoid being prompted to approve each save, consider turning off the Prompt Before Saving option in the menu.

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

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

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 adjust 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 the Autofit).

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

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

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.

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). If you use the Office suites, AutoCorrect words you add in Excel also will be added in Word.

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.

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 Un Freeze 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 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.

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

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