Google Sheets is one of the popular free spreadsheet applications. Its web-based nature makes it easy to collaborate on a spreadsheet with several users. However, having several users work on a spreadsheet together may lead to data entry and formatting problems. One way to manage inputs into a spreadsheet is by using data validation rules to restrict the information you can enter into a cell.
With data validation, you can also provide instructions on what to enter into a cell. At the same time, it can also give others a warning or error message when they provide the wrong data in a cell. Here’s how to use data validation in Google Sheets.
How to Setup a Data Validation Rule in Google Sheets
With data validation, you can limit the type of data input you want in a cell. For example, you can limit a cell only to accept a number, date, fraction, etc. You can also create a drop-down using data validation. Here’s how to do so:
Step 1: Open Google Sheets in your preferred web browser.
Step 2: Create a new workbook and enter your dataset or open an existing workbook with the dataset.
Step 3: On the spreadsheet, select the cells to add data validation rules.
Step 4: Click the Data menu at the top.
Step 5: Select Data Validation to launch a sidebar on the right side of the sheet.
Step 6: From the sidebar, click on the Add rule button.
Step 7: In the ‘Apply to range’ field, confirm the cell range you would like to apply the data validation rule to.
Step 8: Click the drop-down beneath the Criteria field and select your preferred data limitation criteria from the following group:
- Dropdown
- Text
- Date
- Greater than, less than, equal to, or in between
- Checkbox
- Custom formula
- None
Step 9: Enter a value or formula to benchmark your criteria to.
Step 10: Click Done to save your changes.
How to Add Input Messages for Data Validation in Google Sheets
As you set up your data validation rule, you can include an input message. You can also include an input message after you set up the data validation rule. Input messages inform users of the type of input expected in a cell. Here’s how to add an input message in Google Sheets:
Step 1: On the spreadsheet, select the cell you want to include an input message.
Step 2: On the Google Sheets Ribbon, click the Data tab.
Step 3: Select Data Validation to launch a sidebar on the right side of the sheet. If you already have a data validation rule in your worksheet, you will see it listed on the sidebar.
Step 4: Select your existing data validation rule if you want to add an input message.
Step 5: Click Advanced options.
Step 6: Tick the box beside ‘Show help text for a selected cell.’
Step 7: Type out the text you want to show beside the cell.
Step 8: In the ‘If the data is invalid’ field, select Show a warning.
Step 9: Click Done to save your changes.
How to Reject Data Inputs Using Data Validation in Google Sheets
You can set up a data validation rule to reject the wrong inputs in a cell. Here’s how to do so:
Step 1: On a Google spreadsheet, select the cell you want to restrict the data input.
Step 2: Click the Data menu at the top.
Step 3: Select Data Validation to launch a sidebar on the right side of the sheet. If you already have a data validation rule in your worksheet, you should see it listed on the sidebar.
Step 4: Select your existing data validation rule if you would like to add an input message to it.
Step 5: Click Advanced options.
Step 6: Tick the box beside ‘Show help text for a selected cell.’
Step 7: Type the text you want to show beside the cell.
Step 8: In the ‘If the data is invalid’ field, select Reject the input.
Step 9: Click Done to save your changes.
How to Remove Data Validation Rules in Google Sheets
Here’s how you can remove the data validation rule from a cell in Google Sheets:
Step 1: On a Google spreadsheet, select the cell you want to remove the data validation rules from.
Step 2: Click the Data menu at the top.
Step 3: Select Data Validation to launch a sidebar on the right side of the sheet. If you already have a data validation rule in your worksheet, you will see it listed on the sidebar.
Step 4: Hover over the data validation rule to bring up the delete icon.
Step 5: Click the Delete icon to delete the validation rule. You can also click the Remove all button if you would like to delete all data validation rules in the spreadsheet.
Locking Cells in Google Sheets
Applying data validation rules to Google Sheets is just one of the ways you can manage data entry. However, if you would like to prevent data changes, then you can lock certain cells in the workbook.
Categories: How to
Source: thpttranhungdao.edu.vn/en/
