how to lock Cells and Protect Formula but allow data entry in Microsoft excel

admin ExcelComments330Read

I am going to learn how to lock cells in Microsoft excel.

I prepared a table here and as you can see I have the values 2 and 5.文章源自LuckYou.ORG-https://luckyou.org/116.html

and I have a formula which calculates the sum of these values文章源自LuckYou.ORG-https://luckyou.org/116.html

how to lock Cells and Protect Formula but allow data entry in Microsoft excel文章源自LuckYou.ORG-https://luckyou.org/116.html

Now I want to lock the workbook, so that the user can only edit the values in the orange fields.文章源自LuckYou.ORG-https://luckyou.org/116.html

And that this sum is still being calculated And also that the user cannot see the formula behind this field.文章源自LuckYou.ORG-https://luckyou.org/116.html

So to achieve this, we first highlight the two orange cells that we want the user to be able to edit later.文章源自LuckYou.ORG-https://luckyou.org/116.html

And then we go to the home tab, into the alignment section and click on this little arrow in the bottom right corner.文章源自LuckYou.ORG-https://luckyou.org/116.html

Now we go to the protection tab and remove the checkmark where it says “locked”.文章源自LuckYou.ORG-https://luckyou.org/116.html

how to lock Cells and Protect Formula but allow data entry in Microsoft excel文章源自LuckYou.ORG-https://luckyou.org/116.html

Then we click on ok and go to the file tab, and here in the info section文章源自LuckYou.ORG-https://luckyou.org/116.html

We click on protect workbook and then on protect current sheet.

how to lock Cells and Protect Formula but allow data entry in Microsoft excel

Now we can enter a password and if you would now lock the sheet

The user would only be able to edit these orange fields

But he would also be able to see the formula behind the field.

For example and to make sure that the user also cannot see the formula behind this field

We simply remove the check Mark where it says “select locked cells”

how to lock Cells and Protect Formula but allow data entry in Microsoft excel

Now we click on ok and we have to reenter the password again

And now when we click into the workbook

You can see that we cannot select any cell and see the formulas in this workbook

But we can still click into the orange fields which we highlighted and we can also change the values in here

And the formulas will still work

So that's it

 
匿名

Comment

Anonymous

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

Decide