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.

Unlock all cells in the 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.
    Click 'Go to Special' option.
  • 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:
    Select all cells with formulas on the sheet.

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.
Lock the selected cells with formulas.

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.
    Protecting the worksheet in Excel
  • 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!
    Protecting formulas in Excel

    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.

Tip. If you need to edit or update your formulas once in a while and you don't want to waste your time on protecting / unprotecting the worksheet, you can move your formulas to a separate worksheet (or even workbook), hide that sheet, and then, in your main sheet, simply refer to the appropriate cells with formulas on that hidden sheet.

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.

  1. 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.

  2. 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.
  3. 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.
    Hiding formulas in Excel

  4. Click the OK button.
  5. Protect your Excel worksheet by performing these steps.

Note. Please remember that locking cells and hiding formulas has no effect until you protect the worksheet (a short notice right underneath the Locked and Hidden options on the Format Cells dialog points to the next steps). To make sure of this, select any cell with a formula, and look at the formula bar, the formula will still be there. To really hide formulas in Excel, be sure to protect the worksheet.

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.

Unprotecting the worksheet in Excel

Note. If you've hidden the formulas before protecting the workbook, you may want to uncheck the Hidden checkbox after unprotecting the worksheet. This won't have any immediate effect because the formulas start showing in the formula bar as soon as you've removed the worksheet protection. However, if you ever want to protect the same sheet in the future, but let the users see the formulas, make sure the Hidden attribute is not selected for those cells (select the cells with formulas, press Ctrl + 1 to open the Format Cells dialog, go to the Protection tab and remove a tick from the Hidden box).

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!

Table of contents

92 comments

  1. 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?

  2. I am trying to protect formulas, but also allow the insertion of lines that would duplicate the formula. Is this even possible? ??

  3. 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

  4. Want to protect and lock formulas from being edited but still be able to work on without editing them

  5. I want rank my pupil according to the position in class. Eg 1st,2nd etc

  6. i want to locked and hide my full workbook(All Sheet) formula how can i do it.

  7. 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.

  8. 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.

  9. Mam i want to insert and do shorting, in the particular range of locked cells

  10. Hi , Good work.. very easy to understand ..thank you

  11. 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?

  12. Hi,

    I want to hide formula on certain cells but want to allow input on other cells, how ca i do that??

  13. 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 ?

  14. 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

  15. 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

  16. 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.

  17. Thanks. Very useful.

  18. 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,

  19. Thanks so much!
    Vwey well explained, really useful

  20. 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.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)