ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

How to password protect Excel spreadsheets, worksheets or a cell range in Excel 2007 and Excel 2010

Updated on July 17, 2013

Why you should always password protect your important or confidential Excel spreadsheets

Protecting a company or individuals sensitive data is a very crucial in today’s highly connected world. Documents containing data such as payroll information, trading algorithms, confidential information or company secrets should always be password protected alongside normal document protection (restricted network access etc.).

In addition to password protecting an entire document, more granular protection can be used which allows you to:

  • Restrict access to spreadsheets, worksheets or even specific cells to allow only certain users access to particular parts of the document
  • Granting users read only or modify permissions using a password
  • Giving or denying users the ability to do very specific things in a protected worksheet or locked cells including; deleting rows or columns, editing objects, sorting or using pivot tables
  • Unlocking Cell ranges in protected documents using a password
  • Preventing users from adding, changing or deleting new worksheets and changing the size and position of worksheet windows
  • Encrypting spreadsheets using built-in Office encryption

Password protecting a document is of particular importance when you share a document. I have a hub that covers sharing documents in great detail. It covers:

  • Sharing a document
  • Tracking and highlighting changes made to the workbook
  • Reviewing and accepting or rejecting tracked changes
  • Using Document Inspector to check documents for hidden and personal data
  • Compatibility Checker, which allows you to verify that your spreadsheet will work with previous versions of Excel
  • Un-sharing documents

To learn more about sharing documents, the hub can be found here:

http://robbiecwilson.hubpages.com/hub/Guide-to-sharing-documents-in-Excel-2007-and-Excel-2010

Microsoft provides many ways of protecting your documents in Excel 2007 and Excel 2010.
Microsoft provides many ways of protecting your documents in Excel 2007 and Excel 2010. | Source

How to password protect a spreadsheet and encrypt a document in Excel 2007 and Excel 2010

First, let’s begin by protecting an entire spreadsheet. This will prevent anyone from opening the document, even in Read Only mode unless they know the password. To do this in Excel 2007:

  • Click on the Office button
  • Select Prepare
  • Choose Encrypt Document
  • Enter the password, press OK and then re-enter the password
  • Save the workbook to confirm the changes

Set a strong password to encrypt a confidential document in Excel 2007 and Excel 2010 and then store the password in a secure location.
Set a strong password to encrypt a confidential document in Excel 2007 and Excel 2010 and then store the password in a secure location. | Source

Note: As Microsoft advises, you must not forget the password or you will not be able to get into the document. If necessary, write the password down and store it in a secure location such as a safe

When opening an encrypted workbook in Excel 2007 and Excel 2010, users will be required to enter the password to access the file.
When opening an encrypted workbook in Excel 2007 and Excel 2010, users will be required to enter the password to access the file. | Source
  • In Excel 2010:
  • Select the File menu
  • Click Info
  • Next, select Encrypt with password and as we did with Excel 2007, enter the password, click OK and then enter the password once more
  • Save the spreadsheet to finalise the changes

To clear the password from the document, repeat the above steps and clear the password

Allowing users to modify a workbook or open a read only copy in Excel 2007 and Excel 2010

Excel makes it possible to allow users to open a document and be able to modify it providing they have a password or use read only mode if they do not. To do this in Excel 2007 and Excel 2010 (where Excel 2010 differs, additional instructions will be provided):

  • Select the Office button (choose the File menu in Excel 2010)
  • Choose Save As
  • Click the Tools drop down (next to the Save button in the bottom left)

How to access the Tools drop down box in Save As in Excel 2007 and Excel 2010.
How to access the Tools drop down box in Save As in Excel 2007 and Excel 2010. | Source
  • Select General Options

In this dialogue box, you have several additional options. In this example, we will be adding a password to modify the spreadsheet

Dialogue box to add a password to allow a user to modify the file as well as another password to open the file in Excel 2007 and Excel 2010. You can also allow people without a password read only access.
Dialogue box to add a password to allow a user to modify the file as well as another password to open the file in Excel 2007 and Excel 2010. You can also allow people without a password read only access. | Source
Password box as it appears to a user opening a protected file in Excel 2007 and Excel 2010.
Password box as it appears to a user opening a protected file in Excel 2007 and Excel 2010. | Source

If users do not have the modify password they can open a read only copy at this point.

  • If you choose Read–only recommended, users will receive the following upon entering the password

Dialogue box inviting a user to open a read only copy of a document in Excel 2007 and Excel 2010.
Dialogue box inviting a user to open a read only copy of a document in Excel 2007 and Excel 2010. | Source
  • Save the document to confirm the changes

As before if you want to clear the password, repeat the above steps and clear the password.

Protect a worksheet in Excel 2007 and Excel 2010

Rather than protecting an entire spreadsheet, you may want to protect a specific worksheet if, for example, it contains formulas, or data you don’t want users to be able to edit or change. To achieve this:

  • First, select the Protect Sheet button from Changes group on the Review tab.
  • Enter a Password to unprotect sheet
  • Select the options you want to protect (Select locked cells and Select unlocked cells are the only options that are selected by default)
  • Click OK and re-enter the password
  • Save the document to confirm the changes to the sheet

Options that can be allowed or prohibited on a single sheet in Excel 2007 and Excel 2010.
Options that can be allowed or prohibited on a single sheet in Excel 2007 and Excel 2010. | Source

As you can see from the screen shot above, there is significant granularity in what you can protect. If you for example want people to only be able to use Pivot Table reports, you can allow them to do this while preventing them from doing anything else to that worksheet.

If a user attempts to change a cell, they will receive the following error:

Dialogue box advising a user that the sheet they are trying to modify is protected in Excel 2007 and Excel 2010.
Dialogue box advising a user that the sheet they are trying to modify is protected in Excel 2007 and Excel 2010. | Source

Note: All cells are locked by default, but this lock does not have an effect until the worksheet is protected as we did above.

Allowing users to edit specific ranges of cells in Excel 2007 and Excel 2010

In certain circumstances, you may wish to allow people to edit a range of cells on a protected worksheet.

For example, you may have a shared workbook where staff enter data such as how long each day they spend on administration. You could allow each staff member to edit their own column, but no anybody else's. To set this up:

  • Select a range on the worksheet
  • Click on the Allow Users to Edit Ranges button in the Changes group on the Review tab
  • Choose New
  • Enter a Title
  • Type in a Range Password
  • Add permissions using the Permissions button should you need to (so we would Allow one user to Edit Range without a password and everyone would get the Deny permission)
  • Repeat the above instructions for each additional range you want to restrict.
  • Select the Protect Sheet button and then save the spreadsheet to implement the changes

How to configure a number of cell ranges to allow people to edit one range but not others in Excel 2007 and Excel 2010.
How to configure a number of cell ranges to allow people to edit one range but not others in Excel 2007 and Excel 2010. | Source

Now, when you try to edit a cell in those ranges protected above, you receive the following dialogue box:

Dialogue box a user will receive when trying to modify a range of cells that do not have permission to modify in Excel 2007 and Excel 2010.
Dialogue box a user will receive when trying to modify a range of cells that do not have permission to modify in Excel 2007 and Excel 2010. | Source

Preventing users from changing the structure of a workbook or changing the size or position of windows in Excel 2007 and Excel 2010

When you restrict a user from changing the structure of a workbook, they are unable to do the following:

  • See hidden worksheets
  • Rename, move, delete, insert or hide worksheets
  • Move or copy worksheets within the existing workbook or to a new one
  • See the source data of a Pivot table
  • Record a new macro

If you protect windows:

  • It is not possible for users to change the size of move a window
  • Moving or closing windows is not permitted

To do this in Excel 2007:

  • Select the Protect Workbook button in the Changes group on the Review tab
  • In the Restrict Editing group, choose Protect Structure and Windows
  • Tick the option you would like to restrict and assign a Password
  • Click OK, confirm the password and select OK again
  • Save the document to confirm the changes

In Excel 2010:

  • Press the Protect Workbook button in the Changes group on the Review tab
  • As above, select the option you wish to prevent users from changing and select a Password
  • Press OK, enter the password again and click OK
  • As before Save the document

Conclusion

Protecting documents created in Excel is very important as Excel is used by companies around the world to store both confidential information and business critical data. Microsoft recognises this and provides a large and powerful array of protection for entire workbooks, sheets and even cell ranges or portions of a document. In addition:

  • User can be granted read only or modify permissions to a workbook using a password
  • Very specific actions can be allowed or denied including inserting or deleting rows and columns, formatting cells and using pivot tables
  • Users can be prevented from changing, removing or copying worksheets and adjusting spreadsheets windows
  • Workbooks can be encrypted using built-in Office encryption

Many thanks for reading; I hope that you have found this hub informative and useful. Please feel free to leave any comments you may have below.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image
      Author

      Robbie C Wilson 3 years ago

      Hi Vivek,

      Thanks for your kind comment. I am so glad that you found the information in my hub useful.

    • Vivek Thota profile image

      Vivek Thota 3 years ago from Delhi, India

      good one useful info...