In the previous video, you added formatting and organized your spreadsheet so it is easierto read. In this video, you will add data validationto control the type of information in particular cells or columns to make your spreadsheeteven more effective. Data validation is a method of limiting theentries 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. Butthis can lead to errors. For example, your fundraiser may only sellcertain sizes of shirts. A dropdown menu made with data validation lists the sizes to choosefrom when placing a new order, based on what is included in your sheet. This makes yourchoices clear and prevents mistakes, such as typing a size into the spreadsheet thatisn’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 andopen Data validation. This column shows the number of shirts purchasedin each order. You wouldn’t want someone to accidentally enter a word instead of anumber, especially if you’re planning on sorting your data by the number of shirtspurchased, for example. Add data validation that limits the inputsto a number within a range. However, you probably don’t need data validationin 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 dropdownmenus. 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 numbersin the Amount column, Add data validation for a list of items forthe rest of the columns, Remove data validation for any header rows,And resize columns, if necessary.
1. Introduction to G Suite Certification: Sheets - Part 1
2. Sheets: Duplicate Your Sheet and Add New Data — Add, Delete and Insert Data
3. Sheets: Format and Organize Spreadsheet Data — Use Bold and Italics, Merge Cells, Set Alignment, and Format Dates
4. Sheets: Continue Formatting and Organizing — Freeze Cells, Columns, and Rows, Add Borders, Set Fill Color, Add Borders, and Change Fonts and Sizes
5. Sheets: Add Data Validation to Limit Options
6. Sheets: Sort Your Data — Name and Sort Sheets or Ranges