Making protected Excel Spreadsheets easier to use

Allow only certain values into a cell

This is a preview lesson
Register or sign in to take this lesson.
Note: All Excel protection is relatively weak. It is useful to stop casual users but it is not for top secret information.

Everything before is focused on allowing or not allowing a user to do something in a cell. So they either can change a cell or not. But what if you want them to change the contents of a cell but limit the options e.g. they must enter a number bigger than 10%.

To follow along go to 19.11

Another useful level of protection is controlling what they put into a cell and this is possible with the Data Validation feature.

So as an example, if we want to control what will be entered as a sales price in cell C4 below (remember that previously we left this cell unlocked as it is an input, but now we are going to protect ourselves from finger trouble).

We can click on the cell, and in the Data tab we click Data Validation.

You can then specify what you want to allow (we have said the user can enter a number with a decimal) but it must be between 80 and 120.

Different levels of protecting excel spreadsheets

You can even control what message must be given (Input Message tab) and what type of error should be shown (Error Alert tab) if they get it wrong.

In this case now, if you enter 85 in the cell nothing will happen and the spreadsheet will continue to work, but if you accidentally type 8.5 it will not allow the number, as it is too low.