In this article I want to share with you in three ways: protect your excel formula.
So, you have been preparing an Excel spreadsheet solution for a long time, and I’m sure you don’t want anyone to spoil the formulas that you so lovingly created. Thus, there are several ways to protect some of the most vulnerable parts of your Excel spreadsheet.
My top 3 ways to protect your Excel formulas!
- Hide formulas.
- Block the cells containing the formulas.
- Hide the formula bar with some simple VBA.
1. Hide Formulas.
This method temporarily hide your formulas but you can use them again if you need. It is simple and straightforward. Like this.
Select all the cells that contain the formulas you want to hide.
- Main tab - Group of cells - Format - Formatting cells
- Click on the tab "Protection"
- Check the “Hidden” option and click “OK”.
This in itself does not hide your formulas; you need to protect your worksheet to ensure that these settings are made.
- Select the "Overview" tab
- Change group
- Select Protective Sheet
- Enter the password and confirm the password when prompted.
- That's all you need to do.
Try selecting the cell containing the formula. The formula will not be visible in the formula bar. If you want to see the formulas again, simply remove the protection from the worksheet.
2. Block the cells containing the formulas.
The second method is to simply block cells that contain formulas so that they cannot be selected or edited by users. By default, all cells in the workbook are locked, so you will need to unlock them all to begin with.
- Press CTRL + A to select all the cells in the sheet.
- Main tab - Group of cells - Format - Formatting cells
- Untick Locked to unlock all cells on the sheet
- Click OK
Now all we need is to find all the cells containing formulas ...
- Press F5 to open the GoTo dialog box.
- Select "Special" - "Formulas" - click "OK"
- All cells containing formulas will be highlighted.
Then we need to block these selected cells ...
- Main tab - Group of cells - Format - Formatting cells
- Click on the tab "Protection"
- Check the Blocked option and click OK.
This in itself does not block your formulas, you need to protect your worksheet to ensure that these parameters work.
- Select the "Overview" tab
- Change group
- Select Protective Sheet
- Enter the password and confirm the password when prompted.
- That's all you need to do.
3. Hide the formula bar with some simple VBA
My third method of hiding your formulas is to actually hide formula bar on excel sheet. This is easily achieved with a very small amount of VBA coding or Excel Macro.
This macro uses Application object and we hope to use the DisplayFormulaBar property.
To use this small piece of code, you need to paste it into a module in an Excel workbook.
- Open Visual Basic — by pressing F11 or the developer tab — Visual Basic — click modules and add a new module.
Here is the VBA code if you want to copy it. Simply insert it into the module that you created in accordance with the instructions above.
Sub HideFormulaBar ()
Application.DisplayFormulaBar = False
End sub
Just as we hid the formula bar, we can easily write VBA to display the formula bar again.
Sub ShowFormulaBar ()
Application.DisplayFormulaBar = True
End sub
In this case, we set the Application.DisplayFormulaBar to TRUE to display the formula bar.
I use all these methods in my Excel spreadsheets, depending on what I think is best suited for this project I'm working on, for example, it may not always be convenient to hide the formula bar, even if some calculations are all still needed to be seen, so in this case I simply hide sensitive formulas. if this is a purely visual solution to the Dashboard, then hiding the formula bar not only makes room in the Excel display area, but always makes it very neat and tidy for the Dashboard presentation.
