Do you want to calculate something in Excel? You will have to use a formula. If you click on a cell that contains one, you should see it in the formula bar.
However, there may be times when you don’t want anyone else to see your Excel formulas. They may be too complex or contain sensitive information that you don’t want to share.
In Excel, it is possible to hide your formulas, as well as protect the cells that contain them so that no one else can edit them. Next we explain how to hide a formula in excel.
How to protect a sheet in Excel
Hiding formulas in Excel only works when sheet protection is turned on. Protecting a sheet prevents people from editing any of the cells you specify, ensuring they don’t break your spreadsheet.
It is helpful to know how to do this before proceeding.
To protect a sheet in Excel:
- On the ribbon bar, tap Check.
- Click on protect sheet.
- Enter a password if necessary. If you don’t enter one, anyone else can check out your sheet in a few clicks.
- Click on To accept to continue.
- Sheet protection is now activated. Anyone who tries to edit a cell will get a popup message.
How to unprotect a sheet in Excel
With sheet protection enabled, you will not be able to make any changes. If you want to edit your sheet, you’ll need to check it out first.
To unprotect a sheet in Excel:
- press the option “Check” in the ribbon bar.
- Then press unprotect sheet.
- If you entered a password when you protected the sheet, you will need to enter it now.
- Sheet protection is now off, and you can make any edits you want.
How to hide a formula in Excel by locking all cells
You can hide all your formulas in Excel by setting all cells to Hidden and Locked and then protecting your sheet. This will hide all the formulas, but it also means that none of the cells on your sheet will be editable.
To hide a formula in Excel by locking all cells:
- Select the empty space at the top left of your sheet or press Ctrl+A on Windows (or Cmd+A on Mac) to select all cells on your sheet.
- Beam right mouse click anywhere on your sheet and click Cell formator press Ctrl+1 on Windows or Cmd+1 on Mac
- Click on the tab To protect.
- Make sure that both Blocked up What hidden they are marked.
- Click on To accept.
- Follow the instructions in How to protect a sheet in Excel above.
Your blade is now locked. Nobody can edit any of the cells, and clicking a cell that contains a formula doesn’t display the formula anywhere on the sheet.
How to hide a formula in Excel by locking the formulas in the cells
The above method is a quick and easy way to hide all formulas, but it also locks all other cells on the sheet. If you want the other cells to be editable, it is possible to lock and hide only the cells that contain formulas.
To hide a formula in Excel by locking the formulas in the cells:
- Press Ctrl+A on Windows or Cmd+A on Mac or click the empty space at the top left of the sheet to select all cells.
- Press Ctrl+1 on Windows or Cmd+1 on Mac, or right-click anywhere on your sheet and click Cell format.
- Select the tab To protect.
- Make sure the options Blocked up Y hidden are not marked.
- You’ll see green triangles appear in any cell that contains formulas. This is a warning that these cells contain unprotected formulas.
- Repeat step 1 to select all cells.
- Select Start on the taskbar.
- Click on Search and Select.
- Select Formulas.
- Now all cells containing a formula will be selected.
- click with it the right button of the mouse in one of the cells and click Cell format or use the shortcuts Ctrl+1 on Windows or Cmd+1 on Mac
- Click on To protect.
- tick as much Blocked up What hidden and then click To accept.
- Follow the instructions above on How to protect a sheet in Excel.
Now that you’ve locked your sheet, all cells are editable except the ones that contain formulas. These cells are locked and have their formulas hidden.
If you want to hide the formulas but also want those cells with formulas to be editable, follow the steps above, but don’t check Blocked up.
Knowing how to hide a formula in Excel allows you to share your spreadsheet with the assurance that your formulas are not visible, and that no one can modify or delete them.
Another effective way to prevent people from deleting your formulas is to delete them completely. If the numbers have already been calculated, you can substitute a formula for the result in Excel, since the formula itself is no longer needed.
Data visualization is also useful when you share your spreadsheets with others. Learn to make a pie chart or a histogram in excel allows you to present your data more clearly.