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:
On the ribbon bar, press Review. Click Protect Sheet. Enter a password if needed. If you don’t enter one, anyone else will be able to unprotect your sheet in a few clicks.Click OK to proceed. Sheet protection is now on. Anyone trying to edit a cell will receive a pop-up message.
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:
Press Review on the ribbon bar. Next, press Unprotect Sheet. If you entered a password when you protected the sheet, you’ll need to enter it now.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:
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. Right-click anywhere in your sheet and click on Format Cells, or press Ctrl+1 on Windows or Cmd+1 on Mac. Click on the Protection tab. Ensure that both Locked and Hidden are checked. Click OK.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:
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. Press Ctrl+1 on Windows or Cmd+1 on Mac, or right-click anywhere in your sheet and click Format Cells. Select the Protection tab.
Ensure both Locked and Hidden are not checked.You will see green triangles appear on any cells containing formulas. This is a warning that these cells contain unprotected formulas. Repeat step 1 to select all cells.Select Home on the ribbon bar. Click Find & Select. Select Formulas. Every cell containing a formula will now be selected.Right-click on one of the cells and click Format Cells or use the shortcuts Ctrl+1 on Windows or Cmd+1 on Mac. Click on Protection. Check both Locked and Hidden then click OK.
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. Comment
Δ
