Data Validation

Follow these instructions to use Excel’s data validation features in online spreadsheets and web forms generated by SpreadsheetConverter. 

Excel provides a number of built-in data validation features that makes it easier for you to ensure that electronic forms only contain valid information. SpreadsheetConverter supports most of these rules, and will enforce these rules also in the converted web page. 

Using Data Validation in the spreadsheet

To validate input data in a spreadsheet, Excel allows you define rules for each cell in the Data > Data tools > Data Validation section of the Excel menu. For more information about the Data Validation feature in Excel, please study Excel’s Help pages.

You can assign unique rules for each cell in the spreadsheet. The validation criteria supported by SpreadsheetConverter are:

  • Whole number
    The value in the cell is an integer. You can limit the allowed range of integer values, e.g. 1-5.
  • Decimal
    The value in the cell is numeric. You can limit the range of numeric values, e.g. 3.1415-3.1416.
  • List
    SpreadsheetConverter converts this to a dropdown menu with static choices. The Source field must contain a static cell range; you cannot use functions like IFINDEXINDIRECT, MATCH or OFFSET here. The cells in the designated cell range can only contain static text. If you need to use dynamic text for the choices in a dropdown menu you should use the Dynamic Dropdown widget.
  • Date
    The value in the cell is an Excel serial number; a timestamp. You can limit the allowed range for the date portion of this timestamp. Some examples include conditions like invoice date must be a past date,  event date must be a future date, delivery date can’t be on weekends etc.
  • Time
    The value in the cell is an Excel serial number; a timestamp. You can limit the allowed range for the time portion of this timestamp.
  • Text length
    The number of characters in the cell must conform to these rules, e.g. the cell must contain at least eight characters.
  • Custom
    The value of the cell is evaluated using an Excel formula. The formula can test for almost anything, e.g. that a date refers to a Monday or that there is a number in the cell that is a multiple of 12 or total expenses must be under the budget etc. It can be used to avoid duplicate entries or validate product code or special format too.

Ignore blank setting

The Ignore blank checkbox skips the validation rules for empty fields. Tick this checkbox for fields that should be validated only if they contain something.

Specifying Error message

You can specify what message to display if the field contents are invalid. You can do so from the Error Alert tab of the Data Validation dialog box. Enter your message in the Error Message box. The Title box is not supported.

In the Style dropdown, choose the Stop style if you want to prevent the form from being submitted if the field contents are invalid. The error message will be in a red arrow pointing to the invalid field. Choose the Warning style to have the message appear in a yellow arrow or the Information style for the message to appear in a blue arrow. Always tick the check box Show error alert after invalid data is entered.

The Input Message tab is not supported. Add text near the cell if you need to tell users what kind of data they are expected to provide.

Validating an e-mail address

One of the most common and also most complex data validations is to verify that e-mail addresses are correctly written, e.g. that there is only one @ sign, that there is text on both sides of each period etc. The syntax rules for an e-mail address are far too complex to be handled with Excel Data Validation or Excel formulas.

Instead, use the Text > E-mail widget which is a standard text field with built-in validation of e-mail addresses. The field must either be empty or contain a valid e-mail address. If the e-mail address has an incorrect syntax, or if the field is empty but set as Required, the field is considered in error and the form cannot be submitted. This reduces the risk that you receive forms with missing or incorrect e-mail addresses.

The widget only validates the character string in the field; there is no live or online validation that the provided e-mail address actually exists or that someone reads the e-mails sent to it.

Known issues

If you are validating a date, and you enter the date directly into the Start Date or End Date fields, you must use the system date format specified in the regional settings control panel. If you would prefer to use a different date format, please place the date in a separate cell on the worksheet and give it any date format you like. Then insert a cell reference in the Start Date or End Date fields, i.e. if you’ve placed the date in cell B10 then type =B10 into the Date Validation window. To hide the date so that it doesn’t appear in the converted web page, use the Utility widget or place the date on any hidden row, column or worksheet.