You are here: Home / Security and Protection

Protect formula cells but allow access to data cells in Excel 2007 / 2010

May 12, 2011 By

By default all cells in a worksheet are locked. I.e when you protect a worksheet every cell in the worksheet will be protected.

To handle cell protection differently then you have to change the default settings for the required cells.

Here is how to protect formula cells but allow access to data cells:

  1. Select all cells in the worksheet by clicking on the top left corner of the sheet.
  2. On the Home tab, Cells group, click Format.
  3. Under Protection click Format cells. The Format Cells window will be shown.
  4. Click on the Protection tab.
  5. Uncheck the Locked and Hidden check boxes, then click OK.

These five steps will make all cells in the worksheet unprotected and can therefor be modified. Next step is to protect the formula cells, and here is how we do it:

  1. Select all cells that have formulas.
  2. On the Home tab, Cells group, click Format.
  3. Under Protection click Format cells. The Format Cells window will be shown.
  4. Click on the Protection tab.
  5. Check the Locked and Hidden check boxes, then click OK.
  6. On the Home tab, Cells group, click Format.
  7. Under Protection click Protect sheet.

Applies to: Excel 2007 , Excel 2010

  1. On the Home tab, Cells group, click Format.
  2. Under Protection click Format cells. The Format Cells window will be shown.
  3. Click on the Protection tab.

Filed Under: Excel 2007, Excel 2010, Security and Protection Tagged With: , ,

How to protect formula cells but allow access to data cells

February 5, 2009 By

By default all cells in a worksheet are locked (see image below). I.e when you protect a worksheet every cell in the worksheet will be protected.

protect1

To handle cell protection differently then you have change the default settings for the required cells.

Here is a scenario to protect formula cells but allow access to data cells:

  1. Select all cells in the worksheet by clicking on the top left corner of the sheet.
  2. On the Format menu Click Cells.
  3. Click on the Protection tab.
  4. Uncheck the Locked and Hidden check boxes, then click OK.

These four steps will make all cells in the worksheet unprotected and can therefor be modified. Next step is to protect the formula cells, and here is how we do it:

  1. Select all cells that have formulas.
  2. On the Format menu Click Cells.
  3. Click on the Protection tab.
  4. Check the Locked and Hidden check boxes, then click OK.
  5. On the Tools menu click Protection and select  Protect sheet.

Applies to: Excel 2003

Filed Under: Data, Formulas, Security and Protection Tagged With:

How to remove personal information from a workbook

June 12, 2008 By

When you save a workbook (or any other Microsoft Office document), some of your personal information like Author, Manager, Company and Last saved by are saved as File properties. If you want to share your workbook with others, then you may want to remove this information before you distribute it.

There are two things you can do:

Method 1

  1. On the File menu click Properties.
  2. Click the Summary tab.
  3. Clear any information that you don’t want to share with others.
  4. Save your workbook.

However this method is incomplete because if you click on the statistics tab you will see your name in the Last saved by field.

Method 2

  1. On the Tools menu click Options.
  2. Click the Security tab.
  3. Under Privacy options you will find a checkbox labeled: Remove personal information from file properties on save. Select this checkbox.
  4. Save your workbook.

Note

Apparently this method will only hide the information in Excel, but if you save the document in other format like a web page (*.mht) then some of this information can still be viewed. Please check this link for more details:

http://office.microsoft.com/en-us/excel/HP010503321033.aspx

Applies to: Excel 2003

Filed Under: Security and Protection Tagged With: , ,

How to protect or unprotect cells in a worksheet

May 7, 2008 By

In a previous post we talked about how to protect worksheet elements. When you protect a worksheet, by default all its cells will be locked.

If you want to protect or unprotect some cells selectively then do the following:

  1. Select the cells you want to protect / unprotect.
  2. In the main menu click Format.
  3. Click Cells. The Format Cells window will open.
  4. Click the Protection Tab.
  5. Select the Locked check box to protect the cell or clear the check box to unprotect it.

Tip.

In the Protect tab there is a checkbox named Hidden. This checkbox is applicable for cells with formulas.

Select this checkbox to hide formulas from appearing in the formula bar.

Note.

  1. If the Cells command is dimmed when you are trying to protect cells, then parts of the worksheet may already been locked.

Click Tools —> Protection —> Unprotect Sheet.

  1. When you are done, and in order for your cells to be locked then you must protect the worksheet again through the Tools menu.

Filed Under: Security and Protection Tagged With: , ,

How to protect Worksheet elements

May 3, 2008 By

To prevent unauthorized users from performing certain worksheet tasks, like formatting cells, deleting rows or inserting rows, follow these steps:

  1. Switch to the worksheet you want to protect.
  1. On the Tools menu, point to Protection, and then click Protect Sheet.
  2. Type a password for the sheet. Then reconfirm the password when asked for that.
  3. In the list: Allow all users of this worksheet to , check the elements which you want the users to have access to. All unchecked items will be protected.

xprotectsheet.gif

Tip:

You can protect the worksheet without entering a password, but in this case anyone can unprotect it. This is only useful if you want to avoid the worksheet being modified accidentally.

Related Links:

How to protect a workbook with a password

Applies to: Excel 2003

Filed Under: Security and Protection Tagged With: , , ,

How to protect a workbook with a password

April 28, 2008 By

To allow only authorized users to view or modify your Excel workbook, you can secure your entire workbook file with a password. Follow the steps below to achieve this goal:

  1. On the File menu, click Save As.
  2. Click Tools.
  3. On the Tools menu, click General Options.

Save Options

  1. If you want users to enter a password before they can view the workbook, type a password in the Password to open box.
  2. If you want users to enter a password before they can save changes to the workbook, type a password in the Password to modify box.
  3. Click OK.
  4. You will be prompted to reenter your password(s) for confirmation.
  5. Confirm your password(s) then click Save.

Tip:

To remove a password from a password-protected workbook (If you have the right to modify of course), then follow the same steps to assign a password i.e.

File –> Save As –> Tools –> General Options

Clear the password(s) –> Click OK –> Click Save.

Notes:

  1. Passwords are case sensitive.
  2. If you are the only one who is allowed to open the workbook, then there is no need to enter the Password to modify.
  3. Make sure that you don’t lose your passwords because there is no easy way to recover them.
  4. This procedure applies to a new workbook as well as an existing workbook for which you want to add a password. In the second case you will be asked if you want to replace the existing file. Click Yes.

Related Links:

How to protect worksheet elements

Applies to: Excel 2003

Filed Under: Security and Protection Tagged With: , , ,