Please ensure Javascript is enabled for purposes of website accessibility

Going Concern March Madness: Recap of Excel Functions and Key Shortcut Winners

Ladieees and gentlemen (yes, we’re using those terms loosely), here are your winners from Round 1 of Going Concern March Madness: The Ultimate Excel Bracket. The games began when Worksheet Functions and Keyboard Shortcuts squared off within their respective regions:

FUNCTIONS
#1     MATCH/INDEX
#2     IFERROR
#3     SUBTOTAL
#6     SUMIF
#8     SUMPRODUCT
#10     VLOOKUP
#12     DATEVALUE
#13     LEFT

 

KEY SHORTCUTS
#1     Ctrl-S
#2     Ctrl-Z
#3     Ctrl-Home
#4     Ctrl-Tab
#5     Ctrl-Page Up
#7     Ctrl-A
#9     Ctrl-Minus
#11     F5

If you’re a glutton for Excel punishment, or just looking to fritter away a bigger portion of your day, read on for a rundown of the gory details.

Round 1 — WORKSHEET FUNCTIONS

#1 seed MATCH/INDEX macerated #16 seed ISERROR. Savvy users often use MATCH/INDEX as an alternative to VLOOKUP, and in fact we could be facing a showdown between MATCH/INDEX and VLOOKUP in round 3. ISERROR lost much of its glamour when #2 seed IFERROR came on the scene in Excel 2007, but it still has valid uses in Excel. MATCH/INDEX advances to take on SUMPRODUCT.

Not surprisingly, #8 seed SUMPRODUCT smoked #9 seed PMT. Voters considered  PMT to be a one-trick pony that determines loan payments while  SUMPRODUCT wears many hats. Savvy users can use SUMPRODUCT to multiple two or more ranges together and sum the results, tally numbers in the same fashion as SUMIFS and COUNTIFS, or even count the number of # sign errors in your worksheet.

#12 DATEVALUE initially devastated #5 seed OFFSET. The OFFSET function fought back, but couldn’t prevail. Among other things, OFFSET allows you to create resizable ranges, which can help future-proof your formulas. DATEVALUE converts text-based dates to their numeric equivalent, and advances to take on LEFT.

#13 seed LEFT leapt over  #4 TRIM. TRIM eliminates extraneous spaces from cells, which is highly useful in cleaning up exports from accounting software and other data sources. Voters deemed the ability to extract a certain number of characters from the left of a cell to be more useful.

#6 seed SUMIF smashed  #11 INDIRECT into the court. INDIRECT’s aspirations of a Cinderella run were crushed as users haven’t realized the many uses of this versatile function. SUMIF now squares off against SUBTOTAL.

#3 seed SUBTOTAL stomped CUMPIMPT in the same fashion. The SUBTOTAL function is a cousin of the lowly SUM function, but with superpowers, such as the ability to only tally visible rows. CUMIPMT was introduced in Excel 2007 and offers the ability to calculate interest expense for one or more months in a loan without requiring users to build out an amortization schedule.

#10 seed VLOOKUP vanquished #7 seed SUM, and will now take on IFERROR.

#2 seed IFERROR served an ignominious defeat to #15 seed DATEDIF.  No surprise here, since DATEDIF hasn’t even earned a place in Excel’s help documentation.

Round 1 — KEY SHORTCUTS

#1 seed Ctrl-S slammed  #16 seed Alt-Enter. Users clearly prefer being able to save their work over adding a carriage return within a worksheet cell. Next we’ll see how Ctrl-S stacks up against Ctrl-Enter.

#9 seed Ctrl-Enter edged out #8 seed Ctrl-Minus, as users prefer to be able to fill multiple cells at once over being able to delete rows or columns by way of a keyboard shortcut.

#5 seed Ctrl-Page Up powered over #12 seed Ctrl-Shift-1.  Voters felt the need to move to the next sheet to the right is more useful than formatting a cell with commas and two decimal places. Round 2 pits Ctrl-Page Up against Ctrl-Tab in a portentous pairing.

#4 seed Ctrl-Tab toppled #13 seed Ctrl-0 (zero) as voters prefer to switch to the next open workbook with a keystroke more often than they want to unhide a hidden column. Or maybe it’s that Ctrl-0 doesn’t work in Windows 7 and later unless you hack the Windows Registry. Yeah, that’s it, that’s the story.

#11 seed F5 got all over #6 seed Alt-= as voters surprisingly find displaying a navigation dialog box more useful than automatically summing a range of cells. F5 advances to take on Ctrl-Home for a knockdown between the navigation tricks.

#3 seed Ctrl-Home hemmed in #14 seed Ctrl-Shift-_ as the need to get back to cell A1 in a worksheet overshadowed the desire to eliminate borders from a block of cells.

#7 seed Ctrl+A accumulated almost all of the votes over #3 seed F3, demonstrating that users find the ability to select all cells in the current region more useful than the ability to display the Paste Names dialog box. We’re about to see some alphabetic mayhem when Ctrl-A takes on Ctrl-Z.

#2 seed Ctrl-Z almost zeroed out #15 seed Alt-Down Arrow. Excel would be practically unusable without the ability to quickly undo up to the last 100 actions one has carried out. Alt-Down Arrow attempted but failed to demonstrate its nifty ability to display a drop-down list of text entries in adjacent cells within a given column.

Remember you still have until 11:59 PT tonight to weed out the losers from Data Analysis and Other Features.

David H. Ringstrom, CPA — @excelwriter on Twitter — heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm.