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

‍

‍

What are the other advanced tips in Google Sheets?

If you don’t get the solution to your question in this article or you are interested in learning the other advanced tips in Google Sheets, you may find the answers in the following articles.

‍

Check the following articles to use Google Sheets more efficiently.

Named Range in Google Sheets: Explained

How to Find and Replace Words in Google Sheets

How to Enable Excel Shortcuts in Google Sheets

Relative Reference and Absolute Reference in Google Sheets: Explained

‍

Read the articles below to organize your data in Google Sheets.

How to Remove Duplicates in Google Sheets

Pivot Table in Google Sheets: Explained

‍

Go to the following pots to learn how to create a pull-down menu or limit data input in a cell.

Drop Down List in Google Sheets: Explained

Dependent Drop-down List in Google Sheets: Explained

‍

Do you want to learn how to use Google Data Studio? Navigate to the article below.

What is Google Data Studio?

‍

Analyze your live financial data in a snap

Are you learning this tip to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows and update numbers in real-time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. You can also customize these templates as you want without breaking the automated data inflow.

To learn more about LiveFlow, book a demo.