Data Validation gives you more control over your spreadsheets

Tuesday, February 17, 2009 9:20 AM

Have you ever wanted to suggest or control what people enter in your spreadsheet? Now you can, using the new data validation feature in spreadsheets. Once a range of cells are selected, the feature can be accessed in Tools > Data Validation and lets you create rules for numbers, text, and dates. Let's see how it works.

Say you're hosting a party, you've invited your friends, and want them to fill out a spreadsheet to indicate the best way to contact them, how many guests they're planning to bring, and what flavor of ice cream they prefer to eat.

First, select a range of cells and use Tools > Data Validation to set up the rules. You can set up a rule to make sure that only valid email addresses are allowed so you know the best way to contact your friends.

In this case, you can prevent users from entering in data (an invalid email) that breaks the rule.

Next, you may want to suggest that your friends don't invite more than two additional guests, each. Another rule will prompt this suggestion for a different range of cells.

Notice that the spreadsheet prevents you from entering a number greater than 2:
Finally, if you're a chocolate lover, you may want to kindly suggest to your friends that their favorite choice of ice cream really is chocolate. Another data validation rule for text should do the trick.

For this rule, your friends will see an ice cream flavor suggestion but they'll still be allowed to enter different flavors.

Of course, there are other uses cases of data validation rules, like making sure your friends are entering their ideal dates for the party within a certain specified time range. Have fun planning your next party (with chocolate ice cream)!

A few notes on this new feature:

• If data validation is applied to cells containing data, rules won't be applied until the data is modified.
• Only one validation rule can be applied to each cell.
  

Posted by: Radhika Lakshmanan, Software Engineer