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 david@acctadv.com 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...

Event Date: May 29, 2014 In this presentation Excel expert David Ringstrom, CPA brings you up to speed on the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both...
No field likes its buzzwords more than technology, and one of today's leading terms is "the cloud." But it's not just a matter of knowing what's fashionable. Accounting professionals who know how to use...
There is a growing trend of accountants moving away from traditional compliance work to more advisory work. Client demand is there, but it is up to the accountants to capitalize on that. What should accountants' roles be...

Upcoming CPE Webinars

Apr 22
Is everyone at your organization meeting your client service expectations? Let client service expert, Kristen Rampe, CPA help you establish a reputation of top-tier service in every facet of your firm during this one hour webinar.
Apr 24
In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
Apr 25
This material focuses on the principles of accounting for non-profit organizations' revenues. It will include discussions of revenue recognition for cash and non-cash contributions as well as other revenues commonly received by non-profit organizations.
Apr 30
During the second session of a four-part series on Individual Leadership, the focus will be on time management- a critical success factor for effective leadership. Each person has 24 hours of time to spend each day; the key is making wise investments and knowing what investments yield the greatest return.