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.

array_1

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.

array_2

• 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.

array_4

 

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.

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