How to Protect an Excel 2010 Worksheet


excelThere are some occasions when you would like to prevent your colleagues from making changes to your cells or perhaps you do not want them to have the capability of seeing your formulas in the Formula bar.

Fortunately, Excel will allow you to do this and more, but unfortunately, it is not always intuitive where to find these well hidden features and options. Follow along though and I will guide you.

In Excel, formulas are visible in your Formula bar by default, even if you have protected your worksheet.

Follow the steps below:

  1. In your worksheet, select the cells you would like to hide. If you would like to hide all formulas in your worksheet, you can quickly select all cells containing formulas by clicking F5 | Special | Formulas | OK.

Go-To-Special

  1. On your Home tab, in the Cells group, click Protection | Protect Sheet.
  2. Select the Hidden option.
  3. Click OK.

If you look in your formula bar, you will notice that your formula still appears.

Most folks do not read the message on the Format Cells dialog box when they select the Hidden option, but if they did, they would know that you have to protect your worksheet before the hidden or Locked features will be enabled.


format cells

Once you have selected the cells you would like to hide from your Formula Bar, you have to protect them.

Remember, every cell in your worksheet is locked by default. But until and unless you Protect your worksheet, they are not really locked.

You need to be aware, that since all the cells are by default locked, once you protect your worksheet, users will be prevented from editing any cells. So, before you enable worksheet protection, make certain that you select any cells you would like your users to have the capability to edit when you protect your worksheet and deselect the Locked option on the Format Cells dialog box.

  1. On your Home tab, in the Cells group, click the Format drop-down arrow.
  2. Click the Protection tab and deselect the Locked option.
  3. Click OK.

You are now ready to protect your sheet.

  • On the Review tab, in the Changes group, click Protect Sheet.

Protect-Sheet

You have several different options available to you in the Protect Sheet dialog box. As you can see above, you can add a password so that savvy users cannot unprotect your worksheet You can also choose any of the other options shown above if you so desire.


 

Posted in:
About the Author

Carol Bratt

Carol holds A+, MCP, and MOS computer certifications and is the resident DCT Office expert. She trains the staffs of small law firms in the use of Microsoft Office applications and has authored many books covering Microsoft Office as well as written articles for Infopackets, TechnoLawyer, and Digital Harbor. For more Microsoft Office tips visit Carol’s Corner Office or follow Carol on FaceBook and Twitter.