Pitfalls to avoid

In constructing your formulas, it is easy to get carried away and, very soon, the formula becomes very complex and unfriendly. While it is desirable to keep formulas compact, they should be simple and easy for a third party to follow. If necessary, break up the formula into two or more parts so that it becomes easier to follow while retaining the original effect.

Alternatively, you can use Alt + Enter to force part of the formula to the next line. This will not affect the result of the formula, but it will make the formula easier to understand. Consider the following example:

=INDEX(C5:G10,MATCH(J20,C5:C10,0),MATCH(K19,C5:G5,0))

This complex formula can be broken down into three parts with the use of Alt + Enter, as follows:

=INDEX(C5:G10,
MATCH(J20,C5:C10,0),
MATCH(K19,C5:G5,0))

The formula remains within the same cell but is displayed on 3 lines. As we can see, this makes it easier to decipher.

Protect sheets

If you are going to share your model...