To begin, open the spreadsheet that contains the cells you want to apply data validation to. In this case, let’s assume the cells containing the actual amounts are in the range A2:A100. First, we need to determine the list of valid values that can be entered into these cells. Some examples for an actual amount could include:
A fixed list of currency values like “$10”, “$20”, “$30” etc.
A range of currency values using a formula like “=ROUND(RAND()*100,2)” to generate random numbers between $0-$100 with two decimal places
A drop down list linked to other cells containing pre-set amounts
Calculated amounts based on other cell values or formulas
It’s important to define the valid values upfront so we can reference this list later for the data validation. In this example, let’s say we want to allow any dollar amount between $0.01-$100.00 with two decimal places of precision.
To define this list, go to an empty cell (let’s use B1) and enter the following formula:
=”$”&TEXT(ROW(B1)/100, “#,##0.00”)
This will generate a dynamic list of currency values incrementing by $0.01 in cell B1 downwards. We can now select the range B1:B10000 (or however many values we need) to define our list of valid values for the data validation.
Now go to the Data tab and select Data Validation from theData Tools section. In the popped out dialog box, set the following parameters:
Allow: Number
Data: Equal to
Minimum: =$0.01
Maximum: =$100
For the list, check the “In cell drop down” box and set the source equal to the named range containing our list (B1:B10000 in this case).
At this point you have defined the data validation criteria. All that’s left now is to define where this will apply. For the actual amount cells, select the range A2:A100. Then in the Data Validation dialog box, click the drop down next to “Apply to” and choose “A2:A100”.
Finally click OK to apply. Now when users enter or edit the actual amount cells, a drop down list of valid values between $0.01-$100 will appear to choose from, preventing any invalid entries.
You may also want to consider other optional validation settings like input and error messages. For example, under the Settings tab you could set a title like “Actual Amount” and an input message “Select a valid amount from the dropdown”. And for the error alert, enter something like “Amount must be between $0.01 and $100”.
This provides a more customized and user-friendly experience when validating data entry. You can also highlight invalid cells in red to visually draw attention to issues.
The process of data validation is an integral part of maintaining data quality and integrity in spreadsheets. By taking the time upfront to properly define validation rules and criteria, it prevents errors from being entered in the first place. And defining validation at the cell/range level makes it scalable for any number of records without much ongoing maintenance required.
Using data validation is an effective way to control user input and surface errors before they are saved permanently into the spreadsheet. With some built-in validation rules and formatting options, spreadsheets can achieve many of the same safeguards as traditional database and form interfaces for collecting and managing structured data. Proper use of data validation takes planning but pays dividends in cleaner, more reliable information over the long run.
Andrew Stroud