For any excel user to become proficient and advanced, they should have detailed and sufficient knowledge when using Excel formulas. Excel formulas can make your computations and daily tasks easier. Sometimes a user can share their worksheet with many users, hence the need to hide formulas instead of displaying them. Hiding formulas in Excel is a regular occurrence. We do this when we do not want the formula to be displayed in our worksheet’s formula bar when we click on a cell that has a formula in it. It is easy enough to do this. A user can decide to format the cells containing the formula to protect the worksheet or even check a hidden checkbox.
In the article below, we are going to show you ways of hiding your Excel formulas.
Method 1: Replacing the formula with computed values to hide it
If you want to get rid of all your formulas, this is a more straightforward method. It can only be used when you do not need to use the formulas again. Here is what you have to do;
- In your open worksheet, select the range of cells where your excel formula is applied.
- Right-click on the selected cells and select the Paste option from the displayed menu.
- Click on the paste option labeled 123. After this, click OK. The corresponding values will replace all the formulas.
Method 2: Hiding Excel Formulas by Protecting the Worksheet.
This method is different from the first one as it helps hide formulas temporarily, especially when you need to reuse them later on. It helps suppress the formula and protect the worksheet by locking it to prevent an end-user from editing it. The below steps can be adopted to achieve this;
- In an open worksheet, select the range of cells that contain the Excel formulas you want hidden. To select the entire sheet, press your keyboard’s shortcut keys Ctrl + A.
- Right-click on any of the selected cells and select Format Cells. You can also decide to press ctrl + 1 to initiate this command.
- The above option will open a dialogue box with six tabs in it. Click the last tab, “Protection.’
- Uncheck the Locked option. All the cells in your current worksheet will be unlocked. Excel will display an error message to warn you about Unprotected Formula.
- Go to your worksheet and select all the formula cells and lock them.
- Go to Format Cells in the Protection tab; check the Locked and Hidden boxes. All your formulas will be locked. After doing this, the formula will still be ineffective until the sheet is protected.
- To protect the whole Excel worksheet, go to the Review tab. It is on the main ribbon menu.
- Under the Changers group, select the option ‘Protect Sheet.’
- A dialog box will be displayed by Excel, type in your password to protect your sheet.
- Click OK. A confirm password dialogue box will be displayed. Re-type in your password and click OK. After you have done so, the Excel formulas will not be shown, and neither can they be edited.
Conclusion Excel offers different features that can help its users hide formulas from the end-user. The above methods, when used, will help manage your worksheets. The most significant benefit of hiding formulas and protecting your worksheet is ensuring data integrity by preventing any end-user from editing the data. Unless someone has the password, your excel worksheet will remain safe