How to Maintain Data Integrity in Google Sheets

Does the idea of sharing and collaborating in Google Spreadsheets have you worried about the accuracy and consistency of data input? Perhaps you doubt that others will not be as meticulous as you are when it comes to updating important details in a spreadsheet. You can have peace in mind AND maintain a high level of data integrity in your Google spreadsheets with Data Validation.  

Don’t hesitate, VALIDATE AND COLLABORATE!

In this post, we will review the following functionalities surrounding Data Validation:

1. How to name a range of data.
2. How to create a drop-down button in cells for selecting items from a named range.
3. How to restrict input to valid dates.
4. How to restrict input to valid email address formats.

So far in our Google Sheets series, we have reviewed How to Organize our Data Effectively and How to Create Visual Representations of our Data.  We know that Google Spreadsheets offers numerous features and functions necessary for creating and maintaining elaborate spreadsheets and dashboards, and also the advantage of being able to collaborate with others simultaneously in a spreadsheet.

After sharing your spreadsheet with others and granting them permission to make edits, you will notice that Google Sheets tracks all revisions, therefore you can easily view which collaborator made each change. Data Validation will keep you one step ahead and ensure that these revisions meet your expectations, based on the validation criteria you’ve applied to certain areas of your spreadsheet.

Validation criteria can consist of the following:

• A list of items or list from a named range
• Number (Between, Not Between, Less Than, Less Than or Equal to, Greater Than, Greater Than or Equal to, Equal to, Not Equal to)
• Text (Contains, Does Not Contain, Equals, is Valid URL, is Valid Email)
• Date (is Valid Date, Equal to, Before, On or Before, After, On or After, Between, Not Between)

Use Case

The Employee Contact List is shared between the Administrative Assistant in the Los Angeles office and the Administrative Assistant in the Philadelphia office location. In order to maintain accuracy as both assistants update and add to the spreadsheet, we will use the Data Validation tool in Columns B, C and E shown in the example below.

We will avoid erroneous data in these columns, and therefore the following criteria must be met for entry:

• Values in Column C must be HR, IT or Sales
• Values in Column B must be a valid date
• Values in Column E must be a valid email address

Our first area of focus are the cells in Column C, where we would like to create a drop-down list of items that are available for entry. Let’s review how to give range C2:C15 the name “DepartmentList” in order to set our data validation criteria as “List from a Range,” and that range being DepartmentList.

How to Name a Range of Data

The following are steps for Naming a Range in Google Sheets:

1. Highlight the range of data in which you would like to name (In the example below, we’ve selected range C2:C15).
   
   

2. Select Data from the menu.
3. Choose Named and Protected Ranges from the drop-down list.

4. Refer to the Named and Protected Ranges window that is now displayed on the right side of the screen.
5. Add a name (no spaces) for the selected range in the first field provided, and verify that your selection is accurately displayed in the second field. In the example below, “DepartmentList” is the name provided for the C2:C15 range of data, which is located on the worksheet titled “Named Ranges.”
6. Click Done.
   
   

Now that we have named the range of data that we want to set as our data validation criteria, we are ready to restrict entries in Column C cells to three items: HR, Sales or IT.

How to Create a Drop-down Button in Cells to Show a List from a Named Range

The following are steps for using the Data Validation tool in Google Sheets:

1. Highlight the range of cells in which you would like to add Data Validation. In the example below, Column C was selected by clicking on the column letter displayed at the top of the spreadsheet.
2. Select Data from the menu items.
3. Choose Validation from the drop-down list.

4. Cell Range: In the Data Validation window shown below, verify that your selected cell range is displayed in the Cell Range field.
5. Criteria: Select your validation criteria from the options available: List from a Range, List of Items, Number, Text or Date. In the example, we’ve chosen List from a Range and the input options will be those items from the Named Range, DepartmentList.
   
   

6. On Invalid Data: Select Show Warning to display, “Invalid Cell Contents” next to the cell when invalid data is entered. In the example below, you can see that after choosing to show a warning on invalid data, the warning displays next to the cell within Column C when invalid cell content (“No”) is entered.

On Invalid Data (continued): The more stringent option is to Reject Input, therefore only the specified validation criteria may be added in the range. In the example below, after choosing to reject input of invalid data, we are restricted to input only HR, IT or Sales (DepartmentList range items) into Column C cells.

7. Appearance: We’ve chosen to display an in-cell arrow button to show the list of items available for input. We’ve also selected the Show Help box to display help text when hovering over a cell. The help text indicated below is “Select Dept. from drop-down list.”

8. Click Save to apply.

How to Restrict Input to Valid Dates

We’ve repeated the Data Validation steps for Column B, where the employees’ start dates are listed.

This time, as you can see below, our criteria is set to Date and the input must be a valid date. We have chosen to reject input on invalid data in Column B.

Now, values entered into Column B must be a valid date. We selected the Reject Input button, and therefore when we attempt to enter an invalid date, the input is rejected and the default Help Text, “Enter a valid date” is displayed next to the cell.

How to Restrict Input to Valid Email Address Formats

We have repeated the Data Validation steps once again for Column E, where our employees’ email addresses are listed. This time, as you can see below, our criteria is set to Text and the input must be a valid email address (text containing @). We have also chosen to Reject Input on invalid data.

Now, all text entered into Column E must be a valid email address. Since we selected the Reject Input button, when we attempt to enter invalid data, the input is rejected and the default Help Text, “Enter a valid email” is displayed next to the cell.

Finally, Data Validation has given the Administrative Assistants assurance that only specific criteria may be added to Columns B, C and E of the Employee Contact List. Both assistants have the ability to view, change or add validation rules as needed.

In this final post of our Google Sheets series, we’ve covered how Data Validation will help maintain continuity as spreadsheets are shared with others. We encourage you to save time and increase productivity by taking advantage of sharing and collaborating with others in Google Spreadsheets.

If you have any questions about Google Spreadsheets, we invite you to chat with one of our experts during Blog Office Hours, every Thursday from 11-12 EST. Additionally, if you would like to schedule a Google Spreadsheets training for your organization, please visit the Training section of our website.