This blog is a continuation of Spreadsheet Fundamentals for business folks (slightly geared towards Mac Excel users).
In the previous lesson, we learned how to navigate around a spreadsheet without a mouse. In this lesson, we're going to learn how to jump into cells to audit and edit. If you ever walk by an analyst bullpen at an investment bank and you hear an entire analyst class vigorously clicking away on their mechanical keyboards, they most likely are clicking F2 —> ESC —> ARROW KEY. F2 is the auditing shortcut in most spreadsheet softwares.
Heed this warning: Try not to do this mindlessly, as it can quickly spiral into a horrible habit of needlessly entering and exiting cells without any thought behind it. Everyone is guilty of doing this...it's almost the equivalent of mindlessly refreshing your Instagram feed after you just finished viewing the entire thing.
However, auditing cells is a very powerful feature in spreadsheets and will likely be your most used shortcut. It’s function is simple, yet oh-so-powerful: It allows you to see inside a cell. If the cell is a formula linked to other cells on the same sheet, then you will see the other cells referenced with a colorful outline around their cells. If it’s just some number or string, then you’ll just see the number or string with the ability to format it. In order to get out of this view, you just press ESC.
The reason why some analysts will hit F2 —> ESC a million times in a row is to spot check to see if there are any irregular patterns in the references. If there are, there's likely a mistake in the model.
Once you've got the hang of entering and exiting cells, you may want to be able to jump directly to referenced cells. These features are called Trace Precedents and Trace Dependents. Trace Precedents allows you to locate cells that affect the value of the currently selected cell. Trace Dependents allows you to locate cells that will be changed if you change the currently selected cell.
For some reason, these are some of the features in Excel that are not fully built out (I will get to this in a bit).
The way you trace precedents is by pressing CMD (or CTRL) + [. If you're on a cell that references another cell and type that combination, you will jump from your current cell to the first referenced cell. In order to return to your original cell, you can press F5.
As you may have noticed, when you press CMD (or CTRL) + [ it only brings you to the first cell that's referenced. What happens if there are multiple cells referenced? This is where Excel falls short. Luckily for you, there are a ton of plug-ins that solve this problem for you (Factset, CapIQ, Macabus, TTS and more). With these plug-ins enabled, usually you can type CTRL + SHIFT + [ which will open a menu that will allow you to cycle through the referenced cells using the up and down arrow keys. As you can envision, this makes tracing much more powerful.
Trace Dependents is slightly less used, but nonetheless still useful in figuring out how a model or spreadsheet works as it will help you figure out how a cell is calculated. There are a couple ways to use this feature. The first way is just the opposite of Trace Precedents, using CTRL + ].
Another useful Trace Dependents shortcut is ALT—>M—>D (unfortunately this shortcut only works on PC), which shows you via arrows what other cells rely on this particular cell. The way I remember this is "MD" stands for doctors, and doctors perform "checkups." Not my best work. "D" also stands for dependents and similarly if you did ALT —> M —> P you would trace precedents. The way to get rid of the arrows is ALT—>A—>A.
Now put these skills to work! Go download a financial model off the internet and just trace and try these shortcuts. If you get a really complicated model, you may see a million lines when you try the trace precedent or trace dependent shortcut...
