Arrow left
Back to product guides
Google Sheets Tips

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.

How to open a data validation window from the menu bar
How the data validation pop-up window looks like

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.

What “List from a range” data validation looks like

How to implement “List from a range” data validation

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

How to implement “List of items” data validation

(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”.

How to implement “Number” data validation

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

How to implement “Text” data validation

(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”

How to implement “Date” data validation

(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”).

What a drop-down list looks like in a consolidated P&L template

To learn more about LiveFlow, book a demo.

Automate any custom financial dashboard in Google Sheets with LiveFlow

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at:
Check Icon
Call us at +1 (415) 650-1711