Hiding and Protecting Excel Formulas

By Dan DiNicolo, April 12th, 2007 Posted in Excel. Subscribe to our RSS Feed

After taking the time to create an Excel spreadsheet laden with formulas that actually work correctly, the last thing you want to have to deal with is some adventurous rogue user messing with your hard work. While the majority of people will leave formulas that they don’t understand alone, others will attempt to tinker with formulas or even overwrite them completely. For cases where you’re creating Excel spreadsheets for other users and don’t want your formulas messed with, one of your best options is to hide the formulas completely.

Hiding your formulas and preventing changes by other users is a multi-step process. Begin by right-clicking on the cell (or multiple cells) where the formula exists and select Format Cells. Next, click the Protection tab. Ensure that both the Locked and Hidden checkboxes are checked, and then click OK.

At this point you’ll notice that the formula is still visible when you click on its cell. To change this you need to complete step 2, where you actually protect the worksheet. To do this, click Tools > Protection > Protect Sheet. In the Protect Sheet window, you can either click OK to protect the sheet without a password, or enter a password that will need to be supplied to unprotect the sheet and make changes and then click OK. Note: don’t forget the password or you’ll lock yourself out!

After enabling protection, you’ll notice that the formula in the cell you applied the Locked and Hidden settings to is no longer visible. If a protection password was not applied, any user will be able to select Tools > Protection > Unprotect Sheet to make the formula visible once again. If a password was applied, it will be needed to unprotect the sheet to make changes.

Written by Dan DiNicolo - Visit Website

All Tutorials by Category:

Entire site Copyright © 1999-2007 2000Trainers.com, all rights reserved.
Content on this site may not be copied or reproduced in any way without permission.

IT Showcase