How-To

How to Hide a Formula in Excel

Spreadsheet featured

If you’re sharing a spreadsheet, you might want to keep your formula safe from prying eyes. Here’s how to hide a formula in Excel.

Want to work something out in Excel? You’ll need to use a formula. If you click on a cell containing one, you should see it in the formula bar.

There may be times, however, when you don’t want anyone else to see your Excel formulas. They might be too complex, or contain confidential information that you don’t want to share.

In Excel, it’s possible to hide your formulas, as well as protect the cells that contain them so that no one else can edit them. Here’s how to hide a formula in Excel.

How to Protect a Sheet in Excel

Hiding formulas in Excel only works when you turn sheet protection on. Protecting a sheet stops people from being able to edit any of the cells that you specify, ensuring that they don’t break your spreadsheet.

It’s useful to know how to do this before we go any further.

To protect a sheet in Excel:

  1. On the ribbon bar, press Review.
  2. Click Protect Sheet.
    excel protect sheet
  3. Enter a password if needed. If you don’t enter one, anyone else will be able to unprotect your sheet in a few clicks.
  4. Click OK to proceed.
    excel protection password
  5. Sheet protection is now on. Anyone trying to edit a cell will receive a pop-up message.
    excel protected cell warning

How to Unprotect a Sheet in Excel

With sheet protection turned on, you are unable to make any changes. If you want to edit your sheet, you’ll need to unprotect it first.

To unprotect a sheet in Excel:

  1. Press Review on the ribbon bar.
  2. Next, press Unprotect Sheet.
    excel unprotect sheet
  3. If you entered a password when you protected the sheet, you’ll need to enter it now.
  4. Sheet protection is now off, and you can make any edits you wish.

How to Hide a Formula in Excel by Locking All Cells

You can hide all your formulas in Excel by setting all cells as Hidden and Locked and then protecting your sheet. This will hide all formulas, but it also means that none of the cells in your sheet will be editable.

To hide a formula in Excel by locking all cells:

  1. Select the empty space at the very top-left of your sheet or press Ctrl+A on Windows (or Cmd+A on Mac) to select all cells in your sheet.
    select all cells excel
  2. Right-click anywhere in your sheet and click on Format Cells, or press Ctrl+1 on Windows or Cmd+1 on Mac.
    format cells excel
  3. Click on the Protection tab.
    cell protection in excel
  4. Ensure that both Locked and Hidden are checked.
    locked and hidden cells in excel
  5. Click OK.
  6. Follow the instructions for How to Protect a Sheet in Excel above.

Your sheet is now locked. No one can edit any of the cells, and clicking on a cell containing a formula does not show the formula anywhere on the sheet.

How to Hide a Formula in Excel by Locking Formula Cells

The method above is a quick and easy way to hide all formulas, but it also locks every other cell in your sheet.  If you want the other cells to be editable, it’s possible to lock and hide just those cells that contain formulas.

To hide a formula in Excel by locking formula cells:

  1. Press Ctrl+A on Windows or Cmd+A on Mac or click in the empty space at the very top-left of your sheet to select all cells.
    select all cells excel
  2. Press Ctrl+1 on Windows or Cmd+1 on Mac, or right-click anywhere in your sheet and click Format Cells.
    format cells excel
  3. Select the Protection tab.
    cell protection in excel
  4. Ensure both Locked and Hidden are not checked.
  5. You will see green triangles appear on any cells containing formulas. This is a warning that these cells contain unprotected formulas.
    excel unprotected formulas
  6. Repeat step 1 to select all cells.
  7. Select Home on the ribbon bar.
    excel home menu
  8. Click Find & Select.
    find and select excel
  9. Select Formulas.
    find formulas in excel
  10. Every cell containing a formula will now be selected.
  11. Right-click on one of the cells and click Format Cells or use the shortcuts Ctrl+1 on Windows or Cmd+1 on Mac.
    format cells excel
  12. Click on Protection.
    cell protection in excel
  13. Check both Locked and Hidden then click OK.
    locked and hidden cells in excel
  14. Follow the instructions above on How to Protect a Sheet in Excel.

Now that you have locked your sheet, all cells are editable, except those containing formulas. These cells are both locked and have their formulas hidden.

If you want to hide formulas but also want those formula cells to be editable, follow the steps above, but don’t check Locked.

Creating Excel Spreadsheets For Sharing

Knowing how to hide a formula in Excel allows you to share your spreadsheet safe in the knowledge that your formulas aren’t visible, and that no one can amend or delete them.

Another effective way to stop people from deleting your formulas is to remove them completely. If the numbers have already been crunched, you can replace a formula with the result in Excel since the formula itself is no longer needed.

Data visualization is also useful when sharing your spreadsheets with others. Learning how to make a pie chart or histogram in Excel allows you to present your data more clearly.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top