If you develop solutions for other people using Excel as platform you will almost certainly have been in a situation where a user has (accidentally?) managed to mess things up by trying to change something they shouldn’t.
In all good software solutions, the users should only be able to edit the parts they need to, and as Excel can be used a vehicle to provide commercial solutions Microsoft have included a nice little feature for securing parts of your spreadsheet.
By default, all cells in a spreadsheet are ‘locked’. Any cells (or group of cells) locking status can be checked by selecting the cells, then going to the ‘Format’ menu, selecting ‘Cells’, and then the ‘Protection’ tab. (Shown below)
The tab shows two attributes for the cells status, they can either be:
- Locked – when selected a cell cannot have certain actions performed upon it, depending the options selected when the password is applied.
- Hidden – when selected the contents of a cell are only displayed on the worksheet, not in the formula bar, this is useful for keeping formulae hidden, and only having the result displayed on screen.
The tick boxes themselves can have 3 different states:
- Ticked – The selection is either locked or hidden, depending on which box is ticked.
- Empty – The selection is not locked or hidden, depending on which box is ticked.
- Ticked and Greyed out – Seen with a multiple cell selection, some cells are either hidden or locked, and some are not.
Once you have selected the status for your cells, go to the ‘Tools’ menu, select the ‘Protection’ option then ‘Protect sheet’.
You will now be presented with a dialog box showing the various cell attributes that can be selected. Make your choices, and enter a password in the appropriate box.
You will now find that the cells you selected for locking have limited functionality in line with the choices you made.