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).
* ”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).
Data Validation is useful when:
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.
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”).
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.
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.
Learn how to do this step-by-step in the video below 👇