Prevent user from deleting rows or columns in Excel

When you are creating a Dashboard or a Report, you may have felt the need to prevent the user from deleting either rows or columns

This is how it should work – When the user tries to delete a row/column, Excel should return an error or shouldn’t let that task happen

Here is how you can do it!

Assume that we have some data in the range B2:B12 and the user should not be able to delete any rows between that range

1. In column E (or any adjacent column), select the range E2:E12
2. Type =1
3. Then confirm the formula by pressing CTRL + SHIFT + ENTER

What you have done is created an array in the range E2:E12. Now deleting any single row between this range is not possible until you delete all the rows.

Each time you try to do it Excel will return an error “You can’t change a part of an array” Woot!

And you are smart enough to understand that you can do the same for the columns as well. I learnt this trick from Mr. Excel aka Bill Jelen

If you are curious to checkout the spreadsheet you can download it from below 

Other Smart Tricks like this

1. Make a Chart in 1 Key Stroke
2. Filter Data by Selecting cells
3. Work on multiple sheets using New Window in Excel
4. Smart Border Shortcuts
5. Work on Multiple Sheets at Once