Stop Rows Being Inserted and Do a Calculation

Two for one formula

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.

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.

array_3Final table below.



Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.