Prevent user from deleting rows or columns 1

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

Prevent user from deleting rows or columns 2

  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 here


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


Topics that I write about...

Download Smart Ebooks on
Excel and Power BI