Watch Video – How to Lock Formulas in Excel
Excel formulas are easy to create and edit in Excel.
You can easily edit a formula through the formula bar or directly in the cell.
While this makes it convenient to create formulas in Excel, it comes with a few disadvantages as well.
Consider this.
You are going through a worksheet full of formulas, and you accidentally hit the delete key, or backspace key, or some other number/alphabet key.
Now you’ll be lucky if you’re able to spot the error and correct it. But if you are not, it may lead to some erroneous results.
And let me tell you this, errors in Excel have cost millions to companies (read this or this).
The chances of such errors increase multifold when you share a file with colleagues or managers or clients.
One of the ways to prevent this from happening is to lock the worksheet and all the cells. However, doing this would prevent the user from making any changes to the worksheet. For example, if you’re sending a workbook to your manager for review, you may want to allow him to add his comments or change some cells.
A better workaround to this is to lock only those cells that have formulas in it.
How to Lock Formulas in Excel
Before I show you how to lock formulas in Excel, here is something you must know:
Here are the steps to lock formulas in Excel (explained in detail later on):
- Select all the cells and unlock these.
- Select all the cells that have formulas (using Go To Special).
- Lock these selected cells.
- Protect the worksheet.
Now that I have outlined the steps above, let’s dive in and see how to do this (and more importantly, why we must do this):
Step 1: Select All the Cells and Unlock it
While you may find it confusing, bear with me and keep reading.
As I mentioned, only the cells that are locked as well as protected can truly be restricted. If all the cells are locked, and I protect the entire worksheet, it would mean a user can’t change anything.
But we only want to lock (restrict access) to the cells that have formulas in it.
To do this, we first need to unlock all the cells and then select and lock only those cells that have formulas in it.
Here are the steps to unlock all the cells:
- Select all the cells in the worksheet (use the keyboard shortcut Control + A).
- Use the keyboard shortcut Control + 1 (hold the Control key and then press 1). This will open the format cells dialog box.
- In the format cells dialog box, select the Protection tab.
- Uncheck the ‘Locked’ option.
- Click ok.
Step 2: Select All the Cells that Have Formulas
Now that all the cells have been unlocked, we need to make sure that the cells that have formulas are locked.
To do this, we need to first select all the cells with formulas.
Here are the steps to select all the cells that have formulas:
- Select all the cells in the worksheet (use Control + A).
- Go to Home and within the Editing group, click on Find & Select.
- From the drop-down, select Go to Special.
- In the Go To Special dialog box, select Formulas.
- Click OK.
This would select all the cells that have formulas in it.
Step 3: Lock the Cells with Formulas
Now that we have selected the cells with formulas, we need to go back and lock these cells (enable the lock property that we disabled in step 1).
Once we do this, protecting the worksheet would lock these cells that have formulas, but not the other cells.
Here are the steps to Lock Cells with Formulas:
- With the cells with formulas selected, press Control + 1 (hold the Control key and then press 1).
- In the format cells dialog box, select the Protection tab.
- Check the ‘Locked’ option.
- Click ok.
Step 4 – Protect the Worksheet
Now that the ‘Locked’ property is enabled for cells with formulas (and not for other cells), protecting the entire worksheet would only restrict access to the cells with formulas.
Here are the steps to protect the worksheet:
- Go to the Review tab.
- Click on Protect Sheet.
- In the Protect Sheet dialog box, make sure the option ‘Protect worksheet and contents of the locked cells’ is checked.
- [Optional] Specify the password.
- Click OK.
Once you are done with the above four steps, all the cells that have formulas would be locked, and the user wouldn’t be able to change anything in it.
If the user tries to change the cells, he/she will get a prompt as shown below:
Also read: How to Freeze Multiple Columns in Excel
How to Hide Formulas in Excel
When you lock formulas in Excel, the user can’t make any changes to the cells with formulas.
However, if that cell is selected, the formula in the cell would be visible in the formula bar.
While this isn’t an issue in most cases, but if you don’t want the formula to be visible, you need to hide it.
Here are the steps to hide formulas in locked cells:
- Select all the cells in the worksheet (use Control + A).
- Go to Home and within the Editing group, click on Find & Select.
- From the drop-down, select Go to Special.
- In the Go To Special dialog box, select Formulas.
- Click OK. This will select all the cells that have formulas in it.
- Press Control + 1 (hold the control key and then press 1). This will open the format cells dialog box.
- In the Format Cells dialog box, go to the Protection tab.
- Check the Hidden option.
- Click OK.
Now, when the user selects a cell that has a formula and is locked, he/she will not be able to see the formula in the formula bar.
Note: As mentioned earlier, a cell that has not been locked cannot be protected. The same applies when you hide formulas in Excel. Unless the cell is locked, only checking the Hidden checkbox wouldn’t do anything. To truly hide formulas in Excel, the cells should have the Locked and Hidden check boxes selected, and then it should be protected.
You May Also Like the Following Excel Tutorials:
34 thoughts on “How to Lock Formulas in Excel (a Step-by-Step Guide)”
thank you. indeed very useful. Can you please tell us if we can prevent editing the cells based on dates?
Thanks for the video, this was Exactly what we were looking for, how to lock cell’s with formulas. And thanks for the bonus info, on how to hide the formulas.
Thanks for the Great tutorial! follow the steps and this will work.
Great video, thank you!
Exel lent tutorial. Very well exposed, explained and illustrated. Highly recommended. Thank you.
I enter the same formula in cell C1, C2, and C3. For example:
=A1*B2
On Monday I enter into A1: 12
Enter into B2: 10
I get an answer of 120 in cells C1, C2, and C3.
Perfect.
On Tuesday I have a new set of numbers for a different result.
But I need to save the answer of 120 from Monday in cell C1.
How can I lock that cell so that when I enter my new data into A1 and B2 it doesn’t change the answer from the previous day in cell C1 (120), it only enters the new data in cell C2 and C3?
I have multiple cells using the same formula but I need to save the results in each cell and protect it from changing when I enter new numbers in the other cells using the same formula.
Is it possible?
Please help!
208-610-3300
Thank you,
Leigh Britton
Thank you, very cool and easy to follow!
Thanks for freely sharing your vast knowledge of Excel here! I bet I visit this site at least monthly; there is always something new to know! With gratitude, Bx
Once you lock the formulas the entire sheet gets locked and I can’t seem to input figures. So, I have a formula in the total=SUM(O6:T6). I need people to input their projections in cells O6:T6. When I lock the sheet and formulas, I cannot put figures in the other cells. Please advise
Hi Jayshree, The same question to yours. Do you find out the way to solve it?
This worked perfectly for me, I followed the above directions and only locked the cells with the formulas. If there is a formula in a cell no one should be entering any information in that cell. following the logic above if inputting in O6:T6 the locked cell should actually be s6 with the formula. The search for only formulas was the magic.
Dear how we can exclude a cell from total; this cell also have formula for calculation like =300000-(50000+10000)
Hi thank you for explanations. When I use these steps everything works as expected … but I discovered that I loose filtering feature for my columns which I still need to have. Is there workaround?
Is there a way to lock the cells but still insert rows WITH AN AUTOPOPULATING FORMULA? I have a budget worksheet with columns A-J and Rows 3-33. Columns that have entry fields include:
B – Average Cost for row 1, 2, 3…
C – Option “fixed” or “variable” cost
D – Range of variance if variable cost
Columns E-J include:
E – Maximum cost for row 1, 2, 3…
F – Minimum cost for row 1, 2, 3…
G – Rows are divided into “groups” to indicate budget category. Column G sums total costs for category
H – Calculates % of budget allowance OR total costs
I, J – calculate above percentage using maximum/minimum cost calculations
Each group/category currently has four rows allotted, but users might need to add row(s) if there are more costs in that category. (Example: For housing costs I might have a row for 1) mortgage 2)electricity 3) utilities 4) maintenance 5) Project X fund 6) Security, etc.). Columns E-J are all formulas that don’t need to be touched, but that need to be “carried down” if user adds a row.
I tried a macro that auto-populates auto-adds a row with formulas using a double click, and that works well…when the sheet isn’t protected. I also tried setting it up as a table and that works fine, too, until I protect the sheet/associated formula columns. Despite giving permissions to add/delete new rows, columns, etc. an error message appears when I try to run the macro. Manually inserting a row – both using the macro version and the table version – results in a new row without the formulas.
It seems like there’s got to be a workaround as this seems like a pretty basic function for a straight-forward spreadsheet. Any insight or advice?
Thanks so much!
I’m curious for this answer, I decided for a work around to add an extra blank column with all of the formulas in it I can use if needed. I would like to protect my formatting but alas its late and I need to rest these eyes and this overloaded brain. Enuf fun for the evening. Cheers
Hi,
Enjoyed the video – very helpful.
Can I protect a cell so that the formula can not be removed but I can still change the source data to reflect a new value in the cell?
Is this the “hidden” feature?
Hi this is a simple way of understanding. I also would like to know how to lock the formulas in multiple sheets at a time which are in one work book. Like in one work book, sheet 1, sheet 2,….. sheet 30.
Thank you!
excellent support! it was so easy for me to follow through. Many thanks!
Chirac
Very helpful tutorial. thanks!
Your instruction locks the entire worksheet.
Hello sir
I am a businessman and daily I use Excel I need your help in making it better as well as I want to access the files on my mobile too so can I get you mobile no so I can send you my Excel to understand it better
I am trying to figure out how to simulate a basketball score spread sheet that will generate a random score between selected integers for each of four quarters (or two halves) for several different teams during a playoff tournament. I know how to generate random numbers and to total the four quarter scores but how can I repeat this several times for each of 16 (or more) playoff games? I am fascinated by these formulas.
The article proved very helpful. It gave me all what i wanted. Very clear and easy to understand instructions. Thank you.
i have a formula, =Ordersinhouse!H126, that fills in a cell, i want to fill in the cell with this data but want to block/lock keyboard entries in this cell, is this possible?
This is so helpful, simple clear and concise instructions. Makes me look like an expert. Thank you so much
Thank you for the kind words Priscilla! Glad you found the tutorial useful.
Can this be applied to an entire FILE, not just the worksheet?
it can be applied to the whole file. so in order to do that, you have to select worksheet locked instead of Spread Sheet Lock
Thank you! I just found your site, and it has already helped me immensely.
Your instructions are wonderfully clear and detailed just enough without going overboard. Awesome!
Glad you found it useful!
Hi. Nice article. There is no thing more evil to do than to hardcode a value to a verification column.
There is a typo in the section How to Lock Formulas in Excel in the second step 2. Select all the cells that have formulas (using paste special). It should be Go To Special, not paste special. Typo can cause confusion.
Many thanks for you, Awesome Tips
Very nice and simple.