How to Hide Formulas in Excel (And Protect Your Worksheets)

Padlock on top of a keyboard that represents how to hide formulas in Excel

Let’s say you’ve been building a worksheet to share with your coworkers. When you share it, they’ll be able to see, edit, and change everything that’s in the workbook, including your formulas. It can sometimes be a little too easy to make those changes accidentally. That’s why it’s a good idea to lock and even hide formulas in Excel – that way, you can still share your workbook, but you’ll know that your work is protected.

How to protect your worksheet

First, if you don’t want the people with whom you’re sharing the worksheet to change anything, you can protect the entire worksheet by clicking the Review ribbon tab and selecting Protect Sheet. You can select which actions users are allowed to take while the sheet is protected by checking the desired options.

In practice, I typically accept the default options of Select locked cells and Select unlocked cell. At this point, Excel will basically prevent users from changing anything on the protected worksheet.

There are two enhancements we can make. The first one we’ll tackle is actually hiding the formulas from view. Then we’ll allow users to change some cells (like data input cells) and not other cells (formulas). Let’s start by hiding formulas which, luckily, only takes a few extra steps. 

How to hide all formulas in your Excel worksheet

Here are the steps to prevent users from (a) making any changes to the worksheet and (b) viewing formulas when they select a formula cell. That is, when a user clicks a formula cell, they will see an empty formula bar instead of the formula. The formula will still work.

The sheet must be unprotected to perform these steps.

1. Locate the Protect Sheet or Unprotect Sheet button on the Review ribbon tab. If the sheet is already unprotected, you’re all set – if it is protected, be sure to select Unprotect Sheet.

2. Choose the cells whose formulas you want to hide and select Format Cells or Ctrl+1.

3. Choose the Protection tab in the Format Cells dialog box. 

4. The Locked checkbox is checked by default, so just check the Hidden box as well. The Hidden option is what hides the formula from the user. The Locked option prevents a user from changing the cell value.

Now … HERE’S THE IMPORTANT PART … the Hidden/Locked attributes aren’t enforced until you turn on worksheet protection. That is, until you use the Protect Sheet command, the Locked and Hidden settings are ignored. This explains why you’ve been able to change cell values even though the cells are Locked by default. 🤔

So … the final step is to protect the sheet by clicking Protect Sheet on the Review ribbon tab. At this point, the user won’t be able to view the formulas (or make any changes to cell values).

So, the final little tweak is to enable the user to change the values in some cells, but not others (for example, your formulas).

What if you want to hide formulas in Excel, but still allow other changes?

You might share workbooks with people and want them to be able to make changes to certain cells – this is pretty common with worksheets involving data entry, statistics, and more. But, if you protect the whole worksheet as shown above, others will essentially be prevented from making changes to any cells.

The way to enable them to make changes to some cells and not others is by unchecking the Locked attribute before protecting the worksheet.

For example, let’s say we want to enable the user to make changes to all but formula cells. To accomplish this, we uncheck the Locked property for all cells except the ones with formulas. Here are the detailed steps for how to do it!

Start by disabling the locked option

1. Select all cells – the fastest way to do this is by using the shortcut Ctrl + A.

2. Open the Format Cells dialog (Ctrl+1).

3. Select the Protection option, and make sure that the Locked box is unchecked.

4. Click OK.

Now that the Locked option has been disabled, users will be able to edit those cells when the sheet is protected.

Now, let’s lock and hide formulas.

Lock all cells with formulas & toggle the Hidden option

1. Start by selecting either one single cell (doesn’t matter which one) or all cells (Ctrl+A).

2. On the Home tab, click the Find & Select command and select Go To Special. Check the Formulas box to select only the cells with formulas.

3. With just the formula cells selected, open the Format Cells dialog (Ctrl + 1). 

4. Select the Protection tab, and be sure that the options for Locked and Hidden are checked.

5. Click OK.

6. Protect your worksheet by navigating to the Review ribbon tab and selecting the Protect Sheet option.

Now that the sheet is protected, the locked/hidden attributes are enforced. Since we applied them to the formula cells, only the cells with formulas will be locked. And if a user selects one of the formula cells, the formula will be hidden from the formula bar. The user will be able to edit any of the other cells (since we unchecked the Locked checkbox).

Note that if you add a formula in the future, you’ll need to unprotect the sheet, hide/lock the new formula cell, and then protect the sheet.

Mission accomplished 🙂


Do you have any other tips for keeping your workbooks protected while sharing them? Let us know in the comments!

Avatar photo

Excel University

We love sharing the things we've learned about Excel, and we built Excel University to help us do that. Our motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

3 Comments

  1. Ken Burke on October 19, 2022 at 8:25 am

    I was forwarded this article and i thought it was my answer and it might be and i am just not doing something correct.
    1. I can lock cells, hide cells, and not lock and not hide sells all one sheet.
    2. Protect & Lock the sheet. Got it But
    3. I can’t copy an unlocked row with unlocked cells and insert paste the unlocked row below and or between two Locked Rows?

    • Suzanne G on October 19, 2022 at 9:03 am

      Hi Ken, if you look at the top of the article there is a screen shot of the Protect Sheet box with several actions you can allow users to have access to (or not). I think that might be where you want to check to allow the insertion of rows. I could be wrong, but it’s worth a try?

  2. Jamies on October 19, 2022 at 10:14 am

    One technique I have used is to put checking formulas in a separate worksheet,
    and then setting that worksheet to be VeryHidden from the basic user.

    You need to have the worksheet able to run VBA macros – but once the sheet is hidden,
    Protect the VBA, and SaveA with a non-macro type (.xlsx) for the data entry usage.

    Set cells in that worksheet to include error messages to indicate which (group of) checking generated “it’s wrong” results
    Define names (formula tab) with formulas pointing to the appropriate checking cell(s)
    Set protected cells in the user data entry worksheets showing those defined name values
    Optionally set conditional formatting onthe error existance/description cell rather than all of the cells that could be in error – avoids problems with Excel spending more time checking the CF hive than doing the calculations needed, and limiting the actions you can get to work – inserting lots of cells/rows/columns, Undo etc.
    Then make the worksheet with the checking formulas very hidden.
    That way the data entering person does not see the checking code, that can verify appropriate ranges of cells conform to the data validation, and any appropriate formatting.
    As in:
    Data validation has not been bypassed using paste,
    Text fields contain appropriate text (length)
    Numeric fields are within appropriate ranges.
    Date fields contain appropriate numbers, or text strings. as in Excel considers them to be convertible to dates.
    Values are within limits – and/or found using lookup on validation sets,
    CELL() and INFO() return appropriate values.

    BUT – Do remember, SaveAs (and Close invoking the “do you want to save” message)
    can be used to set values that cannot be checked before the SaveAs is invoked.

Leave a Comment