Accounting
How to Expand Your Abilities in Microsoft Excel
If you're presently using an older version of Excel, bear in mind that Excel 2019 will be released in the latter half of this year. Expect more artificial intelligence features to help you work faster and more effectively. Further, each new version of ...
May. 14, 2018
A recent study by Microsoft showed that 80% of Excel users have yet to use the pivot table feature. This reminded me of a phase I coined years ago, “Either you work Excel, or it works you!” For the uninitiated, pivot tables are a feature that enable you to quickly transform a list of data in Excel into meaningful reports with just a few mouse clicks and perhaps drags of your mouse. Users that are unaware of pivot tables tend to spend far more time than necessary force data into meaningful formats. However, this is just one area of Excel—there are countless ways that users push a rope uphill while working with spreadsheets. In this article I’ll share several techniques you can use to broaden your horizons within Microsoft Excel. Most relate to Excel 2013 and later, but I do have tips for the older versions as well.
You can get a running start with pivot tables in Excel 2013 and later by clicking on any cell within a list of data, activating Excel’s Insert menu, and then clicking on Recommended Pivot Tables. An artificial intelligence feature in Excel will look at your data and suggest a variety of options for summarizing the data. Double-click on the format of your choice, and voila, you now have experience with pivot tables. A field list will appear on the right, from which you can drag field names from the list into any of the four quadrants below. If the report skews in an unwanted fashion, simply drag the field you just dropped out of the quadrant or click the Undo command.
Anyone using Excel 2013 or later can get a similar running start with the Recommended Charts feature. This can be a particularly helpful way to discover uses for several new chart formats available in Excel 2016, such as Treemap, Sunburst, and Waterfall. Simply click anywhere within the data you wish to summarize in chart form, and then choose Recommended Charts from the Insert menu. The resulting dialog box will suggest different formats for presenting the data, as well as background on the merits of each chart type.
Conversely, let’s say that you use certain features in Excel just infrequently enough that you can’t remember where to click. The Tell Me feature in Excel 2016 offers a built-in search capability for unearthing features and functions within Excel. Click in the space that reads “Tell me what you want to do” and then type a few words. Depending upon what you type, a dynamic menu may appear below that space where you can click on the action you wish to take, such as removing duplicates or perhaps speak cells to have Excel read the contents of a selection of cells out loud to you.
Of course, your mileage will vary here, because if you type the words mortgage interest in this space, Excel will unhelpfully show a Send command, as if you wanted to send your spreadsheet by email, as well as categories for More and Math & Trig worksheet functions. All these choices are dead ends—the Tell Me feature presently works best for unearthing features in Excel, as opposed to worksheet functions.
However, there is an easier way for anyone to locate worksheet functions, no matter the version of Excel. Let’s say that you do need to determine the total interest that will be paid on a loan. Most users build out an amortization schedule and then sum the Interest column. If that’s your preferred approach, do note that you can easily insert an amortization schedule into any existing Excel workbook. To do so, right-click on any worksheet tab, choose Insert, and then double-click Amortization Schedule on the Spreadsheet Solutions tab. Click the Unprotect command on the Review menu if you need to customize this schedule. Yet, if the total interest number is what you need, there’s also a worksheet function for that.
In any version of Excel press Shift-F3 or click the Insert Function button, represented by fx on Excel’s formula bar, and then type the words loan interest in the search field, and then click Go. Excel will present a list of possible worksheet functions, including CUMIPT. Double-click on CUMIPMT to display a fill-in-the blank screen for crafting the formula. Note that you’ll need to scroll down to see the sixth argument, for which you should enter 0 for payments made at the end of a month or 1 for the beginning.
If you’re presently using an older version of Excel, bear in mind that Excel 2019 will be released in the latter half of this year. Expect more artificial intelligence features to help you work faster and more effectively. Further, each new version of Excel includes ever more features that are not backwards compatible with older versions, so if you send or receive workbooks used by others outside of your organization, it’s becoming ever more critical to keep your version of Excel current.
——–
David H. Ringstrom, CPA is president of Accounting Advisors, Inc. and is a Microsoft Excel spreadsheet expert, trainer and consultant