Subscriber Don Enderud wrote to ask a question about Microsoft Excel. He wants to use one of the features of Excel that is very valuable when you create a spreadsheet for others to use.
A friend is sending me a very large spreadsheet (approx. 40 tabs, each worksheet with many columns and rows) which contains hundreds, if not thousands of formulas and results cells.
There are also data entry cells in each worksheet. The only thing unique about the data entry cells is that they have a distinct color — yellow.
Do you know of some way in Excel to protect all cells except for the yellow ones — preferably with a single command?
In this case, it sounds like Don is the recipient of the spreadsheet, but he’s still choosing to make use of a valuable prrotection feature in Excel.
If you’re responsible for a spreadsheet that someone else uses, or even if the integrity of the spreadsheet is very important to you, you really don’t want users to be able to modify calculations. Someone inputting an "answer" in a cell that’s supposed to be making a calculation, may get a correct result this time — but the modified cell is going to mess up their answers the next time they use the spreadsheet.
You can protect them, but it’s a few manual steps and depends on how many separate blocks of cells need to be unlocked for editing. How easy it is to protect cells also depends on which version of Excel you’re using.
With Excel 2007:
- Go to the Review tab, select cell(s) to be used for entering data.
- Click on the Allow Users to Edit Ranges in the Review Ribbon.
- Click “New” (this will default to having those cells selected and will set a default name for the input range.
- Click OK
- Click OK or Apply and OK.
- On the Review Ribbon, click on Protect Sheet
- Make sure the top checkbox is checked “Protect worksheet and contents of locked cells”
- Set a password if you want.
- Set the options that you want users to be able to do (when the sheet is protected).
- Click OK
Other versions of Excel have similar functions, but the steps will be a little different.
By the way, the first time I used the protect/unprotect functions on a spreadsheet, it was with an inventory & cost accounting system that I built in Lotus 1-2-3 version 2, running under MS-DOS, back in about late 1987!
The ability to protect portions of spreadsheets that will be used by others is just as important now as it was then.