Sharpen Your Spreadsheet Skills

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.