5. Sheets: Add Data Validation to Limit Options

In the previous video, you added formatting and organized your spreadsheet so it is easier to read. In this video, you will add data validation to control the type of information in particular cells or columns to make your spreadsheet even more effective. Data validation is a method of limiting the entries or responses that can be used in your spreadsheet.

Earlier in this lesson, you added a new row of data by typing in your spreadsheet. But this can lead to errors. For example, your fundraiser may only sell certain sizes of shirts. A dropdown menu made with data validation lists the sizes to choose from when placing a new order, based on what is included in your sheet. This makes your choices clear and prevents mistakes, such as typing a size into the spreadsheet that isn’t offered. Data validation is especially useful for collaborators, who may not be as familiar as you are with the options of shirt design, for example.

Plus, it keeps the formatting consistent. To start, highlight the Amount column and open Data validation. This column shows the number of shirts purchased in each order. You wouldn’t want someone to accidentally enter a word instead of a number, especially if you’re planning on sorting your data by the number of shirts purchased, for example. Add data validation that limits the inputs to a number within a range. However, you probably don’t need data validation in your title or header rows, or you header may be flagged as an “invalid input.”

Remove the data validation from cells that don’t need it.

Next, define the inputs of the other columns with a limited set of options with dropdown menus. Type the sizes that appear in your sheet...

Or copy and paste the options from the cells. Add data validation to the rest of the columns, and remove it from the cells where it doesn’t apply.

Finally, resize any columns that don’t fit the new data.

Now, it’s your turn: Add data validation for a range of numbers in the Amount column, Add data validation for a list of items for the rest of the columns, Remove data validation for any header rows, And resize columns, if necessary.