Excel Tools for Accountants
I’m on the phone with Frank while I’m driving on the 405. (I’m not breaking the law, because the state legislature thinks it’s distracting to hold a cell phone but not to speak via Bluetooth.) Frank, who does all the accounting for my company, has some strong opinions about Excel. He uses it frequently at his clients’ sites, where he pulls data together from various sources to construct cost and income analyses. Sometimes he does this as part of an audit, sometimes simply as a matter of making a point he wants the client’s managers to appreciate.
What Tools do Accountants Need?
That, says Frank, is why he uses Excel. The formatting that he can get into the reports, and the charts that he can create, are so good that his clients aren’t distracted by lousy appearance. Instead, they concentrate on the message that Frank’s trying to get across.
Frank tells me that he’s faithfully taken courses in Excel every time a new version comes out. He feels duty bound to do that, but by now he’s really questioning how much benefit he’s gotten from those courses – even when all the Microsoft Office applications switched from the traditional menu structure to the Ribbon. The courses teach him how to format a working capital analysis, how to create a chart of budget variances, and where to go for a page-break preview of a worksheet.
But the courses never seem to get to tools that would really make his on-site time more effective. It’s ridiculous, Frank says, but he seems to have to type the names of the months and quarters into worksheets every time he visits a client. Same thing with a chart of accounts. Ditto repetitive formulas and statement footings.
I explain to Frank that he can get Excel to do those things automatically. I also ignore him muttering, “This is so cool,” as I walk him through automatically getting month names onto a worksheet. Frank wants more, but it occurs to me that I’m not absolutely certain he’s off the clock, so I say goodbye and concentrate on avoiding a swarm of SUVs as I merge onto the 5. I try not to dwell on a news story that California drivers carry more guns in their glove compartments than insurance policies.
And I resolve to start writing a chapter when I get home. The chapter will discuss some tools that those Excel courses never seem to get around to.
Since way back, at least as far as Excel 97 and maybe even Excel 95, Excel has come with some pre-defined lists. I’m a little surprised to find that over a decade later, Microsoft hasn’t thought of anything else that its users might want to list on their worksheets (state names and abbreviations, for example? or the names and ticker symbols of the Dow 30 Industrials?). But it hasn’t, so it’s a good thing that you can define your own custom lists.
The two built-in lists that come with Excel are days of the week and months of the year. If you count their abbreviations, that’s really four lists.
Suppose that you want a list of the names of the twelve months, spelled out. Do this:
- In, say, cell A2 type the word January and press Enter.
- If necessary, re-select cell A2. Notice that the active cell, A2 here, has a square black block at its lower right corner. That’s called the Fill Handle. Move your mouse pointer over it until the pointer turns into a crosshairs.
- Hold down the mouse button and keep holding it down while you drag across several cells either to the right or down. Notice that each successive cell shows the name of the next month: February, March, April, etc.
- When you’ve had enough, release the mouse button.
You now have the names of several months in consecutive cells. It’s quicker than typing them yourself. And they’re spelled correctly. A few more items to know about:
- If you drag on past twelve cells, the list starts repeating: November, December, January, February …
- If you start by dragging up or left, the end of the list appears next: January, December, November …
- This capability is called drag-and-drop. If it doesn’t work, or if it stops working, the first thing to check is whether it’s been turned off. Click the Ribbon’s File tab and choose Options. Click Advanced on the Excel Options navigation bar. In the Editing Options section, make sure that the checkbox labeled Enable Fill Handle and Cell Drag and Drop is checked. (You should also make sure that the subsidiary checkbox, Alert Before Overwriting Cells, is checked.) If drag- and-drop still doesn’t work, make sure you’ve begun by entering an actual value from the list. If your initial entry was accurate and it still doesn’t work, re- boot.
- You can start with an item within the list, such as April or October.
The other built-in lists are abbreviated month names (Jan, Feb, Mar, etc.), days of the week (Monday, Tuesday, Wednesday, etc.) and abbrevi- ated days of the week (Mon, Tue, Wed, etc.).
Building Custom Lists
There are probably plenty of related items that you or an assistant need to enter on a worksheet from time to time: The names of employees, their social security numbers, payroll deductions, and so on. You can just about eliminate the need to enter and re-enter this information on new worksheets by putting it in custom lists.
The simplest sort of list has just one column. Figure 5-1 shows a list of customers. Once entered on a worksheet, you can convert this tempo- rary list to a permanent list.
Figure 5-1: You can get Excel to store this list so that it’s always available.
Take these steps to create a custom list:
1. Click the Ribbon’s File tab and choose Options from the navigation bar.
2. Click Advanced in the Excel Options navigation bar.
3. Scroll down to the General area.
4. Click the Edit Custom Lists button.
5. Click in the Import List from Cells box. (See Figure 5-2.)
6. Drag through the list in the worksheet (in Figure 5-1, see cells A2:A9).
7. Click the Import button, and then click OK. Click OK again to return to the worksheet.
Figure 5-2: Use this dialog box to edit a list after you’ve first established it.
The list is now stored in Excel itself, not just in a workbook. As a result, the list is available any time you start this installation of Excel. You don’t have to open any particular workbook.
You can also put the list in any workbook by entering any item in the list into a worksheet cell and then using drag-and-drop as described in the prior section. And when you do so, you can be confident that no misspellings have crept into the list; it’s the same as the one you saved.
If you’ve read Chapter 1 on Excel lists and tables, you might have noticed that there’s a difference between a typical worksheet list and a custom list: The custom list is not maintained with a column header (see, for example, Figure 1-2 for a true Excel list). In Figure 5-1, on the other hand, you would ignore the column header in cell A1 when you save it as a custom list – notice in Step 6, you use cell A2 as the start of the list.
This is an annoying distinction if you’re writing about Excel, and it’s emblematic of the inconsistency that tends to mark the way that Microsoft defines structures used in its applications. But if you’re not writing about Excel, it’s not that big a deal. It quickly becomes second nature to add a column header to a worksheet list or table and to omit it in a saved custom list.
Managing Custom Lists
In the prior section I described how to save a custom list using data that’s already on a worksheet. I believe that’s the way to do it because then you have all kinds of tools available. For example, before converting a worksheet list to a saved, custom list, you can:
• Use Excel’s spellcheck on it.
• Sort it using the Sort button on the Ribbon’s Data tab.
• Start by pulling the list into the worksheet from an external data source such as a web site or a database.
However, if you want to, you can create the list from scratch using the dialog box shown in Figure 5-2. Click NEW LIST in the Custom Lists box, if necessary, and start adding list entries in the List Entries box. As the dialog box admonishes you, press Enter after every list entry to get a new line for another item.
You can do some editing of a custom list from the Custom Lists dialog box (although you’re prevented from editing the lists of day and month names and from deleting either those lists or their entries).
For example, select a list that you have built in the Custom Lists box. Now, in the List Entries box, you can:
• Delete an entry. Drag across it with your mouse pointer and press your keyboard’s Delete key (the Delete button in the dialog box deletes complete lists only).
• Edit an entry. For example, drag across the “u” in “honour” and press your Delete key.
• Add an entry to the end of the list. Click immediately to the right of what is now the final entry in the list, press Enter, and then type another entry.