If you want to control information input to particular cells to make sure the data entered by users is accurate and consistent, you need data validation.

The data validation window is located under Data >> Data validation. You can also access this feature by right-clicking and choosing the Data Validation option at the bottom of the contextual menu.

Select cells for data validation

There are two ways to select cells range to have a validation feature. The first way is to select cells to which you want to apply validation and then choosing the data validation option.

If you didn't do it, you can change it inside the cell range text box either by typing a range or clicking the Select data range button and dragging cells.

Data validation criteria

There are seven criteria you can use to control the user input.

List from a range

With these criteria, you can create a dropdown list from cells selected cells. Take a look at the following example:

We are going to create a dropdown list in cell C2, from values inside the range of A1:A3.

First, click the cell C2 so it will be already selected.

Right-click and choose Data validation.

Click Select data range button, next to Criteria, and select three cells with data.

Make sure that the Show drop-down list in cell is selected.

Next to On invalid data, choose Reject input because we don't want users to enter any other data.

This is how the Data validation window should look like:

Click Save.

Now, inside cell C2 there is a drop-down menu where you can choose one of three options.

If you try to enter anything else by typing inside the cell, Google Sheets is going to display the following message:

If you click OK, the message will disappear and the cursor will be moved down and inside cell C2 there will be the last correct value (or space).

If you remove or change an item from the range, it will automatically change values inside the dropdown list.

List of items

If you have a few choices, or you just don't want to have them inside a worksheet, you can use the second option, called List of items.

Instead of selecting a range, you can enter them directly into a text box.

The result will be the same as before, but now we don't need to store them inside our sheet. The next good thing is that we don't have to protect them from being removed from the sheet.

Number

The next criterion is pretty straightforward. You can choose a number as a validation rule. Apart from that, there is an additional option, where you can choose the number to be bigger, lower, equal, not equal, or be inside a certain range.

Text

The next option is the text. You can check whether the text contains, doesn't contain, or is equal to a particular string.

The last two options are very interesting if you are working with emails and URLs. You don't have to write your regular expression (RegEx) because they are already built into Sheets.

Date

Here, you can force a user to give input for a date inside a given range. You can also check whether the entered is correct by using the is a valid date option.

Custom formula

If none of the above options is good for you, you can write your formula.

If you look at Number criteria, there isn't any option to choose odd numbers.

But you can do this using the custom formula. Choose the Custom formula is and enter the ISODD function.

If you try to add a number that is not odd, an error message will appear. It will also appear if you try to type a string. Apart from odd numbers, the only vale that you can enter is a space.

Tick Box

The last type of validation is a tick box. It replaces all selected cells with checkboxes.

The selected checkbox is TRUE and unselected are FALSE. You can change it to different values. Sometimes TRUE is represented by 1, and FALSE by 0.

Validation of data already in cells

What happens if we enter validation to cells that already contain values that are not correct and we set the reject input option?

Let's find out. We are going to use this example:

Choose validation criteria as a number that is lower than 1000.

The data that is not correct will be marked with a little red triangle in the upper-right corner. If you hover over it, it will display a message informing you about invalid data.

Two things may seem odd if you look at the results.

First, cell A4 is incorrect. This happens because the value is a number written as text, and it's aligned to the left by default. You can force Google Sheets to treat any kind of value, or even formulas as text by placing an apostrophe (') in front of the data.

There is another strange thing. Why is the date a correct result?

Dates in Sheets are numbers formatted to dates. It's the number of days since 01/0/1/1900. If you click a cell with a date and move to Format >> Number >> Number you are going to get 561.00. After you remove decimal places, it's 561 days since 1/1/1900, and this number is lower than 1000, that's why the validation treats this number as a correct number.

Change the default error message

If you want to change the default message, you can check the Show validation help text option and enter your own.

If you want to get back to the default message, click the reset button on the right side of the textbox.

Remove data validation

The last step is to remove data validation from a worksheet. The process is very similar to adding validation – select cells from which you want to remove validation, right-click, and select Validation options.

This time, instead of clicking Save, click Remove validation.

Post Views: 108