Stop excel users from inserting or deleting rows

A common requirement is how to stop Excel users from inserting or deleting rows. Typically a template is set up and the author does not want any changes to it, especially with regards the rows and columns. Generally no inserting or deleting of rows is allowed.

Stop insert with Protect sheet option (even without a password)

The first option is to protect the sheet using Excel’s Protect Sheet option. Once you have set up your spreadsheet, you can decide what you want to protect by clicking on Review and Protect Sheet. To understand how this works and all you can do with it, look at our protection options in Excel.

Stop Excel users from inserting or deleting rows

This is an effective way to stop excel users from inserting or deleting rows but it does mean you need to think through the other protection options (which is not a bad thing).

Trick to only protect insert/ delete and nothing else

The above works well but you then need to decide all the other protection options you want. An alternate is to unlock all cells and then put the above protection on.

So first highlight all the cells in your spreadsheet (active area or you could highlight the whole spreadsheet) and choose FORMAT cells. On the PROTECTION tab make sure that LOCKED is unticked.

Now click on REVIEW and Protect Sheet. As shown below, tick everything (allows users to do it) EXCEPT for the Insert Columns, Insert Rows, Delete Columns and Delete Rows.

Stop Excel users from inserting or deleting rows

Now users will be able to do most things as per a normal, unprotected spreadsheet, but when they try and insert or delete rows orcolumns it will be greyed out.

Stop Excel users from inserting or deleting rows

Stop insert without using Protect sheet and no VBA

Even when we tick all the options in the Protect Sheet, by default some things still can’t be done. We love using the Formula Auditing tools (trace precedent and trace dependants), but if the Protect Sheet option is chosen, even if all the cells are unlocked and anything is allowed, these buttons are still greyed out.

A trick is to use an array formula to stop any insert or delete. This is because when you set up an array formula over a selection of cells, they have to stay together. Excel will not allow you to insert or delete within the array.

As shown below,

  • highlight a selection of cells (J2 to J30),
  • Type =1 (or anything else you want),
  • Hold down CTRL and  SHIFT,
  • Click ENTER.
Stop Excel users from inserting or deleting rows

You now have an array formula (note the { } around the formula). If a user tries to insert or delete a row they will get the error message “You can’t change part of an array”.

Stop Excel users from inserting or deleting rows

You can do the same thing with a row to stop the inserting or deleting of columns.

We often hide these columns and rows and label them explaining why they are there.

Related

Excel won’t insert a column or row

Excel slicer with password protection

Protect Excel cells and sheets