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
3. Hold the CTRL + SHIFT keys down and press ENTER.
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.