If you have a range in Excel where you don’t want someone to insert or delete rows you can protect the sheet to stop them. If you can’t or don’t want to protect the sheet then there is a technique you can apply to stop rows being inserted and deleted.

Array formulas are advanced formulas that I will cover in a number of future blog posts, but in their simplest form that can be used to stop rows or columns being inserted or deleted.

All array formulas have to be entered in a certain way. Instead of pressing Enter, you have to hold down the Ctrl + Shift keys and then press Enter. This places curly braces { } around the formula which defines it as an array formula. See the formula bar in the image below.

Try this on a blank sheet.

1. Select the range B1:B10
2. Press the = key and click on cell A1
3. Hold the Ctrl + Shift and press Enter
4. Try to insert or delete a row between rows 1  to 10. You should see the dialog below.

The formula itself doesn’t really do anything except link all the cells to A1, but because its array-entered it stops any rows being inserted or deleted within the array. You can hide this column if you need to.

If you have the range in the middle of the sheet you can still insert or delete rows above and below the range.

To enable inserting and deleting select the whole range containing the array formulas and press the Delete key.

Selecting a range across the sheet will stop columns being inserted or deleted.