In this video tutorial we will see how we can prevent users from deleting rows and columns in Excel Sheet.
This is the smart way of creating the access control on your spreadsheet trackers, reports, dashboards other than the normal routine method of protecting the entire worksheet/spreadsheet.
Let us consider a sample data range like shown below, and let us try to restrict user from deleting any row or column.
Step 1: First, let us select the cells D8 to D16, as shown below.
Step 2: By selecting these cells, type formula as =1 and press Ctrl+Shift+Enter.
You will observe that all the selected cells will have the value as 1, as we have entered an array formula.
Step 3: Now, try to delete any row, you will get a error message saying " you cannot change part of an array."
Step 4: Repeat the same process as in Step 1&2 for 1st row, to enter values
Step 5: Now try to delete any column in the data table, you will get the same error message again.
You can format these additional Row and Column in white color, so that any user would not be aware of the array formula and by this, you can restrict them from deleting any row or column from the data table.
Of course, the common way to restrict user from deleting rows or columns is by protecting the sheet, which will be clearly known to the user that the sheet has been protected.
So friends, in this way you can Prevent Users From Deleting Rows and Columns in Excel
Watch this video tutorial for better understanding:
If you liked this tutorial, share it with your friends. And also you can follow us on Youtube, Twitter and Facebook. We would love to hear from you, Please do comment, suggest or compliment our work and we shall make it better for you. You can write us at dptutorials15@gmail.com