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.
Table of contents
YouTube Stop Insert or Delete of rows in Excel
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.
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.
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 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.
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”.
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
How to remove too many cell formats in Excel