How to Allow Users to Edit Ranges in Excel?

How to Allow Users to Edit Ranges in Excel?

Sometime we need to send an Excel file to other people for data feed. But there is a problem on it; you may think that the person will change the data of entire data sheet. In today’s article I will show you “How to Allow Users to Edit Ranges in Excel” so that the third person will unable to edit or delete any data except your preferred ranges where you want to feed the data by third person.

How to Allow Users to Edit Ranges in Excel

As you can see in the image below, there are four columns in my example worksheet (First Name, Last Name, Age and Address). The First three columns are already fielded and fourth column (i.e. Address) is empty. You want to field the data in Address column by the third person. 

Four Columns

In order to protect your sheet, go to “Review” tab and click “Allow Users to Edit Ranges”. A dialog box will appear (See below).

Alow users to edit ranges

Click to “New” to create a new record. A dialog box will appear (See below).

new range

Set a title in “Title” box. Click arrow sign at the right side “Refers to cells” box. Then drag and select the cell ranges which you want edit after protect worksheet. In my case, It will be F6 to F13 (Address column). Then again click arrow sign. Type “Range Password” and reenter the password (1st time). Now press “OK” to go back the “Allow Users to Edit Ranges” dialog box. You will see here “Protect Sheet” option. Click there. 

Protect sheet

A new dialog box “Protect Sheet” will open. Enter a “Password to unprotected sheet”. Tick all check boxes to fully protect the worksheet. Press “OK” and again reenter the password and press “OK” (2nd time). 

protect sheet dialog box

You will see now user cannot edit anything. If anyone wants to edit worksheet a massage will show (see image below). 

read-only mood

User can only edit “Address” column (i.e. cell F6 to F13). To edit the cells under “Address” column, user need to enter a password (1st time) which you set up at time of create a new record in “Allow Users to Edit Ranges”. 

unlock ranges

Now user can feed the data in “Address” column. 

After receiving the updated data file you need to unlock the whole sheet in order to edit worksheet.  To unlock the sheet, please go to “Review” tab and click on “Unprotect Sheet”; enter the password (2nd time). 

to unprotect

Please see the video below:


That’s it. It is very easy. Isn’t it?


I hope this article "How to Allow Users to Edit Ranges in Excel" will help you. If you have any problem, please comment below. I will definitely help you.

How to Allow Users to Edit Ranges in Excel? How to Allow Users to Edit Ranges in Excel? Reviewed by From Sovan on July 19, 2018 Rating: 5

No comments:

Powered by Blogger.