Category: Excel

Excel

How to Protect an Excel Workbook, a Sheet and its Structure

Do you want to protect your Microsoft Excel workbook or a sheet in your workbook? If so, this blog post is for you. We are going to password protect a workbook, protect a sheet in a workbook and the Excel file itself.

This is for Excel for Office 365 Windows and Mac versions. Other versions of Excel will be similar.

Protect a Workbook

I recommend you start with a blank Excel workbook, just in case you forget a password. To assign a password to an Excel workbook, do the following:

  • Open a blank Excel workbook. For demonstration purposes, I am using an Excel workbook that contains one sheet with cell data. You can choose an existing Excel workbook if you chose. See my workbook below :
Excel Security 1
  • Select File>Save As
Excel Security 2
  • Select More options (underneath Excel Workbook drop down box)
Excel Security 3
  • Select the Tools drop down box
  • Chose General Options
Excel Security 4

Set the Password(s)

You will see notice two password boxes, one to open the workbook and one to modify the workbook

  • Enter one or both passwords. Make sure you write down these passwords and store them in a safe place.

You have two other options to set if you chose, Always create backup and Enable Read-only recommended. The first is self explanatory. The second option is an extra layer of protection by enabling the workbook to read-only status upon opening.

  • Confirm your password(s) by reentering the password(s)
  • Click OK
  • Click Save
  • Close or Exit Excel

Note! A user who doesn’t know the second password can open and view the data by clicking the Read Only option. This user can still modify the data, but they are unable to save their changes to the protected workbook. However, the user can save the workbook under a new name, which circumvents the protection process. Of course, password-cracking software will get you into the workbook.

Protect a Sheet

  • Open a blank Excel workbook. For demonstration purposes, I am using an Excel workbook that contains one sheet with cell data. You can choose an existing Excel workbook if you chose.
  • Select the Review tab
  • Click the Protect icon
  • Click Protect Sheet icon. Refer to following image:
Excel Security 5
  • Enter your password in the Password to unprotect sheet box
  • Click OK
  • In the Confirm Password dialog box, type the password again
  • Click OK
  • Save your changes
  • Close or Exit Excel

Tip! To remove a password, click Protect>Unprotect Sheet and enter your password. You can also select Protect Workbook icon to protect a workbook which follows.

Protect Excel Structure

  • Open a blank Excel workbook. For demonstration purposes, I am using an Excel workbook that contains one sheet with cell data. You can choose an existing Excel workbook if you chose.
  • Select the Review tab
  • Click the Protect icon
  • Click Protect Workbook icon. Refer to the following image:
Excel Security 6

Select the Windows option if you want to prevent users from moving, resizing, or closing the workbook window or hide/unhide windows

  • Enter your password
  • Click OK
  • In the Confirm Password dialog box, type the password again
  • Click OK
  • Save your changes
  • Close or Exit Excel

Protect the Excel File

To prevent users from accessing the data in your Excel file, we are going to protect the file with a password

  • Open a blank Excel workbook. For demonstration purposes, I am using an Excel workbook that contains one sheet with cell data. You can choose an existing Excel workbook if you chose.
  • Select File>Info. Refer to my image below:
Excel Security 7
  • Click Protect Workbook icon
  • Chose Encrypt with Password option
Excel Security 8
  • Enter a password in the Encrypt Document dialog box
  • Click OK
  • In the Confirm Password dialog box, type the password again
  • Save your changes
  • Close or Exit Excel

Be cautious when sharing files or passwords with other users. You still run the risk of passwords falling into the hands of unintended users. Remember to write down your passwords and store them in a safe place.

Leave a Comment

Please feel free to leave a comment. I would love hearing from you. Do you have a computer tech question? I will do my best to answer your inquiry. Please mention the app and version that you are using. To help me out, you can send screenshots of your data related to your question.

How to Create a Microsoft Excel Drop Down List

Drop down lists are used to select specific data, in our example, a list of Salutations. If the user tries to enter a Salutation that is not in the list, Excel rejects the entry.

I am using a simple drop down list in an Excel sheet. This feature is not well known nor initiative.

Salutations was a BIG DEAL for a religious organization that I worked for as a Consultant. The company had a donor base of 25,000+ names/addresses. (Of course, I was using a database, not Excel, to manage the data.) If the Salutation was incorrect on a donor letter, some donors refused to give monies for the company projects. For example, do you address them as Mr. and Mrs. or Dr. and Mrs. or Mr. and Dr.? Some donors wanted no Salutation. I spent a lot time getting the Salutations correct.

This is for Excel for Office 365 Windows and Mac versions. Other versions of Excel will be similar.

Setting Up the Excel Sheet

  • Enter the headings in Row 1 (Optional)
  • Create a list of cells E2:E5. Alternatively, you could put the list in another sheet.
  • Select the empty cell A2. (You can position the drop down list in most any cell or even multiple cells.)
Excel Drop Down List Setup
Setting up Excel for a drop down list

Using Data Validation

  • Select the Data tab
  • Select Data Validation
  • Choose List from the Allow options drop down list
  • Click the Source control and drag to highlight cells E2:E5. Alternatively, you could enter the cell reference $E$2:$E$5 or use a sheet and cell reference if your list is in another sheet.
  • Ensure that the In Cell dropdown option is checked. If you leave this unchecked, Excel forces users to enter only the list values, but it won’t present a drop down list. The users would have to guess the values; not a good idea!

See the following screenshot:

Excel Drop Down List Source
Entering the Allow and Source option
  • Click OK

You can add the drop down list to multiple cells. Select the range of data input cells instead of a single cell. Also, you can select noncontinuous cells by holding down the Shift key while you click the appropriate cells.

You can copy and paste this drop down cell to any other cells in your sheet.

Below is a screenshot where Mr. was selected as a Salutation for cell A2:

Excel Drop Down List Selected
Excel drop down list selection

Leave a Comment

Please feel free to leave a comment. I would love hearing from you. Do you have a computer tech question? I will do my best to answer your inquiry. Please mention the app and version that you are using. To help me out, you can send screenshots of your data related to your question.