Excel Tips – 3 Ways To Protect Your Excel Formulas

In this article I want to share with you 3 ways to protect your Excel formulas.

So, you have spent a long time preparing your Excel spreadsheet solution, and I bet you don’t want anyone to mess up those formulas you have so lovingly created. So, there are a few ways to protect some of those most vulnerable parts of your Excel spreadsheet.

My top 3 ways to protect your Excel Formulas!!

1. Hide the Formulas.
2. Lock The Cells That Contain Formulas.
3. Hide The Formula Bar With Some Simple VBA.

1.Hide The Formulas.

This method will temporarily hide your formulas, but you will be able to use them again if you need to. It’s simple and straight forward. Here we go.

Select all of the cells that contain formulas that you want to hide.

• Home Tab – Cells Group – Format – Format Cells
• Navigate to the Protection Tab
• Check the Hidden option and hit OK

This doesn’t in itself hide your formulas, you need to then protect your worksheet to ensure these settings work.

• Select Review Tab
• Changes Group
• Select Protect Sheet
• Enter a password and confirm password when prompted
• That’s all you need to do.

Try selecting the a cell that contains a formula. The formula will not be visible in the formula bar. If you want to see the formulas again simply unprotect your worksheet.

2. Lock The Cells That Contain Formulas.

The second method is to just lock the cells that contain formulas so they cannot be selected or edited by users. By default all cells in a work book are locked, so you will need to unlock them all to start with.

• Hit CTRL+A to select all of the cells on the worksheet
• Home Tab – Cells Group -Format – Format Cells
• Untick Locked, to unlock all of the cells on the worksheet
• Hit OK

Now all we need to do find all of the cells that contain formulas…

• Hit F5 to bring up the GoTo Dialog Box
• Select Special – Formulas – Hit OK
• All of the cells that contain formulas will be highlighted

Then we need to lock those highlighted cells…

• Home Tab – Cells Group -Format – Format Cells
• Navigate to the Protection Tab
• Check the Locked option and hit OK

This doesn’t in itself lock your formulas, you need to then protect your worksheet to ensure these settings work.

• Select Review Tab
• Changes Group
• Select Protect Sheet
• Enter a password and confrm 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 the formula bar on the Excel worksheet. This is easily achieved by a very small piece of VBA coding or an Excel Macro.

This macro uses the Application Object and we are looking to use the DisplayFormulaBar property of it.

To use this small piece of coding, you need to insert it into a module in your Excel workbook.

• Open Visual Basic – by hitting F11 or Developer Tab – Visual Basic – Click Modules, and Add New Module.

Here is the VBA code if you want to copy it. Just paste it into a module you have created as per the instructions above.

Sub HideFormulaBar()

Application.DisplayFormulaBar = False

End Sub

Just as we have hidden the formula bar we can easily write some VBA to show the formula bar again

Sub ShowFormulaBar()

Application.DisplayFormulaBar = True

End Sub

In this instance we set the Application.DisplayFormulaBar to TRUE to display the formula bar.

I use all of these methods in my Excel spreadsheets depending on which I feel is most appropriate for that project I am working on for example it maybe not always appropriate to hide the formula bar altogether if some calculations are still needed to be seen, so I would just hide the sensitive formulas in that case. if it is a purely visual Dashboard solution then hiding the formula bar not only gives room on the Excel display area, but it always makes for a very neat and tidy visual for the Dashboard presentation.