Stop rows being inserted or deleted in Excel

An array trick

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 EnterArray Entered formula
  4. Try to insert or delete a row between rows 1  to 10. You should see the dialog below. Array Error message

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.

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.