Data validation and named ranges
1. Data validation and named ranges
Person: When entering data into a Google Sheet manually-- especially if there are a lot of entries and collaborators adding data-- it's helpful to ensure that what is entered into a cell is valid in the context of the sheet. For example, On the Rise wants to enter puff pastry orders into a Google Sheet. A few of the columns for the orders are puff pastry type, and puff pastry size. For data entry into these columns, it would reduce errors to prevent anything being added That is not valid for the data type. With data validation, entries for puff pastry type can be limited to just the four types that the company has, along with the three available sizes in the size column. Any data that is not valid could be rejected with a help text that details the only values that can be entered. Or even more useful, the cells in each column could have a dropdown list with the appropriate values to select from when adding to the sheet. All of this is accomplished with data validation. To create data validation rules, open the data menu and click "Data validation." Or right-click a cell and click on "Data validation" in the menu. The data validation dialog will appear. Choose a cell range for your data validation rule to apply to. Add criteria, which is the data that will be accepted for the entry in the range. The criteria can be a list from a range, a list of items, number, text, date, checkbox, or a custom formula. Data will be validated upon entry, but to ensure there are no typos or incorrect values, check the "Show dropdown list in cell" box to speed up accurate entries. When invalid data is entered, choose to either show a warning or to reject the input. And a tooltip can be displayed when a person hovers their pointer over the cell with custom text if the box for "show validation help" is checked. Click "Save" to apply the validation. When you or a collaborator enter a value in the cell or cell range where the data validation rules are applied, they will be enforced by Google Sheets. When working with data sets in Google Sheets, it can improve accuracy and efficiency to name ranges of data in a sheet. Once a range is named, it can be referenced by that name in a formula. So instead of a formula for calculating sales results for the year being written as "=SUM(A3:D15, F3:F15)," it could be "=SUM(monthly results, rep totals)," which is more human-readable and easier to understand for collaborators also editing the sheet. To add a named range, open the data menu and click "Named ranges," or right-click on a cell or selected range and click "Define named range" from the menu. The named ranges panel will open on the right side of the sheet. Enter a name for your range. If necessary, click to enter the range address or select it in the sheet. Then click "Done." Now you can use a named range as a reference in formulas in the sheet.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.