How to lock and protect formulas in Excel?

When you create a worksheet, sometimes you need to use some formulas, and you don’t want other users to change, edit or delete the formulas. The easiest and most common way of preventing people from playing with your formulas is to lock and protect cells which contain formulas in your worksheet. Please go to lock and protect the formulas as follows:

Lock and protect formulas with Format Cells and Protect Sheet functions

Lock and protect formulas with Worksheet Design good idea3


arrow blue right bubble Lock and protect formulas with Format Cells and Protect Sheet functions

By default, all cells on a worksheet are locked, so you must unlock all of the cells first.

1. Select the whole worksheet with Ctrl + A, and right click, choose Format Cells from the context menu.

2. And a Format Cells dialog box will pop out. Click Protection, and unchecked the Locked option. Click OK. The whole worksheet has been unlocked.

doc-protect-formulas1

3. Then click Home > Find & Select > Go To Special, and a Go To Special dialog box will appear. Check Formulas from Select option, and then click OK. See screenshot:

doc-protect-formulas2

4. All of the cells which contain formulas will be selected.

5. Then go to lock the select cells. Right click on the selected cells, and choose Format Cells from the context menu, and a Format Cells dialog box will display. Click Protection, check the Locked checkbox. See screenshot:
doc-protect-formulas3

6. And then click Review > Protect Sheet, and a Protect Sheet dialog box will pop out, you can enter the password in the Passwordto unprotect sheet box. See screenshot:

doc-protect-formulas4

7. Then click OK. And another Confirm Password dialog box will appear. Reenter your password. And click OK.

doc-protect-formulas5

And then all cells which contain formulas have been locked and protected. In this case, you cannot modify the formulas, but you can edit other cells.


arrow blue right bubble Lock and protect formulas with Worksheet Design

If you have installed Kutools for Excel, you can lock and protect formulas quickly by using Worksheet Design utility.
Kutools for Excel, with more than 120 handy functions, makes your jobs easier. 

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

1. Click Enterprise > Worksheet Design to enable the Design group. See screenshot:

doc protect formulas 1

doc arrow

doc protect formulas 2

2. Then click Highlight Formulas to highlight all formula cells. See screenshot:
doc protect formulas 3

3.Select all the highlighted cells and click Selection Lock to lock the formulas. And there is a dialog pops out to remind you that the fomulas cannot lock until protect the sheet. See screenshots:

doc protect formulas 4doc protect formulas 5

4. Now click Protect Sheet to type the password to protect the sheet. See screenshots:
doc protect formulas 6

Note

1. Now the formulas are locked and protected, and you can click Close Design to disable the Design tab.

2. If you want to unprotect the sheet, you just need to click Worksheet Design > Unprotect Sheet.

In Worksheet Design group, you can highlight unlock cell, name range and so on.

Tip.If you want to quickly protect multiple sheets, please try to use the Kutools for Excel’s Protect Worksheet as shown in the following screenshot. It’s full function without limitation in 60 days, please download and have a free trial now.

doc protect multiple worksheets
 

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink +13 Tyler Stein
Thank you, this was helpful!
2013-10-31 16:27 Reply Reply with quote Quote
Permalink 0 brong
when i use formula of excel, but i do not want another people see the formula of me. so how to do it ?
2017-04-07 04:11 Reply Reply with quote Quote
Permalink 0 George Coyle
Excel protection for the PC
2013-11-21 17:53 Reply Reply with quote Quote
Permalink -1 Karl
:-) Great that really helps. My errant pen mouse would sometimes scrape the cell box and the formula would pop up, and not knowing, sometimes I had changed it. Caused many headaches while doing banking in the totals column. thanks again.
2014-01-16 21:12 Reply Reply with quote Quote
Permalink 0 RIJESH
:roll: NICE EXPLANATION GOOD ONE....
2014-01-20 07:43 Reply Reply with quote Quote
Permalink 0 Howard Walker
Quoting RIJESH:
:roll: NICE EXPLANATION GOOD ONE....

Well it looks good, but I have followed it at least 6 times and each time it does not lock any of my formulaes.
2017-05-02 12:19 Reply Reply with quote Quote
Permalink -1 JOSHY BOY
thank you this really helped :lol:
2014-01-27 16:20 Reply Reply with quote Quote
Permalink -2 JOSHY BOY
thanks alot it really helped
2014-01-27 16:21 Reply Reply with quote Quote
Permalink 0 vinay4125
Hi, please help me out, i have 3 columns (Ticket Number, Site ID, Time) i need to copy Site ID and Time according to the Ticket Number from 1st Excel sheet to 2nd excel sheet

For Example : I Have Ticket Number 425665 with Three Site ID's UW_GJ_2904 , UW_GJ_1995 , UW_GJ_0960 , So i need to copy all three site id's from 1st Excel sheet to 2nd excel sheet .... But as per vlookup only first site ID is Reflecting UW_GJ_2904 is Reflecting in all three places, which it should not(in some cases for single ticket Ex: 425771 : only one site UW_GJ_0514 will be there, it was reflecting properly, The problem is for each ticket which has more than one site ID is not reflecting properly)

First Excel Sheet :
Ticket Site id time
425665 UW_GJ_2904 1/21/14 4:51 PM
425665 UW_GJ_1995 1/21/14 4:51 PM
425665 UW_GJ_0960 1/21/14 4:51 PM

Second Excel Sheet:
ticket site id time
425665 UW_GJ_2904 1/21/14 4:51 PM
425665 UW_GJ_2904 1/21/14 4:51 PM
425665 UW_GJ_2904 1/21/14 4:51 PM

if it is only one id its Reflecting properly
First excel sheet:

425771 UW_GJ_0514 1/21/14 7:44 PM

Second Excel Sheet:

425771 UW_GJ_0514 1/21/14 7:44 PM
2014-01-28 16:05 Reply Reply with quote Quote
Permalink -1 raja
I want to lock the formula cell but it has use to drop and copy
2014-02-04 19:57 Reply Reply with quote Quote
Permalink +2 Umer
only one cell like to lock which kept the formula
2014-03-13 11:56 Reply Reply with quote Quote
Permalink -3 sanjaya
;-) thank you very much... :-)
2014-03-22 12:53 Reply Reply with quote Quote
Permalink -4 DEEPAK KHANAL
After long period i found my problem i.e. locking/protect ion single sell on excel. Thank you very much www.extendoffice.com
2014-04-03 05:28 Reply Reply with quote Quote
Permalink -1 GG
The BEST explanation I've found to do
This. Thank you!!
2014-04-09 00:43 Reply Reply with quote Quote
Permalink -1 Omen
it is very usefully thanks
2014-04-29 06:02 Reply Reply with quote Quote
Permalink -1 Marty
Protect Formulas in Excel Spreadsheet
2014-04-29 19:25 Reply Reply with quote Quote
Permalink 0 Muhammad Tahir
It is wonderful guide, I have ever seen.
Thanks so much for saving my hardwork.
2014-05-30 11:24 Reply Reply with quote Quote
Permalink 0 Peter Fisher
Thanks very much. This was most useful.
2014-06-04 10:24 Reply Reply with quote Quote
Permalink 0 Ali Khan
Thank you! it was helpful.

Ali
2014-06-16 06:40 Reply Reply with quote Quote
Permalink -1 Nato
Dear author, thank you very much for such a useful guide.
Please, advise if it is possible to lock the formula cells in excel so that it is possible to delete other bulk data from the file without deleting the formulas? With the method above, it is not possible to select whole data with ctrl+A and delete.

Thank you!
2014-07-09 13:47 Reply Reply with quote Quote
Permalink +1 sexy_ella
hi...thank you so much...this was HELPFUL...... :)
2014-08-01 06:36 Reply Reply with quote Quote
Permalink -1 Yu wai
Thanks a lot..
Glad to know to lock the cells
2014-08-15 10:28 Reply Reply with quote Quote
Permalink -2 Yu wai
Thank a lot.
It is convience for all.
2014-08-15 10:34 Reply Reply with quote Quote
Permalink -1 Clau
Very helpful, thank you so very much!!Blessings !
2014-08-22 18:47 Reply Reply with quote Quote
Permalink -1 firdoush alam
Thanks..satisfi ed, is there any possibilities of querry through e-mail.
2014-08-30 04:57 Reply Reply with quote Quote
Permalink -1 measbunna
Thanks very helpful for me. :)
Best regards
2014-09-11 07:52 Reply Reply with quote Quote
Permalink -2 Shyam Sunder Singh
Awesome description !

Very helpful, thank you so very much!!Blessings !
2014-09-16 12:19 Reply Reply with quote Quote
Permalink 0 Jamie
I want to have users be able to edit column 1 and column 2 but not be able to edit anything in column 3 (the formula) yet have it add up. I would like to lock only the column with the formula. When I do this it does not allow me to edit the other cells (which the formula adds up).Please help!
2014-10-03 13:21 Reply Reply with quote Quote
Permalink +1 Nyiko
Thank you. Finally, i got the answer
2014-11-19 08:14 Reply Reply with quote Quote
Permalink 0 SA
Thank you very much,
this is of great help!
2015-01-28 20:23 Reply Reply with quote Quote
Permalink 0 Natasha
In step 5, user should be selecting Locked AND Hidden in order for the formula not to show and be able to be edited by others without changing formula.
2015-02-20 17:50 Reply Reply with quote Quote
Permalink 0 Omar
Hi if i protect the formula in excel before send it by email the person who recive the email can use the sheet with formulas or not??
2015-02-23 10:55 Reply Reply with quote Quote
Permalink 0 Ani
Hello I tried This But my other cells are also locked which dont have any formula
kindly help.
2015-03-16 14:52 Reply Reply with quote Quote
Permalink +1 accountant
Good and clear explanation
2015-06-18 14:50 Reply Reply with quote Quote
Permalink +1 shahzada umer
i want to know about how to lock the cell in excell sheet. that nobody intrupt the specific cell figure.
2015-08-29 06:49 Reply Reply with quote Quote
Permalink 0 Amir Muzaffar
Awesome Tut
and well explanation
Thanks for this help
2015-11-09 05:07 Reply Reply with quote Quote
Permalink 0 raj
si want to freez the formula in one sheet of the book , like daly am receiving the files day wise as summry-15 & summry-16, i need certain summry so i made summry in one sheet (in the same book) using vlookup & hlookup ,but when am pasting the formula in summry-16 formula is taking the data from suury15 only i need formula has to take from the current book only(which my summry sheet part of the book).
2015-11-19 11:19 Reply Reply with quote Quote
Permalink 0 XZa
asdlkfjsdlk alsdkjflas fka klsdlfkjasdl adjlasdfkj lasdf
2016-02-19 09:16 Reply Reply with quote Quote
Permalink 0 pat
What I need is a way to use 2 different passwords on a shared worksheet. I need to be able to lock my formulas and then when someone puts in the data they need to lock and protect with another password. Is this possible?
2016-04-06 20:15 Reply Reply with quote Quote
Permalink 0 Lisa
Thanks! This was a massive help, I should have turned to you straight away instead of battling on for hours!
2017-02-02 07:23 Reply Reply with quote Quote
Permalink 0 Philip Hales
Have gone through this half a dozen times, yes! it is locking cells containing formula, but all formatting is greyed out.

I have an Allocation Sheet, which has DAYS and NIGHTS on if we do DAYS Nights formula is strikethrough, and vice versa, but change DAYS to NIGHTS, but not from strikethrough!
Cannot change FONT colour either.
2017-02-24 08:41 Reply Reply with quote Quote
Permalink 0 Philip Hales
This locks the cell with the formula, but also is preventing changing FONT colour and Strikethrough etc?
2017-02-24 08:42 Reply Reply with quote Quote
Permalink 0 Heiko
This is the only page that showed step 5. And all my formulas are protected. But how do I protect formulas AND all the text I put in. The Workbook I created is an invoice ledger. So I have cells with text, that is not protected. All I want them to do is fill in the ledger with the purchase dollars. Not to be able to change category names, or pager titles.
2017-04-06 23:23 Reply Reply with quote Quote
Permalink 0 Srikanth C
very useful good explanation thanks
2017-06-06 11:45 Reply Reply with quote Quote

Add comment


Security code
Refresh