Here is a way to set up a form in Excel to make it easier for users to enter data, using sheet Protection and unlocked cells:
You can download the file here to follow along.
Creating forms that are easy for your users can be a challenge. Here is an example of a simple form that I need someone to complete:
When a user starts typing into any of the fields, they have to tab, enter, click on or use the cursor movement keys to get to the next appropriate cell. A form such as this also allows them to enter data into an area that is not intended for information.
A solution for this is to protect the worksheet after you unlock the cells where you want data to be entered. By default, Excel formats all cells as locked, so that when a sheet is protected, data cannot be entered into any cell. If you want to allow for entry into a cell or cells, you must first “unlock” those cells prior to protecting the worksheet.
You can confirm if a cell is formatted as locked or unlocked one of two ways:
1) Right click on the cell, go to “Format Cells” and look for the check mark in the Locked box on the protection tab.
2) After clicking on the cell, you can also go to the Format option of the Cells group on the Home tab and look for a box around the padlock next to “Lock Cell”. If there is a box around it, then the cell is locked.
Highlight all the cells where you want data to be entered by holding down the Ctrl key and clicking on each of the cells. Then by using one of the two methods indicated above, unlock those cells.
Next you must protect the worksheet. You can do this by going to the Changes group of the Review tab and clicking on Protect Sheet. The dialog box that pops up allows you to enter a password, but that is not necessary to protect the sheet. You may, however, want to include a password to avoid any potential security risk.
What you have now done is to protect the entire worksheet from data entry EXCEPT for those cells that you unlocked. A significant benefit of this process is that now a user can TAB after entry and Excel will take them only to the next unlocked cell, making it much easier for the user to complete the form.
Way to go, Excel!