What does this mean on Microsoft’s website?

[VBE313]

Can someone please give me a detailed example of what this means?

Do not use forward referencing and backward referencing

To increase clarity and avoid errors, design your formulas so that they do not refer forward (to the right or below) to other formulas or cells. Forward referencing usually does not affect calculation performance, except in extreme cases for the first calculation of a workbook, where it might take longer to establish a sensible calculation sequence if there are many formulas that need to have their calculation deferred.

 

[Yongle]

Excel formulas are calculated ONE BY ONE in a specific sequence (eg A1 before B1 before C1 before D1 before E1 etc)
Excel calculates every cell, then recalculates every cell affected by calc1, then recalculates every cell affected by calc2, then recalculates every cell affected by calc3 etc....
If there are lots of cell dependencies within the same cell, Excel may need to recalculate that cell multiple times as it resolves each one
Knowing calculation sequence can guide how a worksheet should be structured to reduce the number of recalculations

A simple illustration
With formula in cell A1
=E1
calc A1...E1..A1 (3 calculations)
- value in E1 is not resolved when A1 is evaluated and Excel must recalculate A1 after E1 is calculated

With formula in cell E1
=A1
calc A1..E1 (2 calculations)
- A1 was evaluated first and its value resolved before E1 is calculated


Explanation found at https://www.techrepublic.com/blog/10-things/10-ways-to-improve-excel-performance/

Work from left to right

This tip is easy to implement because data tends to flow from left to right naturally, but it doesn't hurt to know that there's a little more going on under the hood. By default, Excel will calculate expressions at the top-left corner of the sheet first and then continue to the right and down. For this reason, you'll want to store independent values in the top-left portion of your sheet and enter expressions (dependent cells) to the right or below those values. In a small sheet, you won't notice much difference, but a sheet with thousands of rows and calculations will definitely perform better when you position dependent cells to the right and below the independent values.

In technical terms, this behavior is called forward referencing. Formulas should be to the right or below the referenced values. Avoid backward referencing, where formulas are to the left and above the referenced values.

 

[BristolJGM]

Bump...

Am I right in saying that the following calculation sequence would not involve any forward referencing?

A1 has value 1
A2 has a formula =A1+1
B1 has a formula =A2 +1

?

The reason for asking is that the MS website says this:

"To increase clarity and avoid errors, design your formulas so that they do not refer forward (to the right or below) to other formulas or cells."

Taken literally, cell A2 is "to the right" of cell B1 (it says "to the right" OR "below"). However, am I right that the wording is misleading because by default Excel will calculate the cells in the order A1, A2, B1?

 

[BristolJGM]

Just to add - by the same logic, if we have a numbered list of people in cells A1:B3 and then a VLOOKUP in cell C1, this will involve a forwards reference since cell C1 will be calculated before cell B3?

I'm asking because I have a very large workbook which is 'stalling' as Excel works out a calculation sequence...