How to hide and lock formulas in Excel
This tutorial shows how to hide formulas in Excel so they do not show up in the formula bar. Also, you will learn how to quickly lock a selected formula or all formulas in a worksheet to protect them from being deleted or overwritten by other users.
Microsoft Excel does its best to make formulas easy to interpret. When you select a cell containing a formula, the formula displays in the Excel formula bar. If that's not enough, you can evaluate each part of the formula individually by going to the Formulas tab > Formula Auditing group and clicking the Evaluate Formulas button for a step-by-step walkthrough.
But what if you don't want your formulas to be shown in the formula bar, nor anywhere else in the worksheet, for confidentiality, security, or other reasons? Moreover, you may want to protect your Excel formulas to prevent other users from deleting or overwriting them. For example, when sending some reports outside your organization, you may want the recipients to see the final values, but you don't want them to know how those values are calculated, let along making any changes to your formulas.
Luckily, Microsoft Excel makes it fairly simple to hide and lock all or selected formulas in a worksheet, and further on in this tutorial we will show the detailed steps.
How to lock formulas in Excel
If you've put a lot of effort in creating an awesome worksheet that you need to share with other people, you certainly wouldn't want anyone to mess up any smart formulas that you worked so hard on! The most common way of preventing people from tampering with your Excel formulas is to protect the worksheet. However, this does not just lock formulas, but rather locks all cells on the sheet and stops users from editing any of the existing cells and entering any new data. Sometimes you may not want to go that far.
The following steps demonstrate how you can only lock a selected formula(s) or all cells with formulas on a given sheet, and leave other cells unlocked.
1. Unlock all cells in the worksheet.
For starters, unlock all of the cells on your worksheet. I realize that it may sound confusing because you have not locked any cells yet. However, by default, the Locked option is turned on for all cells on any Excel worksheet, whether an existing or a new one. This does not mean that you cannot edit those cells, because locking cells has no effect until you protect the worksheet.
So, if you want to lock only cells with formulas, be sure to perform this step and unlock all cells on the worksheet first.
If you want to lock all cells on the sheet (whether those cells contain formulas, values or are blank), then skip the first three steps, and go right to Step 4.
- Select the entire worksheet either by pressing Ctrl + A, or clicking the Select All button
(the gray triangle in the top left corner of the worksheet, to the left of the letter A). - Open the Format Cells dialog by pressing Ctrl + 1. Or, right-click any of the selected cells and choose Format Cells from the context menu.
- In the Format Cells dialog, go to the Protection tab, uncheck the Locked option, and click OK. This will unlock all cells in your worksheet.
2. Select the formulas you want to lock.
Select the cells with the formulas you want to lock.
To select non-adjacent cells or ranges, select the first cell/range, press and hold Ctrl, and select other cells/ranges.
To select all cells with formulas on the sheet, do the following:
- Go to the Home tab > Editing group, click Find & Select button, and choose Go To Special.
- In the Go To Special dialog box, check the Formulas radio button (this will select the check boxes with all formula types), and click OK:
3. Lock cells with formulas.
Now, go to lock the selected cells with formulas. To do this, press Ctrl + 1 to open the Format Cells dialog again, switch to the Protection tab, and check the Locked checkbox.
The Locked option prevents the user from overwriting, deleting or changing the contents of the cells.
4. Protect the worksheet.
To lock formulas in Excel, checking the Locked option is not sufficient because the Locked attribute has no effect unless the worksheet is protected. To protect the sheet, do the following.
- Go to the Review tab > Changes group, and click Protect Sheet.
- The Protect Sheet dialog window will appear, and you type a password in the corresponding field.
This password is needed for unprotecting the worksheet. No one, even yourself, will be able to edit the sheet without entering the password, so be sure to remember it!
Also, you need to select the actions that are allowed in your worksheet. As you see in the screenshot above, two checkboxes are selected by default: Select locked cells and Select unlocked cells. If you click the OK button leaving only these two options selected, the users, including yourself, will be able only to select cells (both locked and unlocked) in your worksheet.
If you want to allow some other actions, e.g. sort, auto-filter, format cells, delete or insert rows and columns, check the corresponding options in the list.
- Once you've selected any additional actions you want to allow, if any, click the OK button.
- The Confirm Password dialog box will appear and ask you to retype the password, to prevent an accidental misprint from locking up your Excel worksheet forever. Retype the password and click OK.
Done! Your Excel formulas are now locked and protected, though visible in the formula bar. If you also want to hide formulas in your Excel sheet, read through the following section.
How to hide formulas in Excel
Hiding a formula in Excel means preventing the formula from being shown in the formula bar when you click a cell with the formula's result. To hide Excel formulas, perform the following steps.
- Select a cell or range of cells containing the formulas you want to hide.
You can select non-adjacent cells or ranges by holding the Ctrl key, or the entire sheet by pressing the Ctrl + A shortcut.
To select all cells with formulas, use the Go To Special > Formulas feature as demonstrated in Selecting cells with formulas.
- Open the Format Cells dialog by doing any of the following:
- Press the Ctrl + 1 shortcut.
- Right-click the selected cell(s) and choose Format Cells from the context menu.
- Go to the Home tab > Cells group, and click Format > Format Cells.
- In the Format Cells dialog box, switch to the Protection tab, and select the Hidden checkbox. It is this option that prevents an Excel formula from being shown in the formula bar.
The Locked attribute, which prevents the contents of the cells from editing, is selected by default, and in most cases you'd want to leave it this way.
- Click the OK button.
- Protect your Excel worksheet by performing these steps.
How to remove protection and unhide formulas in Excel
To get the previously hidden formulas to show in the formula bar again, do one of the following:
- On the Home tab, in the Cells group, click the Format button, and select Unprotect Sheet from the drop-down menu. Then type the password you entered when protecting the spreadsheet, and click OK.
- Or, go to the Review tab > Changes group, and click the Unprotect Sheet button.
This is how you can hide and lock formulas in Excel. In the next tutorial, we will discuss various ways to copy formulas and you will learn how to apply a formula to all cells in a given column in a click. I thank you for reading and hope to see you again soon!
You may also be interested in
80 responses to "How to hide and lock formulas in Excel"
i want an if formula, where if one value is equal to another value, than a third value should come in that cell.
=if(cell1=cell2,.....)
pls corrct the formula
Hi Harji,
Here you go:
=IF(A1=A2, A3, "")
The formula reads as follows: if A1 is equal to A2, copy a value from A3, otherwise return an empty string (blank cell).
You can find plenty more formula examples in the following tutorial: How to use the IF function in Excel.
ok
Thanks & Regards
Is it possible to not only hide and protect certain formulas on an excel spreadsheet, but to also hide a pivot table that is on a separate sheet within the workbook?
Hi, I wonder if there's a way to retrieve forgotten password for a protected sheet. Your guidance is highly appreciated...
Long
Hi Long,
If Excel 2010 and lower, you can easily unprotect a sheet with the following VBA code:
https://www.ablebits.com/office-addins-blog/2016/02/10/protect-unprotect-excel-sheet-password/#unprotect-excel-sheet-without-password
In Excel 2013 and 2016, you would need to use some specialized tool, for example free Password Protection Remover add-in for Excel:
http://www.spreadsheet1.com/password-protection-remover-free-office-excel-add-in.html
Thanks a lot.
Hi Svetlana.
With this password removal tool, any pass-worded Excel workbook can be breached, right?
I have logical statement formulas in cells and a check box that is used to launch the formula and place a value in the cell based on the formula. I would like to protect the worksheet, but when I protect the sheet and click on the check the box, it won't place the value into the cell.
How can I use the check box and logical formulas within a protected worksheet?
i a have a workbook with sheets and its not my own work
i need to show the cells formula cause i need to change something in the formula to my own need the broblem is that the cells are protected what can i do ???
I have 3 value
1 79
2 76
3 256.45*7%
4 blank cell
Dear,
I want you from ur side that how we can automate value in 4th blank sale of highest value when we can change value 1 and 2 manually 4th blank cell can be give highest result.it should be used via IF condition ... i find my result by max function but i want this from if condition
I have used a basic formula =A2+7
However, I am trying to determine how to prevent any results to display prior to the data being entered. Meaning, I would like the date to only show up once someone has entered it into A2.
=IF(A2="","",A2+7)
In other words, if cell A2 is blank then the cell in which the formula is located should remain blank. Otherwise the basic formula is performed.
I,am not satisfied with your solution
i want just to hide formula without protecting the whole sheet
dear sir,
how to hide cell value in formula bar without protect sheet and hid formula bar.
Hi Nitin,
It's not possible to hide a cell value in the formula bar. To hide the formula bar, go to the View tab > Show group, and clear the the Formula Bar check box.
I need to protect and hide my formula cells...
I did everything and it's protect and hide perfectly but the problem is after i enter the data s in the unprotected cells i cant able to edit option , if i click f2 then the error msg comes.
Dear Sir,
I want Hide Formulas and display values in formula bar
I have a perpetual spreadsheet and the date column has a circle formula =IF(D15"",IF(C15="",NOW(),C15),"") and I don't want to have the formula deleted. I have tried the protect sheet but this will prevent the spreadsheet to be perpetual like adding in a new row when the tab is hit.
Hi,
I want your help i dont know how to protect the cells with the formulas to avoid any edition of them, but to allow users to drop or copy the formula to a new cell in case they need to add information.
Thanks
I want to hide the formula only keeping other all cells editable.
Formula's will be hidden but still it will work. Can you pls suggest ?
How you can insert any text in specific cell of excel, keeping range of number “0 to 100”
Hi, I was wondering if I was to use 5 different cell with each having a different formula like =SUM(A2*A3) and =B4-B5 and =C6/C7 etc. to see the bigger picture like creating a conversion counter. Are there a way to lock it so that when I change one of the cell with whatever number that all will follow and change? Because when I try to change a number in a cell, the formula goes away.
Sorry this sounds confusing. It is hard to explain in writing.
Hi, How can i lock a cell after data was is entered automatically?
I want cells to be open for 7 days every month and after a certain date the cell must locked automatically.
Hello!
I would like to enter a formula that uses =if(and( )) to grade my candidates according to their aggregate for instance: =if(d7<=12,"div1") but that condition is only true if the candidate has not scored a 9 in any of the four subjects making the aggregate 12. I tried entering like this =if(d7<=12(and(a7,b7,c7<9,"div1") but showed error that i couldn't correct so i got stuck.
Hello Oromokoma,
if you want to check whether no one scored 9, but it could be 10 or 11, then
=IF(AND(D7<=12, A7<>9, B7<>9, C7<>9),"div1","")
But if the score number should be lower than 9, then
=IF(AND(D7<=12, A7<9, B7<9, C7<9),"div1","")
The formula will return "div1" ONLY when all 4 conditions are met. If you meant something other than that, explain your task in more details and email us at support@ablebits.com with the workbook with data attached. Also, provide a link to this article and your comment.
Hello,
I want to hide cell which contain some formula, but when i am hiding the formula cell its automatically getting applied on the cells which are present in formula.
for example - =IF(ISBLANK(D11),"",IF(((D11/30)*100)>89,"A+",IF(((D11/30)*100)>74,"A",IF(((D11/30)*100)>55,"B",IF(((D11/30)*100)>34,"C","D")))))
if this formula is on column E but this formula contains col D in it, so when i am hiding col E. col D is also getting hide automatically.
Please help me
Regards
Mayank
Hi,
When I protect the sheet, then all the other buttons are not working. they got disabled. So can you please give me solution on this ?
Thanks and regards,
Aniket
Hi,
I have an Excel sheet that I would like to send to others. I want them to be able to make their changes and be able to send the sheet back to me. However, I do not want them to have any access to my formulas at all for security purposes.
Will locking and hiding the formulas ensure that they will not be able to access the formulas?
Thank you very much!
hi i just want to lock the formula coulmn but when i lock and protect the sheet whole sheet is protected no value can be changed anyone plz help me
I want to hide the formula only keeping other all cells editable.
Formula's will be hidden but still it will work. Can you pls suggest ?
When adding a a new row to a protected worksheet I would like the formulas of the protected cells to automatically populate into the new row (without having to unprotect the sheet). Is there a way to do this?
Kindly,
Bailey
i want to protect my formulas but still be able to enter an amount. by protecting the formulas i can't insert anything in those blocks. please help
Hi,
I want to lock the cell which is containing a formula of SUM for another few cells, when i lock the Formula cell and then go to protect sheet and open the sheet from other users pc its protecting the whole sheet rather the highlighted cell i.e =SUM(D3:D11) i want D 2 to be locked but the rest of the cells D3 to D11 to be available for entering values.
Thank you.
how to protect only some cells(like two cells in worksheet)? please mam reply.
Hi!
Please check out the following tutorials for the detailed instructions:
How to unlock individual cells on a protected sheet
Lock all cells on a sheet except for input cells
Thank you very much for this tips.
can you point me in right direction?
Sheet1 is connected to Sheet2
when A1sheet1 changes row1sheet2 changes.
When A2sheet1 changes row2sheet2 changes...so on
I want to lock ColumnAsheet2 so that once I change values in Sheet1, values in ColumnAsheet2 doesn't change.
Thank you.
i am having and excell file with recipes and the formulas are locked and i cant edit. when i edit the formula does not activate.
How can I protect my formula column if i past special in entire sheet
Nothing error should pop up
Nothing error should pop up but past past special should be done
I've made an invoice sheet with formulas in some of the cells, but if there is no value for the formula to use, then I get 0's in the subtotal and total cells. Is there any way to hide those 0's if there is no values for the formulas so the invoice looks better?
Thanks D
What a great, easy-to-read, easy-to-understand tutorial. Thank you for this service!
Excellent tutorial ,enjoyed it fully .
hey, i protected a spreadsheet, however when i open it from google sheets all formulas are shown. any solution to prevent google sheets to show it.
Is it possible to protect cells that have a formula but also allow users to override the formula once ready to do so without having to mess with unlocking and unprotecting the sheet?
Is it possible to have an cell (unprotected and with a formula in it) revert to the formula which was there originally, even after somebody entered a different value? Maybe after a restart? Link a formula to the cell and have the formula be active even if a different value was entered by somebody else?
I.E.: cell with existing formula, somebody types over formula, then decides they did so in error and delete what was typed, is there a way to retain the formula, without having to enter it again, or hitting undo? protecting the cell is not an option.
Thanks in advance!
Do you know how I can automatically refresh my pivot table in google sheets? I have used the code G but it works only in excel. It, however, does not update online
Thank you for your question, Manu.
Please note that in contrast to Excel, pivot tables in Google Sheets are refreshed automatically.
You can learn more about pivot tables in Google Sheets from the article on our blog.
If you have any questions, don't hesitate to ask.
Hi
i want to hide part of equation ( =SUM(V8:V38)+AG30) hide +AG30
Very well elaborated and explained in simple words,
Thanks
In the step 2: "In the Go To Special dialog box, check the Formulas radio button (this will select the check boxes with all formula types), and click OK." It doesn't save this settings as formulas and it is going to the marked as "Comments".
How to fix that? We use Excel 2016 (MS Office 360 Business). Thank you.
Thanks so much!
Vwey well explained, really useful
Hi,
Only a series of Cells containing formulae, I want to lock except the entire Worksheet.Can it be possible in EXCEL....if so what's the method?
Thanks,
Hi Saptarshi,
Thank you for your question.
If I understand your task correctly, please have a look at the following section of the article above:
How to lock formulas in Excel
Hope you’ll find this information helpful.
Thanks. Very useful.
I have an excel sheet, but I cannot seem to select the hide and unhide button in the tab sheet. I have break the password already.
Thank for the great post .
i need your help about "hide formulas in excel" , after read your post it workable for single sheet but it didint work on my second tap in same worksheet . please advise
Hi. Thank you for your clear explanation for locking and unlocking cells with formulas. I am working on a spreadsheet a friend made with some pretty advanced formulas and macros embedded. Unfortunately, he does not trust me to not delete the formulas and locked down the whole workbook. I am not able to make any adjustments to font or color because of the lock. I was wondering...Is it possible to just lock specific cells with formulas but still allow the user to make changes to cell color? I would like to be able to highlight areas that need work or that I have a question about...
Thanks,
Tanya
Hi, Svetlana,
The process you showed above , makes the whole sheet Locked for editing once we protect the Sheet (->Review ->Protect Sheet ) .
Is this possible to make the certain cells Locked while working on the sheets remaining cells can be edited simultaneously ?
Hi Akshay,
Sure, it is possible. Please check out the following tutorial for the detailed steps:
How to lock certain cells in Excel worksheet
Hi,
I want to hide formula on certain cells but want to allow input on other cells, how ca i do that??
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?
Hi , Good work.. very easy to understand ..thank you
Mam i want to insert and do shorting, in the particular range of locked cells
Hello,
Are there any special rules for array formulas when it comes to locking them? I have enabled protection on a sheet that contains a number of these, and while they're all locked according to the cell properties, I can still edit them. Non-array formulas lock properly.
I have locked the formulas and protected the worksheet but now I am unable to use the Sort and Filter functions in my worksheet, even though I have checked those options in the Protect Sheet dialogue box. Please let me know if I am missing something here. Thank you.
i want to locked and hide my full workbook(All Sheet) formula how can i do it.
I want rank my pupil according to the position in class. Eg 1st,2nd etc
Want to protect and lock formulas from being edited but still be able to work on without editing them
How to formulate this in excel
These all values are in different cells like A1, B1, C1 then A2, B2, C2 then A3, B3, C3 and so on
|A|A| SET1
|A|B| SET2
|A|C| SET3
|A|C| SET3
|A|B| SET2
|D|B| SET4
|B|C| SET5
etc.....
I want different sets of the data in column 3 according to data in column 1&2.
Please help
I am trying to protect formulas, but also allow the insertion of lines that would duplicate the formula. Is this even possible? ??
Hi Do you know how to hide half of the formula?
For example 100+50+250+50*0.95 and I don't want to show 0.95 in formula but I want to include it. Is it possible?
Is there a way to keep the grouping working while sheet is protected?
Just wow
Hi
I have a casual attendance sheet and i would need to lock the cell with the formula.
Eg.month April 30days and so my format 31 days.so the date of 31 cells needs to lock automatically. please let me know if there is a formula lock.
regards.
Hello Dhammika!
I think the tool Data Validation will be useful to you. Read more in this article.
if i do the same process as you mentioned above for hiding and locking the formula with password, it doesn't work when i copy the file in another version of Excel. for example when i create a file in excell 2016 and copy the file in excel 2007 it cracked all password itself. What is the possible solution for this problem ? Please suggest me.
if i do the same process as you mentioned above for hiding and locking the formula with password, and copy in another version of excel it cracked password itself. what can i do for this problem?