Microsoft Excel is having a big birthday this year. But there’s no room for “over the hill” jokes here—even marking the big 3-0, Excel is still as relevant as ever.
“Excel is the industry leader in spreadsheet software, and, my guess is that it will be around for a long time,” said Jeff Lenning, CPA/CITP, president of Excel University, a company that trains accounting professionals in the software.
Think you know everything there is to know about SUMs and SUMIFS? Think again.
Here are some Excel tips that will help you be more efficient in your work.
Take your data as it comes
Avoid introducing manual steps into recurring-use workbooks. Instead, take the time to figure out the formula that allows you to work with the data in the format in which it was sent.
Memorize command shortcuts and create new ones
Keep your hands on your keyboard to improve your speed. Boost your overall productivity by learning the keyboard shortcuts for frequently used commands.
Good ones to know:
Select the entire row.
Select the entire column.
Insert the SUM formula.
Move to the worksheet to the left.
Move to the worksheet to the right.
You can also build your own commands by creating your own shortcuts on the Quick Access Toolbar.
Master those tables
Microsoft introduced the table feature in Excel 2007. Tables offer some advantages over using ordinary data ranges.
Tables, which are easy to format, maintain the header row for easy viewing, and also feature filter controls on the header row for easy sorting.
In addition, tables automatically expand when you add new data under the table. When you use the table’s name in your formulas, your formulas will automatically include any new transactions you paste-append to the table, according to Lenning.
Use the SUMIFS function
Caleb Bullock, CPA, CGMA, a supervisor at Somerset CPAs, PC, in Indianapolis, is a fan of the SUMIFS function, which is formatted as =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]).
SUMIFS is a conditional summing function that adds up a column of numbers, but includes only those rows that meet one or more conditions.
It’s similar to the VLOOKUP function, an easy tool to use to find information in a table or range by row. SUMIFS function can actually replace traditional functions such as VLOOKUP when returning a number.
Use the SUBTOTAL function
This function works like the SUM function but excludes other SUBTOTAL functions in its range. In addition to simple sums, it can be usedto find a different type of subtotal, such as an average, count, maximum, or minimum. You can even use SUBTOTAL to ignore values in hidden rows.
The SUBTOTAL syntax: =SUBTOTAL (Type of total, range of cells).
Make formatting easy
If you don’t already have a pre-formatted worksheet, save time by using keyboard shortcuts. One favorite of Joseph Mauriello, CPA, a senior director at BDO in Dallas, is the Ctrl+5 shortcut for formatting a strikethrough.
Excel wizard Sarah Klionsky, CPA, tax associate at McGladrey LLP in Chicago, also notes that if you double-click the format painter (See the screen grab below), it keeps the tool in the ‘ON’ position, and you can continue to format noncontiguous cells instead of having to reselect the format for each section.
Adjust your workspace
Nothing is more frustrating than flipping back and forth to different datasets or being unable to print correctly. To troubleshoot some of those quirks, try these ideas:
If your scroll bar is annoyingly small, select the first empty column to the right of your data and then hit Ctrl+Shift+Right Arrow to select all empty cells to the right, Klionsky recommends. On the Home tab in the Cells section, select Delete, and save the workbook. “The scroll bar should be much larger now and easier to grab,” she said.
To print more easily, set up print titles. This will allow you to keep the same columns or rows on every page when printing. (See the screen grab below).
To save time when working between two spreadsheets, Diego J. Baca, CPA, an assurance manager at Ernst & Young in Denver, recommends using the Ctrl+Tab shortcut.
Other shortcuts easily hide and unhide rows of data, Mauriello says:
Hide a row.
Unhide a row.
Samiha Khanna is a freelance writer in Durham, N.C.
The Edge e-newsletter, is dedicated to providing tips and tools of interest to young professionals, including articles on building career resiliency, networking for success, and de-prioritizing the immediate to focus on the important. Watch for it in your inbox. Subscribe now.