Avoiding cross sheet linking in Excel
Cross sheet linking is where you create a formula on one tab that’s making use of cell references on far away tabs.
It can feel like the most natural thing in the world to build up a formula that’s making use of multiple references on far away sheets.
But if you take care to avoid cross sheet linking, it will make your models easier to trace and check.
How to avoid cross sheet linking
With just a little bit of extra diligence applied to build work, it’s not hard to avoid cross sheet linking altogether.
Here are a couple of alternatives that mean cross sheet linking can be avoided. If you’re ever tempted to build up a formula that makes use of multiple references on far away sheets you can instead:
- Alternative 1 – do all the hard work (the formula build up) on the far sheet, and then import the result as a simple link onto the near sheet
- Alternative 2 – import cell references from the far sheet, one at a time as simple links, onto the near sheet. And then do the work (the formula build up) on the current/ near sheet.
The difference ‘no cross sheet linking’ makes
If someone is trying to work through a spreadsheet quickly and finds a complex formula, those calculations are going to take a bit of unpacking (before the spreadsheet inspector even starts to apply themselves to thinking about whether the formula really does what it’s supposed to do).
Once they’ve navigated themselves to the cell they’re interested in, the first thing they’re going to do is hit “F2”. But then, with a complex formula, that’s where the (not) fun is going to start.
Alternative 1 - do the work on the far sheet and then import onto the near sheet
Look what happens when we take the time to avoid the cross sheet links by doing the work closer to where the source data is on the far sheet.
In this case, that first “F2” inspection tells us something useful. Straight away we get some information about the components that make up the formula we’re looking at.
Then, with the restructuring, on the far-away sheet we can make use of (is it my favourite? I’m not sure) “Ctrl [“ (that is, press down control and, at the same time, press down the left square bracket) to trace each component back to its source – quickly.
Now, from the far away sheet, by pressing “F5” and “enter” (press F5, lift off, don’t bother looking at the box that pops up on screen, and press “enter”) we can get back to where we started. See: “tracing through models quickly“.
With formulas constructed a little more carefully, it becomes much quicker and easier to inspect the pieces that the result has been assembled from.
Alternative 2 - import the data from the far sheet and do the work on the near sheet
Here’s the other option, which sees you bringing the source data from the far away sheet before doing the work on the near sheet.
Avoiding cross sheet linking makes models easier to trace
With just a little more thought applied to how complex formulas are built up, and taking care to comply with the “no cross sheet references” rule, Excel spreadsheets suddenly become easier for someone else to inspect and trace.
Hitting on “F2” provides a new user with some useful information straight away (they stand a chance of seeing the coloured boxes Excel throws up, showing the components that feed into the formula). And from there they can navigate to each piece, making use of “ctrl” “[” and then “F5 enter” to bounce backwards and forwards quickly following the model’s workings.
At that point (compared to the alternative of multiple complex formulas that work across different sheets) making the effort to comply with the “no cross sheet linking” rule really does feel like it’s paying dividends!
