Excel’s data validation tool allows you to constrain a cell’s inputs to meet certain criteria. This can help prevent errors from occurring. In the example in the previous section, we can prevent the divide by zero error by forcing the diameter of the steel to always be less than the diameter of the concrete.
The worksheet contains the same problem as the previous section. Select the cell containing the diameter of the steel. In the Data Tools section of the Data Tab, click Data Validation.
The Settings tab of the Data Validation window will allow you to specify that this cell’s value is always less than the diameter of the concrete. Change the first drop-down menu to Decimal so decimal values will be accepted. Then, select less than in the second drop-down menu. Click within the Maximum box and then select the cell containing the diameter of the concrete (C7).
The other two tabs of the Data Validation window allow you to provide messages to the users. Switch to the Input Message tab. In the Input message box, type “Enter a value that is less than the diameter of the concrete.“
You can also display an error alert when a value that doesn’t meet the criteria is entered. Select the Error Alert tab and enter the message “The diameter of the steel must be less than the diameter of the concrete.“
Click OK.
Now, when the cell is selected, the input message appears:
If a user enters a value that is greater than the diameter of the concrete, an error message appears, preventing them from entering that value:
You can click Retry to enter a different value, or Cancel to undo that action.
However, it’s still possible to create an error because there are no constraints on the cell for the diameter of the concrete. If you enter a value for dconcrete that’s equal to the diameter of the steel, a #DIV/0! error results:
To prevent this from happening, we can add restrictions on the concrete diameter. Select the cell, then go to the Data tab > Data Validation. Again, set the first drop-down menu in the Settings tab to Decimal. In the second drop-down menu, select greater than. Click inside the Minimum box, then click cell C6.
Create an Input Message saying “Enter a value that is greater than the diameter of the steel.“
In the Error Alert tab, enter in the message “The diameter of the concrete must be greater than the diameter of the steel.“
Click OK.
These two data validations work together to prevent errors on the spreadsheet. They restrict any entries where the diameter of the steel will be greater than or equal to the diameter of the concrete.
Allowing Only Specific Values with Data Validation
Data validation can also be useful in situations where only certain values are acceptable. In this section, you’ll see an example where a lookup table is used, and an input cell needs to be restricted so that only values that are in the lookup table will be valid.
Worksheet 05e contains a calculation of a safety factor for an aluminum shaft undergoing a torsional load. To calculate the safety factor, we need to know the stress in the shaft as well as the yield strength of the material. The yield strength in cell C14 is found using a VLOOKUP function. The function searches a table for the alloy entered in cell C7 then returns the corresponding yield strength multiplied by a thousand. For example, when the material is set to 356, the VLOOKUP function searches the table on the right for alloy 356. Because the yield strength in the lookup table for alloy 356 is 22 ksi, the VLOOKUP function returns 22*1,000 psi for the yield strength in the calculations table.
The spreadsheet then calculates the safety factor from the yield strength and the actual stress in the shaft.
For the spreadsheet to work properly, the name of the material has to perfectly match the values in the column of the aluminum alloys. If a name is entered incorrectly, it produces a #N/A error in the VLOOKUP function (and, in turn, the safety factor).
We can constrain the input to the material cell so that it must match one of the alloys on the right using the Data Validation tool.
Select cell C7, then choose to Data Validation within the Data tab. In the Settings tab, click List in the first drop-down menu. You could manually enter the allowable values, separated by commas, into the Source box to create that list. However, since the list is already in the spreadsheet, you can simply select those cells. Do so, then click OK.
Now, when you select the material cell, a drop-down menu icon will appear beside the cell. When a user clicks this icon, a list of values from the lookup table will appear:
The user can either choose a value from that list or type a value in as they normally would. However, if their entry does not match any alloy on the list, they will get the default error message: “This value doesn’t match the data validation restrictions defined for this cell.”
