Most users don’t know what an array formula is and many people avoid them, but they can stop rows being inserted.

Have a look at the table below.

We want to calculate the margin AND we don’t want anyone to insert rows in the table. A multi-cell array formula can achieve both.

1. First select the range D2:D13.

2. Enter the following formula – its easiest to use the mouse to select the ranges

=B2:B13-C2:C13

3. Hold the CTRL + SHIFT keys down and press ENTER.

4. Done!

Things to note

• You have to use Ctrl + Shift + Enter (CSE) to enter array formulas.

• When you use CSE it places curly brackets around the formula see below.When you see those it means it’s an array formula. The formula is identical in all the cells.

• Excel won’t allow you to change part of multi-cell array and so it stops anyone from inserting or deleting rows within the range. Error message below is displayed.

• If you forget to use CSE then the formula may return an error or incorrect results.

Final table below.