How To Password Protect Excel Worksheets and Lock Unlock Cells
Applies to all Excel templates including: "Georges Budget for Excel", "Georges Excel Checkbook Register", "Georges Excel Checkbook for Mac"
(back to All Support Topics)
Why protect Excel worksheets and cells
You can password protect a cell, range of cells, or entire worksheet of an Excel file. For example, you may want to protect cells that have Excel formulas in them and leave the cells where users enter data unlocked.
When you protect a cell, range of cells, or entire worksheet, you are locking that cell, range of cells, or entire worksheet and preventing users from making changes to the locked cells contents, such as the Excel formula in the cell and also preventing changes made by certain worksheet commands such as cell formatting and deleting rows. When protecting an Excel worksheet, you can also allow certain changes such as allowing users to change only the cells formatting or allowing sorting or filtering on a protected sheet.
In the Excel worksheets that contain the Excel checkbook registers of both the checkbook software (Georges Excel Checkbook Register and Georges Excel Checkbook for Mac) and the personal finance software (Georges Budget for Excel), all the cells are locked and password protected except the cells where you enter register transactions including the Date, Check, Description, Memo, Rec, Category and Amount. If you try to enter data in a locked password protected cell, Excel will show the following message. Its not an error message, and in regards to the Excel templates on BuyExcelTemplates.com, its just letting you know that you can't enter data in the locked cells and you can only enter data in the cells that are unlocked.
How to protect password protect Excel worksheets and cells
In Excel 2007, Excel 2010, Excel 2013, Excel 2016, and Excel 2016 for Mac, to password protect an Excel worksheet for a particular Excel file:
- Select the cell, range of cells within a worksheet, or entire worksheet that you want to lock for protection. Then click the Home Tab in the Excel Ribbon, and then click the expand arrow in the Font group, and then in the window that open up, click the Protection tab. Put a checkmark next to "Locked" if you want the particular cell, ranges of cells or entire worksheet that you have selected to be locked for protection. Remove the checkmark if you do not want the cell, range of cells or entire worksheet that you have selected to be locked for protection. You can also put a checkmark next to Hidden to hide the formulas in the selected cells. Note that locking cells and or hiding Excel formulas has no effect until you protect the worksheet as noted in step 2.
- Click the Review Tab in the Excel Ribbon, and then click Protect Sheet command button, and then in the Window that opens up, put a checkmark next to "Protect worksheets and contents of locked cells", and then enter a password (optional), and then put any optional checkmark(s) next to the items under where it says "Allow all users of this worksheet to", and in the next window, reenter password to confirm and then click OK. When prompted to reenter your password, there is a note of caution from Microsoft with states: "Caution: If you lose or forget the password, it cannot be recovered. It is advisable to keep a list of passwords and their corresponding workbook and sheet names in a safe place. Remember that passwords are case-sensitive. Note: That each worksheet in an Excel file has its own Excel worksheet protection.
The options that you can select that are listed under "Allow all users of this worksheet to" include: Note: even if you do not lock any worksheet cells as is noted in step 1, if you protect an Excel worksheet, the following commands can not be used on the protected worksheet unless you put a checkmark next to them.
- Select locked cells
- Select unloced cells
- Format cells
- Format columns
- Insert columns
- Insert rows
- Insert hyperlinks
- Delete rows
- Sort
- Use AutoFilter
- Use PivotTable reports
- Edit object
- Edit scenarios
For example, you might just want to allow users to be able to only select unlocked cells where they can enter some data. Or, allow users to use the Filter in a table while the rest of the worksheet commands are protected. Or, you might have a cell that is locked and protected but you still want the users to be able to format the cell's color or size so you would put a checkmark next to "Format Cells".
You have to save the Excel file if you want to save the Excel changes that you made including locking or unlocking cells and protecting the worksheet with or without a password.
Considerations when password protecting Excel Worksheets
- If you forget or lose your Excel worksheet password, then you will not be able to access those password protected worksheets, cells and commands of the Excel file. Microsoft does not recover lost Excel worksheet passwords. BuyExcelTemplates.com does not recover lost Excel worksheet passwords.
- Creating strong passwords so password can not be easily hacked and broken into.
- Is Excel sheet password to be shared and with whom.
- Which cells / features to lock unlock.
- Who has access to the Excel file and where is the Excel file stored and backed up?
Additional Excel protection and security tools
In addition to password protecting Excel Worksheets in the Excel file, there are additional ways to protect an Excel spreadsheet.
- Protect an Excel file.
- Protect Excel workbook structure and windows.
- Protect Excel VBA project macros.
(back to All Support Topics)