News and Blog Articles

Here are a couple of quick shortcuts that can speed up your work if you’re tracing through a big model.  Sometimes you might find yourself constantly wanting to use these over large models – thankful that they exist!

“Ctrl [” (control combined with the left square bracket on  your keyboard) is a shortcut that helps you trace back to the first precedent i.e. the formula that’s ‘pointing to’ the formula you’re currently in.

If you’ve selected a call that contains a formula, press “Ctrl” and, at the same time as keeping that key pressed down, hit on the left square bracket “[“.

If the formula you’re currently in has been constructed carefully (or professionally) a smart modeller will have positioned the furthest away cell reference earliest in the formula.  If the formula is something like = [cell reference at the bottom of a far away sheet] + [cell reference at the top of a far away sheet] + [cell reference at the top of this sheet] + [cell reference a bit further down this sheet] + [cell reference just above this formula], then “Ctrl [” will take you straight to the top of the far away sheet.

Next press “F5”, take your fingers off the keyboard (don’t look at the “Go To” box that pops up on the screen, you don’t need to) and press “Enter”.  That keyboard combination will take you straight back to where you started.

If you find yourself spending decent amounts of time tracing through big models, that combination (“Ctrl [” to trace, followed by “F5” and “Enter” to jump back to where you first started) will be of great use to you – saving an awful lot of clicking.

Of course, part of the trick (maximising the effectiveness of “Ctrl [“) is being in the habit of constructing formulas by placing the furthest cell reference first within your formula.  When you’re trying to check what someone’s done (in a big model) this formula-construction habit really starts to become helpful – enabling you to make the most beneficial use of “Ctrl” “[“.

In fact it becomes a hallmark of good professional modelling work.  If you see that someone has taken the trouble to assemble their formulas smartly (anticipating that someone new to the model will want to trace through the work), you start to think that the modelling must have been conducted by an experienced safe pair of hands.

Careful ordering of the references within your formulas (positioning the furthest away reference first) does help.  But it’s still tricky to inspect a formula that makes use of lots of references on far away sheets.

If you’re really interested in model transparency, what you’ll eventually learn to do is minimise cross sheet linking (Microsoft recommends this in its support documentation).  Rather than have lots of complex formulas referencing far away sheets, what you could do instead is bring those references in – to the tab you’re working on – as a simple link.

Imagine a complex formula on the current tab = [cell reference at the top of a far away sheet 1] + [cell reference at the bottom of a far away sheet 1] + [cell reference at the top of a far away sheet 2] + [cell reference at the bottom of a far away sheet 2] + [cell reference at the top of this sheet] + [cell reference a bit further down this sheet] + [cell reference just above this formula].  

What you could do is make sure the work happens on the far away sheets rather than on this near sheet.  That is, create a subtotal on [far away sheet 1] that’s = [cell reference at the top of a far away sheet 1] + [cell reference at the bottom of a far away sheet 1].  Also a similar sub-total on [far away sheet 2]. 

 Next import the references (the subtotals) from the far away sheets onto the current tab.  You can see those shaded blue below.  That is, one cell containing = [subtotal on far away sheet 1] at line 17.  Also a second cell = [subtotal on far away sheet 2] at line 18.  Now the current tab contains two imports in two lines.

As a reminder, the first more-complex version of the formula was that shown in the first screen shot above.  By taking the step to import, the revised formula becomes much simpler and easier to trace.  In a big model (with lots of complex formulas) each import only needs to be made once.  It can be reused from there. 

 A habit of importing (which at first seems a bit cumbersome) can really work to simplify calculations in a big model, making them easier to trace and enhancing calculation speed.

With the model restructured and constructed in the way Microsoft suggests you should (minimising cross sheet linkages) see what happens when you press “Ctrl [“.  when you’re sitting in cell C22 on the near sheet.  With no far away tabs to jump to, you’ll see all the relevant references highlighted on this tab straight away.

Scroll to the blue imports on the near sheet and you can use “Ctrl [” to jump across to the sheets where they came from.

Then press “F5” “Enter” to jump back to where you started from on the near sheet.

Yes really some people (we) think that hard about the modelling work we do for you – reducing cross sheet linking, simplifying and importing calculations onto the sheets where they’re needed.

The extra steps make models simpler and easier to trace (e.g. for the eternal funder reviewing your forecasts, or their advisor) and, according to Microsoft, help with calculation speed.

The bonus it that “Ctrl [” becomes a really useful shortcut for jumping around well-structured models.  But just knowing about “Ctrl [” kind-of misses the point.  It’s only really useful because of how carefully the model has been constructed (zealously) in the first place.