How to Use Data Validation in Google Sheets

In this article, you can get yourself familiarized with the Data Validation function in Google Sheets. This function is beneficial to avoid breaking formulas down and limiting irregular information input (especially when a sheet is shared with many people).

‍

How to do data validation in Google Sheets

1. Go to the “Data” tab, click “Data Validation”, and you get a pop-up window.
2. Select a range where you want to make Data Validation effective.
3. Choose one of the criteria.
4. Check or uncheck “Show drop-down list in a cell”.
5. Choose one of two options* against invalid data input.
6. If you want to leave a hint to an editor who makes an invalid data input to make it valid, check the box at “Show validation help text:” and enter a message.
7. Click “Save” to make the setting effective.

‍

* ”Show warning”: This option allows an editor to input invalid data in a cell in the selected range but gives him/her a warning message that his/her input breached the defined input rule.

* “Reject Input”: This option refuses any invalid input.

‍

Next, we will explain the types of data validation available in Google Sheets.

‍

(i) List from a range: With this option, data input is limited to words, dates, or numbers in the selected range of cells.

‍

‍

‍(ii) List of items: You can directly enter the input in the text box on the pop-up window by punctuating them with commas.

‍

‍(iii) Number: If you choose this, the input should be a number. You can also add a condition such as “between X and Y”, and “greater than Z”.

‍

(iv) Text: By selecting this option, you can limit the data input to text with conditions, including email address or URL.

‍

(v) Date: If this is effective, the data input should be a date. You can add a similar condition as you can do for “(iii) Number”

‍

(vi) Custom formula is: This option allows you to insert a formula to pull data from somewhere else.

‍

(vii) Check box: You can check or uncheck a box in a cell if you choose this option. You can enter a cell value corresponding to a checked box and another cell value for an unchecked box. (e.g., TRUE for a checked box and FALSE for an unchecked box).

‍

When would you use Data Validation in Google Sheets?

Data Validation is useful when:

1. You want to keep data input consistent from the beginning to the end.
2. You would like to limit data input (e.g., specific range of numbers: 1-10) or data style.
3. You need to create a drop-down list.

By leveraging the Data Validation function, you can make your analysis more efficient and valid without overlooking irregular input or revising your model or formulas to adjust to new input types. 

‍

How do I create a dependent drop-down list in Google Sheets?

To learn how to make a dependent drop-down list, refer to this article. [A link to be inserted once the report is ready]

‍

Finally, here is an example of a drop-down. This is LiveFlow’s Consolidated P&L Template For Excel & Google Sheets. You can see a drop-down list, with which you can switch monthly financial data, in the middle of the picture (the cell next to the text “Choose Month”).

‍

‍

‍

To learn more about LiveFlow, book a demo.

‍

‍