Are you getting all you can out of Excel?
Excel has plenty of features, shortcuts, and tools that can save you time, improve the integrity of your workbooks, and simplify data analysis. Here are some tips to up your game. This article is based on Excel 2016; previous versions and Excel for the Mac may not have all these features or may work slightly differently.
1. Keyboard shortcuts
Keeping your hands on the keyboard shaves seconds off many tasks. While the few extra seconds it takes to use your mouse may not seem like much, as Anastasia Papadatos, general ledger accountant at Noodles & Company, a publicly traded restaurant chain headquartered in Broomfield, Colo., said, "When you're able to combine all those keyboard shortcuts and stop using your mouse, that time that you're saving builds up exponentially."
Beyond the basics such as Ctrl+c to copy and Ctrl+v to paste, here are some useful shortcuts for Excel 2016:
Ctrl+1 — opens the Format Cells dialog box
Alt+= — inserts the sum function to add up all the numbers above it
Ctrl+Arrow key — moves selection to the edge of a data region
Shift+Arrow key — extends selection one cell at a time
Ctrl+Shift+Arrow key — extends selection to the edge of a data region
Ctrl+Home — moves selection to cell A1
Ctrl+End — moves selection to last cell containing data
Ctrl+Page Up/Page Down — toggles to previous/next worksheet in workbook
F4 — changes a cell reference in a formula to an absolute reference
F2 — shows the formula in a cell and enters edit mode
Alt+IR or Alt+HIR — inserts a row
Alt+IC or Alt+HIC — inserts a column
Brian Tankersley, CPA/CITP, CGMA, director of strategic relationships at K2 Enterprises, suggests holding down the Alt key, "and you'll see some letters show up, and if you type those letters, it'll select different ribbon tabs, and it'll actually select individual commands."
Need an easy way to fill a formula down a column? Just double-click on the lower right corner of a cell with the formula.
Want more shortcuts? Here and here are some more.
2. Use the Quick Access Toolbar
Are there formulas or commands you use frequently, such as SUM or Sort? Adding those to the Quick Access Toolbar can save you time and effort, especially if they're buried in menus or on one of the tabs, according to Tankersley. The first nine slots on the left are the prime positions because those can be accessed by holding down the Alt key and typing the number corresponding to that icon. You can add items to the Quick Access Toolbar using one of three methods. The easiest is to simply right-click on the icon in the ribbon and choose Add to Quick Access Toolbar.
Next easiest is to click on the downward triangle at the right edge of the Quick Access Toolbar, as shown below. You can choose one of the commands already there, or select More Commands.
A third option is to choose File > Options > Quick Access Toolbar as shown below.
3. Use the SUBTOTAL function instead of SUM
Jeff Lenning, CPA/CITP, founder and president of Excel University Inc., says that accountants should master the SUBTOTAL function. The SUBTOTAL function can be used repeatedly in the same column, then again at the bottom of that column to compute a grand total. Unlike the SUM function, SUBTOTAL won't double-count the amounts included in the subtotals above it.
Besides adding up the numbers in a range of cells, the SUBTOTAL function can give you the average, maximum, and minimum of that range. To use the SUBTOTAL function to find the sum of a range of cells, use this syntax:
=SUBTOTAL(9,range)
To learn more about using the SUBTOTAL function, check out this article by Lenning.
4. Store data in tables, not ranges
Lenning recommends storing expanding data sets in tables. Without a table, formulas that refer to a range won't include rows added under the data set. Correcting the formula to include those new rows requires manual editing. However, when the data set is converted to a table, formulas automatically update to include the new rows.
To create a table, select any cell in the data set you want to convert to a table. Then on the Insert tab, choose Table. A dialog box will pop up to guide you through the creation of your table. If the first row of your data set has column descriptions, be sure to click the box for "My table has headers." In this article, Lenning shows how to create a table and demonstrates the basic functionalities.
Besides keeping formulas updated and adding nice formatting, putting your data into a table makes it easier to manage and analyze data. This article demonstrates the analytical possibilities when a QuickBooks general ledger is converted to a table, and this article shows how tables can be used to create automated recurring reports.
5. PivotTables
Once your data is in a table, the PivotTable function allows you to quickly and easily analyze and summarize the data. Both Tankersley and Papadatos consider PivotTables to be one of the crucial Excel skills every accountant should master. "You can get a quick snapshot of what you're looking for, and you can easily change it," said Papadatos. "You can quickly get the sum of all your invoices, or the average, or the minimum, or the maximum."
Here's an example that shows how to summarize bank account transactions using a PivotTable. First, the data are converted to a table following the above instructions. We want the totals by category, which is in the column labeled Acct. After clicking on a cell in the table, Summarize Data with PivotTable is selected and the dialog box Create PivotTable pops up. Click OK, and a PivotTable will be created on a new sheet.
In the new sheet, click in the check box beside Acct, then Amount, to create a PivotTable that summarizes the data by category.
There's always more to learn about Excel
These tips only brush the surface of what Excel can do. Tankersley has been doing "heavy-duty Excel since the late '80s," and said, "It's amazing to me how there is still so much to learn." As a demonstration of the surprising features of Excel, here's a video Tankersley made using the Excel add-in Power Map. This video shows the hometowns of attendees at the 2016 AICPA Practitioners/TECH+ conference in Las Vegas.
Lenning encourages accountants to think of Excel as a platform that enables automation of manual, mechanical, data-driven tasks. "Right now, accountants are faced with this situation that they have more work than time," he said. "Everyone's busy; they're stressed out. The coolest thing is to figure out how to have Excel get your work done for you so that you have more time in your schedule than work."
Liz Farr, CPA, is a freelance writer based in Los Lunas, N.M. To comment on this article, contact Chris Baysden, senior manager of newsletters at the AICPA.
