How to Edit Data Validation in Google Sheets (With Easy Steps)

Suppose you have applied data validation to create a dropdown list in Google Sheets for a list of fruits. Now you need to change the dropdown list to show a list of vegetables. Then how will you do it? Well, fortunately, you can edit the validation criteria as required to do that. In this article, we will show you how to edit data validation in Google Sheets.


A Sample of Practice Spreadsheet


What Is Data Validation in Google Sheets?

The Data validation feature in Google Sheets is used to specify some exclusive inputs for a particular range of cells. You can apply Data validation to allow only valid inputs in the applied range. This feature allows setting several criteria to validate data. You can create a dropdown list to specify the options in the list as the only valid inputs. You can also set conditions with Number, Text, Date, Custom formula, etc. for valid data inputs. Data validation is a great way to ensure the accuracy of inputs of data.


Step-by-Step Procedure to Edit Data Validation in Google Sheets

Consider the following dataset containing Fruits and Vegetables in columns B and C as shown below. We have applied data validation to create a drop-down list in cell E5 showing the names of the fruits. Assume you need to edit the data validation in that cell to change the contents of the drop-down list and show the names of the vegetables instead.

how to edit data validation in google sheets

Follow the step-by-step procedure to be able to do that.


Step 1: Select Data Validation Range

  • First, you need to select the range where the data validation is applied. So, select cell E5.

Select Data Validation Range in google sheets


Step 2: Open Data Validation Window

  • Then, go to Data >> Data validation from the ribbon as shown below.

Open Data Validation Window in Google Sheets

  • After that, the Data validation window will pop up as follows. Here, you can see all the criteria applied to create the dropdown list.

Read More: How to Use Data Validation in Google Sheets from Another Sheet


Step 3: Change Validation Criteria

  • The validation Criteria are set to List from a range. You can change the criteria to List of items, Number, Text, Date, Custom formula is, and Checkbox.

Change Validation Criteria in Google Sheets

  • If you change the criteria to List of items, then you will need to manually input the names of the Vegetables and separate them by commas.


Similar Readings


Step 4: Update Criteria Range

  • Here, we want to keep the criteria as before but change the criteria range. So click on the Select data range icon at the right of the criteria range box.

Update Criteria Range in Google Sheets

  • Then select the range C5:C8 and click on the OK button in the Select a data range dialog box.


Step 5: Choose Action for Invalid Data Entry

  • Next, check the Show dropdown list in cell checkbox so that you can see the list as a dropdown when clicking the dropdown arrow in the cell.
  • Then mark the radio button for Reject input so that no invalid input can be entered.


Step 6: Update Data Validation

  • Finally, click on the Save button to complete the editing. You can also click on Remove validation to clear the data validation.

Update Data Validation in Google Sheets

  • As a result, the dropdown list will change to the following.

Read More: How to Remove Data Validation in Google Sheets


How to Update Cell Values after Editing Data Validation in Google Sheets

Now consider the scenario of changing an item in the List of range for the dropdown list. If the earlier item is selected in the dropdown list, it will throw an error due to a mismatch in the source range and the validation range. You can manually update the input from the dropdown to fix the error. But this will become a very tiresome task for a large dataset.

Suppose you have copied the dropdown list in cell E5 to the cells below.

How to Update Cell Values after Editing Data Validation in Google Sheets

Now if you change “Apple” in the Fruits column to “Apples”, then cells E5 and E7 will throw an error.

Follow the steps below to update those cells with “Apples” at once as it will be very useful for larger datasets.

📌 Steps:

  • First, select the range containing the dropdown lists. Here the range is E5:E8.

  • Then press Ctrl+H and the “Find and Replace” window will pop up. You can also do that from the Edit tab in the ribbon.
  • Next, enter Apple in the Find box and Apples in the Replaced with box.
  • After that, click on Replace all and Done.

  • At last, the validation cells will be updated as follows.

Read More: Updating Cell Values Based on Selection in Drop Down List in Google Spreadsheet


Things to Remember

  • Make sure you have entered the range or formula properly in the Enter a range or formula box.
  • If you choose “List of items” as the criteria, you need to put a comma after each item.

Conclusion

In this article, we have demonstrated how to edit data validation in Google Sheets. We also highlighted the process of updating cell values based on the selection in the dropdown list. Now you can apply the steps whenever you need to edit data validation in your own dataset. If you have any queries or suggestions, please let us know in the comment section. You may also visit our OfficeWheel blog to explore more Google Sheets-related articles.


Related Articles

Afrina

Afrina

This is Afrina Nafisa. Currently working as a Technical Writer at OFFICEWHEEL.COM. I have done my graduation from Ahsanullah University of Science and Technology. As I am eager to learn more and more currently my motive is to make myself better every day with my work so that I can make better content for all the readers.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo