Creating a "Chainsaw" List of Excel Worksheet Functionsby
By David Ringstrom, CPA
Depending on your version of Excel, you have nearly 500 different worksheet functions at your disposal. Some worksheet functions, such as CUMIPMT and NPER, are like the chainsaw in my garage. I don't use them very often, but when I do, no other tool will suffice. Other functions are more akin to screwdrivers and pliers – I use them so often, I can type them in my sleep. In this article, I'll show you an easy way to keep a "chainsaw" list of functions at the ready so you can avoid trying to remember "how'd I do that before?"
We'll store our list in an unlikely location known as the Name Box. It's pretty much impossible to use Excel and not notice the Name Box, which appears just above the upper-left-hand corner of the worksheet frame. Most users know this as the space in Excel where you can determine the address of the currently selected cell.
To see how our chainsaw list will work, click on any blank worksheet cell and then type an equal sign. As shown in Figure 1, the cell address in the Name Box will likely be replaced with SUM, although you may see another function name instead. Click the arrow next to the word SUM (or whatever function appears there), and a drop-down list of ten functions will appear. As shown in Figure 1, when you choose a function such as PMT, the Function Wizard appears. You can then either complete each field or take a shortcut and type the arguments in the first field, separated by commas. Do remember to type an equal sign in a worksheet cell before you click the arrow within the Name Box; otherwise, you'll either see a blank list or a list of range names that has been created within the current workbook.
Figure 1: Excel's Name Box provides ready access to recommended or recently used functions.
The worksheet functions shown in Figure 1 represent the standard set of recommended functions, which you can replace by choosing other functions from the Insert Functions dialog box shown in Figure 2. To display this dialog box, click the fx button on the Formula Bar, choose More Functions, or press Shift-F3.
Figure 2: Choices you make from Insert Functions will appear on the list within the Name Box.
When necessary, it's easy to restore a hidden formula bar:
- Excel 2007 and later: Click the Formula Bar checkbox on Excel's View tab.
- Excel 2003 and earlier: Choose View and then Formula Bar.
- Excel 2011 for Mac: Choose Formula Bar on the View menu.
Once you launch the Insert Function dialog box in the Windows-based versions of Excel, you can either enter words to describe the desired function, such as "total interest for a loan", and then click Go as shown in Figure 2, or use the drop-down list to browse functions by category. Brief descriptions will appear as you scroll down the list within the Insert Function dialog box. Either click OK or double-click on a function name to display the Function Wizard shown in Figure 1.
Any functions you choose from the Insert Functions dialog box will appear at the top of the functions list in the Name Box, which is how you can develop your chainsaw list of functions. If you actively use the Insert Functions dialog box, this may be of little benefit to you, but if you're like many users who don't use that feature often, it can serve as a helpful list of favorite functions. Going forward, you'll be able to remember COUNTIF when you need to find duplicate values or break ties, or that SLN calculates straight-line depreciation. And of course, that FREQUENCY allows you to associate values with different thresholds.
Read more articles by David Ringstrom.
About the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at [email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.