Four ways to protect your Excel formulas
Formulas are one of the most important components of an Excel sheet and as such, deserve-warrant-protection. You don’t want users accidentally changing formulas and impacting the purpose of your work. Fortunately, using Excel’s Protection feature, you can keep users from inflicting accidental damage. Beyond protecting, sometimes you’ll need to find formulas altogether. Usually, you’ll do so to protect proprietary and conditional information. The good news is that it’s easy to protect and hide Excel formulas.
Let’s begin with a warning
It’s important to clarify the difference between protection and security. Excel protection isn’t a security feature. Protection is meant to prevent well-meaning users from accidentally damaging your work. It won’t prevent sensitive and confidential data from falling into the wrong hands. For instance, did you know that anyone can copy data from a protected Excel sheet into another workbook or even Google Sheets to access protected data? Excel protection is lost once data is copied somewhere else. It really is that easy, so don’t make the mistake of thinking a protected sheet is secure.
When creating a sheet, you need the ability to enter and modify data and formulas, so everything is visible and accessible by default. You can see formulas in the Formula bar or by pressing F2 for in-cell editing. To see all formulas at once, as shown in Figure A, you can use the Show Formula option. Simply click the Formulas tab and then click Show Formulas in the Formula Auditing group. Click it again to display the results. Or press Ctrl+’ (that’s the backtick character below the tilde character).
Protecting a sheet disables many options. However, it doesn’t disable Show Formulas or the Formula bar. Next, I’ll show you how to hide the Formula bar.
Hide the formula bar
Without protecting the shet, there’s no way to truly hid the formulas other than to hide the Formula bar. Usually, you won’t want to disable the Formula bar unless viewers are working with a read-only file. To hide the Formula bar, just click the View tab and then uncheck Formula Bar in the Show group. As you can see in Figure B, the Formula bar is hidden. You can still access the cells, but you can’t see formulas.
Bear in mind, however, that hiding the Formula bar won’t protect your formulas from being modified or viewed:
- Show Formulas will still toggle the formulas and their results.
- A user can enable the Formula bar just as easily as you disabled it.
- Pressing F2 to engage in-cell editing also displays the formula and allows users to make changes.
It’s worth noting that you can disable the Formula bar via Excel’s Advanced options (on the File menu). In the Display section, uncheck Show Formula Bar. Just remember that a user can change this option as easy as you did. It’s great to know that you can hide the Formula bar but doing so won’t protect formulas. So let’s look at a couple of cell formats that will protect and hide formulas.
Protect and hide formulas
At the cell level, it’s easy to both protect and hide formulas using simple formats-albeit not a format in the traditional sense. To illustrate, let’s run through a quick example
- Select the cells with formulas you want to hide and protect-cells E4: E7 in our example sheet.
- Right-click the selection and choose Format Cells from the submenu.
- In the results dialog, click the Protection tab.
- Check the Hidden option (Figure C) and then click OK. Notice that the Locked option is checked by default.
At this point, both formats are set but not active. You must enable sheet protection to activate these formats. To enable protection, click the Review tab and then click Protect Sheet in the Protect group. In the resulting dialog, enter a password, as shown in Figure D, and click OK. Confirm the password and click OK again.
As Figure E shows, you can’t see the formula in the Formula bar. By locking and hiding the formulas, you keep users from changing and viewing the formula. This is enough to keep well-meaning users from unintentionally damaging your work.
If you open a protected Excel file in the browser edition, you can see hidden formulas, but you can’t modify them. For most sheets, hiding formulas is overkill, but it’s easily implemented. Note that locking is the default, (step 4 above), so you must unlock cells you want users to access before protecting the sheet, rather than the other way around. You’ll need to unlock the input cells.
Apply read-only property
You can bypass protection altogether if you don’t care to hide formulas. To do so, password protect the entire file as follows:
- From the File menu, choose Save As.
- Click the More Options link below the name and File Type controls.
- Click the Tools dropdown (next to the Save button) and choose General Options.
- In the resulting dialog, enter a password in the Password To Modify control (Figure F) and click OK.
- Re-enter the password to confirm and click OK.
Anyone can open and view the data, but only those who know the password can modify the contents-you protect the formulas and everything else. But remember, viewers can see the formulas.
Problems to consider
In the earlier warning section, I mentioned that users can circumvent protection by copying data into another workbook or some other program. The good news is that this method copies only data, not formulas. However, you can prevent this breach with a simple formatting setting; don’t allow users to select protected cells.
As we saw in Figure D, Excel offers several options that add flexibility to the protection feature. In particular, the first two, Select Locked Cells and Select Unlocked, are checked by default. By unchecking Select Locked Cells, you can prevent users from selecting and copying data in those cells into another program. Crisis averted-kind of.
Permission to copy the entire workbook is another problem. For instance, if you open a protected Excel file in the browser edition, you can see hidden formulas. You can’t modify locked cells; the browser supports this protection. However, users will be able to view your formulas. You’ll need help from your system administrator to keep users from copying the Excel workbook.
Resource Credit | TechRepublic