Creating a "Chainsaw" List of Excel Worksheet Functions

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 or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.


You may like these other stories...

Whenever I speak to accountants about creating a cloud practice, the most common question is, “How do I charge my clients?” Ten years ago, maybe even five years ago, if I would’ve posed this question...
While reputational risk is the No. 1 nonfinancial concern among corporate directors, cybersecurity/IT risk is gaining steam. In fact, both private companies and organizations with more than $1 billion in revenue felt they...
Accountants who specialize in forensic and valuation services point to electronic data analysis, or big data, as the most pressing issue they’ll face in the coming months, according to results of a new survey released...

Upcoming CPE Webinars

Jul 31
In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
Aug 5
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.
Aug 20
In this session we'll review best practices for how to generate interest in your firm’s services.
Aug 21
Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.