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.

Table of Contents

A Sample of Practice Spreadsheet

Edit Data Validation

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.

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.

Step 2: Open Data Validation Window

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

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

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.

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.

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.

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

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.

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.

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.