How To Hide Formulas in Excel (and Only Display the Value)Step by Step Instructions with Screenshots
If you choose to share an Excel file with someone, they will often be able to see as well as edit anything in the file.
If you would prefer not to have others be able to change your file, you can protect the whole worksheet or workbook or just specific cells with data you don’t want to be altered.
However, if you choose to protect the worksheet, the person using the worksheet will still be able to see the formula you used to make calculations by simply clicking on the cell.
But there is something you can do to hide these formulas so that users will not be able to see them.
Hiding Formulas in Excel
If you want to hide the formulas in your worksheet and protect the whole worksheet, here is how to do it.
- Select the cells in which you want the formulas hidden.
- Now, find the “Home” tab, and look for the “Number” group. Then, click on the dialog box launcher.
- The “Format Cells” dialog box should now appear. Choose the “Protection’ tab. Then, check the hidden option and click” OK.”
- Find the “Review” tab, and then in the “Protection” group, click the option Protect Sheet.
- When the Protection dialog box appears, type in the password that will be required to unlock the worksheet and click “OK.” This is optional; you are not required to have a password.
These steps will protect your whole worksheet.
Users will see the value in the formula bar if they click on a cell with a value in it.
However, if a user clicks on a cell with a formula, the formula will not be displayed in the formula bar.
Users will also not be able to double-click on a cell and enter edit mode.
Hiding All the Formulas in a Worksheet While Still Being Able to Edit Other Cells
If you have a formula in a cell in Excel, there are a couple of ways for a user to view it.
They can double-click on the cell, which will cause them to enter edit mode or select the cell, and the formula will appear in the formula bar.
But, once you hide the formulas, users will not only be unable to edit the cell, but they also will be unable to view the formula in the formula bar.
Unfortunately, with the previous method, users will not be able to edit any other cells either.
However, there is a way to hide formulas while allowing other cells to be edited. Here is how.
Steps for Hiding Formulas While Retaining the Ability To Edit Other Cells
- Select the worksheet that contains the formula you want to hide. To do this, press and hold the control key and then A.
- Next, right-click and press control and then one or select “Format Cells.”
- After you have selected this option, you might see a dialog box like the one below. If you do, select “Protection.”
- Once you select the “Protection” tab, be sure to remove the check from the “Locked” option. This will unlock every cell in your worksheet. You should find that when the cells become unlocked, Excel will inform you of the error “Unprotected Formula.”
- Now, select the formula cells in your worksheet and lock them. Then, choose the “Protection” tab in “Format Cells” and put a check in the options “Locked” and “Hidden.”
- If you have a lot of formulas and want to be sure you are protecting all of them, press the key F5, choose “Go to Special,” and select formulas.
- Once you see the dialog box, Choose “Formulas” and click “OK.” This will select any formula cells in your worksheet.
- If you want to protect the worksheet now that you have the formula cells selected, locked, and hidden, locate the “Review” tab and “Protect Sheet.”
- Select “Protect Sheet,” and a dialog box might appear. Choose “Select Locked Cells” and “Select Unlocked Cells.” Type the password you want. Make sure you will remember the password since you will not be able to edit the cells if you forget the password. Click “OK’ and confirm the password.
- Click “OK” again, and the formulas are locked and password protected.
These two methods above will hide the formulas in your worksheet.
But if you know how to use VBA code, you have other options.
This can add flexibility to how you can edit your worksheet, but this should only be considered if you are comfortable with editing VBA code.