Do you frequently work on MS-Excel calculations? These couple of keys will save you a huge time!
Everyday at work, we go through on various calculations and models prepared in MS Excel. Some of these are prepared by us, while many a times, they are prepared by someone else (say a colleague, client, banker, vendor or customer). In all of these situations, we may need a quick understanding of the flow of the calculations i.e.
1. What are the source cells (assumptions) in a formula?
We typically do this to check if the assumptions have been linked correctly. We also do this to check what a particular assumption is or where is a certain cell.
2. Which rows or cells represent the final calculations?
We may do this to check which are the final output calculations that’ll be used in our charts or presentations. Further, sometimes, before we delete a cell or row or column, we’d better check if there are any cells dependent on that cell!
Through this article, I am trying to answer these questions. Additionally, will include a few tips to save time while doing this :)
1A. Locating the source cells (or precedent cells) with the ‘Trace Precedent’ Arrows
a. Simply take the cursor to a calculation
b. Go to ‘Formulas’ Tab and click on ‘Trace Precedents’. Keyboard shortcut for this is ALT >> M >> P
c. You’ll see BLUE, RED or DOTTED arrows to the various precedent cells i.e. the cells that drive our calculations
d. The BLUE Arrows represent precedent cells that are on the same sheet
e. If any of the precedent cell on this sheet is erroneous, the arrow turn RED
f. The DOTTED arrows refer to a cell that is on another sheet. Double click on the DOTTED arrow, and you’ll be directed to the GO TO dialogue box.
Likewise, you can trace the Dependents with Arrows. The keyboard shortcut for the same is ALT >> M >> D!
Check-out this video for an example:
1B. Locating the precedents on the same sheet in one go, using the CTRL + [
If you are working on a single sheet calculation, then this is your shortcut!
1. Simply take the cursor to a calculation and Press CTRL + [
2. What do you get? All the cells which directly drive your calculation, are selected as a result
3. You can choose to highlight the driver cells by filling them with colour
This is shortest and simplest way to locate cell references in a single sheet calculation.
Likewise, you can always use CTRL + ] to trace if there are any cells on that sheet, that are dependent on your formula!
Check-out this video for an example:
1C. Locating the source cells with ‘F5’ key or ‘GO TO’:
For source cells lying anywhere in a multi-sheet workbook, I particularly prefer the F5 key the most! All you need to is:
|
Action (Step) |
Keys that you Need to Press |
|
|---|---|---|
|
a. |
Go inside a formula (This is known as Cell-Edit mode) |
Either Double-click on the cell or Simply press ‘F2’ key |
|
b. |
Select the cell reference that you want to GO TO |
SHIFT plus ← or → keys or Mouse click hold & drag |
|
c. |
That’s it! You are now directed to the Cell reference. |
Press ‘F5’ key (shortcut for ‘GO TO’) and follow it by pressing ‘Enter’ key |
|
d. |
Voila, the cell reference is blinking! This means you can even change this cell reference if it’s not the correct one! |
If you wish to correct the cell reference, press ‘ENTER’ and the new reference will be accepted. |
|
e. |
Once you are done checking, if you don’t want to disturb the existing links |
Just press ‘ESC’ and you are back to original formula cell |
So use either of the above and save time at work! For specific training on best practices of working on financial calculations and working on them faster, you may consider signing up for our Specialized Program
CAclubindia
