When I use Microsoft Excel, am I making the most of the spreadsheet functions available? What are the most helpful shortcut keys in navigation and formula input? I needed a review course to rejuvenate my old tricks and learn some new ones. On June 10, 2015, about a dozen people attended a day-long seminar “The Power of Excel – Part 2,” held on location at the offices of the CFA Society of Toronto.
The seminar was conducted by Jon Zelman of The Marquee Group. Zelman’s years in investment banking and financial analysis have given him a deep knowledge of Excel, and the type of models that financial analysts plan to build with it. Moreover, he is passionate about the subject.
Over the next few “Old Dog, New Tricks” posts I will summarize a few things I learned, which were just a subset of all that was on offer.
1. Resist the Mouse
An apocryphal story concerns a well-known bank where each newly hired financial analyst was issued a laptop—but no mouse—for the first month. Management’s intention was to force the analysts to develop deep familiarity with shortcut keys. Zelman would have thrived in this environment.
He began by heartily encouraging the class to use the keypad: resist the mouse! Each student received a laminated page of keyboard shortcuts. Some are ubiquitous, such as Copy, Paste but others such as <cntl> G are less common (GoTo).
The shortcuts are listed at The Marquee Group website; select Academy > Keyboard Shortcuts. However, this is not an exhaustive list and they are missing my new darling, learned in Zelman’s class: Cntl + Backspace.
2. Best shortcut of the day
Have you ever been typing a formula in one cell, gone and done stuff, and then wanted to return to where you were at?
A note on terminology: The cell you were typing in is called the Active Cell, just like the sheet you are working in is the Active Sheet, and the Excel file is the Active Workbook.
The best shortcut that we learned that day turned out to be Cntl + Backspace to return you to the Active Cell. Why? Think about typing in a formula, one that requires me to select a large range, say A8:G6000. After selecting that vast array by combinations of Shift + End + Arrow Keys, I am stuck somewhere looking at the extreme-most corner, cell G6000, which is nowhere near the Active Cell where I began typing the formula. I used to scroll like crazy to get back—what a hassle! Thanks to Zelman, I’ve learned I can click Cntl + Backspace to return to the cell where I was typing.
I’m not alone in my excitement over this shortcut. “I am pleasantly surprised to find a new shortcut, one that takes away my manic mouse scrolling in one fell swoop,” writes power user Chandoo on his website, and I certainly agree. ª