If you love spreadsheets as much as I do, you probably know how easy it is to break them. You might have it setup to work perfectly…until you insert or delete a row…or copy and paste the wrong thing…or overwrite a formula.
For spreadsheets that I use personally, I rarely lock them down. For those spreadsheets that you are sharing with others, it will save you a lot of trouble if you use these two tips to help protect your spreadsheet.
Tip #1: Protect your Cells
Excel allows you to quickly and easily lock down cells so that users can’t edit them. Whenever they try to enter something into a locked cell, they’ll get an error message. This is important especially for formulas. Here’s how to do it:
- Select the Cells that should be unlocked
By default, Microsoft has all cells set to Locked. If you hold Ctrl, you can click on all the cells that you want the user to be able to change. - Right Click one of the selected cells and choose Format Cells
You could also go to the menu where you can change fonts, and choose the little arrow in the bottom-right corner. - Go to the Protection Tab
This is the tab on the far right. - Uncheck Locked and hit OK
- Go to the Review Menu and click Protect Sheet
- Make sure the top two options are selected, and click OK
I normally don’t set a password unless I’m really worried about people trying to break the spreadsheet. I normally just allow users to Select Locked or Unlocked Cells. They can edit the values of Unlocked Cells. They can see what is in Locked Cells but cannot edit them.
The button now has changed from Protect Sheet to Unprotect Sheet. To Unprotect Cells, click Unprotect Sheet. If there is a password, you’ll be prompted for it.
Tip #2: Using a Dropdown List
At times, you will want to limit a user to only a few options on what to enter into a cell. Maybe they need to choose from a list of expense accounts or a list of employees or a list of months. You can provide them with a Dropdown list which makes it easier for the user while adding protection to the spreadsheet. Here’s how:
- Enter your list somewhere in the spreadsheet
If the list is not set in stone, I put the list on a separate sheet. For example, if I create a timesheet for someone where they track their time by client, the user would need to keep adding to that list…hopefully. If the list is set in stone, I normally create it off to the side of the sheet and hide the column. - Select the cell you want the dropdown list in and go to the Data menu and choose Data Validation and then Data Validation
- In the Settings Tab, choose List from the Allow menu
There are a lot of other options which may be helpful such as limiting numbers or dates entered to a range. The two check boxes to the right should be checked. - Click the Arrow in Source, Select the list you entered and press the Enter key and then click OK
If you expect the list to grow over time, make sure you select empty cells under the list as well. These will be filled in as the user enters new values in the list.
Hopefully these two tips help you stay out of the spreadsheet repair business!
Useful Links
- Example Spreadsheet: https://jctaccounting.com/wp-content/uploads/2023/10/HealthFlex_Rates_2024_Excel.xlsx
- Microsoft’s Instructions on Protecting Cells: https://support.microsoft.com/en-us/office/lock-cells-to-protect-them-cb7835f6-9c37-4161-bb53-d1c410acaf21
- Microsoft’s Instructions on Dropdown Lists: https://support.microsoft.com/en-us/office/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b