The Data Tab

Key Terms:

• Data validation: Tool that limits potential inputs in a range

• Cleanup suggestions: Automated formatting tips to improve readability of a spreadsheet

• Column stats: Automated summary statistics of a particular column

Data Validation

While spreadsheets made for personal use might not need this feature, data validation can certainly be helpful when writing interactive spreadsheets for other people or even to give yourself a reminder as to what accepted inputs are.

Data validation can be customized, but the core concept is that using data validation on a range will limit what values the user will be allowed to input. This can be done in various ways, including limiting to a certain type of data or using a dropdown list of values, but the key idea is that the user will only be able to input certain approved values. For example, this might be useful in a spreadsheet where the user’s input is used to calculate a certain quantity: the spreadsheet author can prevent the formula from returning an error by using data validation.

Data validation can also display itself to the user in various ways: Google Sheets allows users to choose to either display a warning or simply reject the value from being input into a cell. Warning text can also be implemented to guide the user as to what values are acceptable to input. Regardless of the exact implementation, data validation can be a helpful tool to reduce confusion for anyone using a spreadsheet without full knowledge of what it does or what input is required.

Cleanup Suggestions

Similar to data validation, cleanup suggestions are not a computational tool as much as a convenience; they do not calculate or manipulate any data, but rather make a spreadsheet more approachable for a future user.

Once again, this feature is fully automated by Google Sheets and requires no user input. Its primary purpose is to improve readability, but the suggestions will not always be particularly helpful or relevant. It can, however, be used as a guiding tool to remind the spreadsheet author to make formatting changes that might help someone more easily interpret the data. For example, with a table with a long list of rows, it might be helpful to use alternating colors to clearly separate rows from each other. In a table with large numbers, it could be useful to right-align the numbers so that the commas match up, making it easier to compare quantities at a glance. Like Google Sheets Explore, cleanup suggestions are by no means a comprehensive list of improvements that should be made to a spreadsheet, but are best used as guidelines and potential ideas that could make the spreadsheet more informative or easy to use for an unfamiliar user.

Column Stats

Column stats are exactly what the name suggests; they are statistics calculated in reference to a particular column. Based on whether the data is qualitative or quantitative, various statistics are calculated, such as mean, median, sum, minimum and maximum. Additionally, various visualizations are created, namely a count (bar graph) and distribution (histogram). Various convenient options are provided as well, such as the ability to skip a certain amount of rows (to account for labels at the top of a table), as well as information on total cells in the column, empty cells, and unique values. While these statistics can all be calculated using formulas, if your data is already located in a convenient column, then column statistics can be a helpful tool to quickly get information about a dataset without committing to adding cells for calculations. Like the two previous features listed, column statistics are not a comprehensive tool, but rather one that can be used to accelerate some basic processes and get insights as to what information might be useful for other users of the spreadsheet.