The purpose of this blog post is to teach you how to lock / protect formula part only in Excel Dataset.
Protecting or locking formula parts is one of the most important skills to learn in Excel.
Excel datasets require a lot of effort to design formulas. And when we share the Excel file with a colleague, there may be a high chance that the formula will be changed or edited.
Which could be harmful to you. To avoid this, we will present in this tutorial how to lock / protect formula parts only instead of the entire worksheet.
What Is Protection Property?
To begin with, you need to know that Excel has a protection property for each cell, and you can choose to enable or disable this for any cell.
We have applied some formulas to Column E (Total) in the image below.
By using the protection property, we will only protect the formula part and leave the rest of the dataset editable.
This means you cannot make any changes inside the formula part, but you can edit the rest of the dataset.
See the dataset below we are using in this tutorial.
If you want to protect the entire worksheet, but the protection property is disabled, then you cannot do so.
In the same way, if the protection property is enabled, but you do not select ‘Protect Sheet’ in the “Review” tab, you cannot protect the worksheet.
Therefore, to protect a worksheet, you need to click the “Protect Sheet” button in the “Review” tab and ensure that the protection property is enabled.
How to Find the Protection Property.
In case you want to disable the protection property in a cell or selected area or the whole sheet.
You need to follow the further steps.
Step 1:- In the first step, you need to select the area where you want to disable the protection property.
Suppose that we want to disable it from the entire worksheet.
To disable it from the entire sheet, we will select the entire sheet by clicking the triangle before ‘Column A’ and ‘Row 1’, see image below:-
Step 2:- Then Right-click any cell of the selected area
Step 3:- You will get the ‘Format Cells’ dialogue box after right-clicking on “Format Cells” on the right-click properties.
Step 4: – You can see multiple tabs here, including – Number, Alignment, Font, Border, Fill, and Protection.
Click on the ‘Protection’ tab and you’ll see two options with checkboxes.
The first option, Locked, is marked as ‘Ticked’.
It indicates that the protection property is enabled for entire worksheet.
The second option, Hidden is not ‘ticked’ and we will learn more about this later in this post.
REMOVING this tick from the ‘Locked’ option will disable the protection to entire worksheet, and you will no longer be able to protect the worksheet.
Therefore, make sure that this option is always remain checked.
Note: – by default this option is always ticked on.
How to Make Protection Property Enable/Disable
Marked the “Locked” option as ticked to make protection enable and un-check the “Locked” option will disable it.
To remove the protection property from the selected area, need to follow the step below.
Simply remove the ticked option and press ‘Ok’ to proceed.
By clicking “Ok’ here, you will disable the protection property for the selected area.
Now let’s protect the sheet and see that whether we can edit the dataset or not.
Go to the ‘Ribbon’ and then go to the “Review” tab and click the ‘Protect Sheet’ button, as shown in the image below.
By clicking on ‘Protect Sheet’, a Protect Sheet dialogue box will open with multiple options.
You can enter the password to open the locked sheet to edit or modify data. We are not using a password here, so leaving it blank.
And click on the ‘Ok’ button below.
Now see that your worksheet appears as protected, you have to ‘unprotect’ it to enter any data.
This means that your worksheet is now protected and you cannot enter any data.
Now let’s see if we can edit the dataset or not.
See in the image below, we have edited some data in ‘ROW – 4’ and our worksheet is still in the Unprotected Sheet’ mode.
Note: – In general, we cannot enter the any data without removing the ‘Unprotected Sheet’ mode.
This is all happening because we have disabled our ‘Protection Property’.
Therefore, to make the worksheet protected, we must first enable property protection.
By this example, we understand that protection properties can be enabled or disabled for any specific area in Excel.
Note: – Again, we’ll mark the protection property as enabled as in the default mode.
Select the entire worksheet and right-click in any cell, then click on the ‘Format Cells’ option and choose the ‘Protection’ tab, then select the ‘Locked’ option and click ‘Ok’ at the bottom.
By doing this, our protection property is enabled again.
How to Lock / Protect Formula Part Only
As you can see below, we have a dataset where we want to make the formula area as protected, so that it cannot be edited or modify or changed.
But can easily enter the data at the rest of the area in the dataset.
To do this, we will follow the below steps.
Select the whole sheet —>then Right-click from mouse —> click on the ‘Format Cells’ option from right-click properties.
In the ‘Format Cell’ dialogue box, go to the ‘Protection’ tab and un-check the ‘Locked’ option.
And press ‘Ok’ at the bottom.
By doing so, the protection property is disabled now for the whole selected area in the worksheet.
Now we will make protection property enable for only selected area.
In the next step, select the area that you want to prevent others from editing or making changes, such as the ‘Formula column’.
Press Right-Click in any cell of the selected area from the mouse and click the ‘Format Cells’ option.
Format cells dialogue box will open up.
Go to the ‘Protection’ tab and ticked-on the ‘Locked’ option.
See the image below:-
And press ‘Ok’.
This means protection property is enabled for selected areas only.
Follow these steps to make this selected area fully protected from being edited or changed.
Go to the ‘Review’ tab in the ‘Ribbon’ area and click on the ‘Protect Sheet’ tab option.
Make sure the ‘Protect worksheet and contents of locked cells, must be ticked on.
You can enter a password for additional security when opening the file in the password area.
If you do not want to enter the password then leave this place blank.
And press ‘Ok’.
Finally, your selected dataset is protected now and nobody can do any changes or modify this area from now.
If you want to edit any other part of the dataset, you can do that, but if you want to edit any part of the protected area, you cannot make any change.
As you can see, we have made some changes to the dataset apart from the protected area.
We did this in sheet protection mode without making our sheet Unprotected.
Because the protection property is only enabled for the ‘Formula’ area, but disabled for the rest part.
Now attempt to make some changes within the formula part. You will be unable to do so and receive the following error message.
Because the protection property is enabled for this particular area.
So nobody can enter or modify this area.
How to Hide the Formula in the Formula Bar
Now click on any cell where you have to apply some formulas, and see that the formulas are visible in the formula bar.
If you want that nobody can see this formula in the formula bar then I have a solution for this also.
In the below image, you can see the formula visible in the formula bar.
Step 1 – Unprotect the sheet by clicking on the “Unprotect Sheet’ option in the ‘Review’ tab.
Step 2 – Then again select the area where you have applied the formulas and press right-click from the mouse.
Step 3 – Click on the ‘Format Cells’ option and the ‘Format Cells’ dialogue box open-up.
Step 4 – Go to the ‘Protection’ tab and mark the second option ‘Hidden’ as ticked on.
And click ‘Ok’ at the bottom.
Now both properties are enabled for the selected dataset.
Step 5 – Again go to the ‘Review tab’ and click on the ‘Protect Sheet’ option here.
If you want to enter any password then use it in the password area otherwise leave it blank.
And press ‘Ok’ here.
Step 6 – Now, click any of the formula cells to see that our formula is no longer visible in the formula bar.
So we can protect our formula part in any dataset in Excel using these techniques.
If you want to learn more about this, please visit Microsoft Office Support.
I hope you find this tutorial helpful.
Please feel free to put your comment or suggestion in the comment box below.