> For the complete documentation index, see [llms.txt](https://docs.coherent.global/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.coherent.global/build-spark-services/set-data-validations.md).

# How to: Set data validations

Excel has the ability to limit input values to a cell based on [Data Validation](https://support.microsoft.com/en-us/office/apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249).

Spark is able to read these Data Validations that are defined for `Xinput` in an Excel workbook (see [How to: Map inputs and outputs](/build-spark-services/map-inputs-and-outputs.md#map-a-single-multiple-cell-range-to-an-input-output)).

Excel Data Validation present in the cells will be used to:

* Detect errors in the API request inputs in the [API Tester](/navigation/api-tester.md) and [Execute API (v3)](/spark-apis/execute-api/execute-api-v3.md) .
* Generate test cases in the [Testing Center](/navigation/testing-center.md).
* Return validation parameters in the [Validation API](/spark-apis/validation-api.md).

## Add Data Validation in Excel

1. Select the cell(s) to which the Data Validation should apply.
2. In the Ribbon, select the *Data* tab and click **Data Validation**.
3. In the resulting screen, define the allowable inputs into the cell.
4. If the *Validation criteria* are linked to other cells, it is strongly suggested to use absolute cell references, e.g. `$A$1`.
   * Absolute references enable any dynamic dependent validation to be picked up by the [Validation API](/spark-apis/validation-api.md) and the [Testing Center](/navigation/testing-center.md) test case generation.
   * An example of dynamic dependent validation would be the choice of valid city and state combinations.

<figure><img src="/files/2EPfXC9jXzL2uPyjZljO" alt=""><figcaption></figcaption></figure>

5. Spark can also return the customized messages that are entered in the *Error message* field under the *Error alert* tab. See a demonstration of this step [here](https://www.youtube.com/watch?v=Ik-jDFo_6D8).
6. Click **OK** to set the Data Validation.
7. Data Validations applied to `Xinput` will be automatically picked up by Spark
8. If an API request is made where the parameters do not comply with the Data Validation, Spark will return an error in the API response [Execute API (v3)](/spark-apis/execute-api/execute-api-v3.md#response_data), which can be seen in the [API Tester](/navigation/api-tester.md) *Error logs*.

{% hint style="warning" %}
Note that Spark supports these validation criteria only:

* *Whole number* (min and max)
* *Decimal* (min and max)
* *List*
* *Date* (min and max)
* *Text length* (max)
  {% endhint %}

## Use complex formulas to define an error message

Syntax: `Xvalidate_<parameter>`

Spark allows users to define custom error messages based on the value of a mapped field.

1. Define the formula for `Xvalidate`.
   1. Typically `Xvalidate` is used with an `=IF` function, where the result of a formula is used to determine whether or not a message is returned.
   2. For example in this formula, `=IF(A3 < 1000, "Insufficient value", "")`.
      1. Depending on the value of cell `A3,`
      2. If the value is less than `1000`, return the error message `Insufficient Value`.
      3. Otherwise `""` means not to return an error.
2. Follow the instructions to create a Name in [How to: Map inputs and outputs](/build-spark-services/map-inputs-and-outputs.md#define-a-named-range-in-excel) for the selected range.
3. When creating the Name, use the noted prefix defined in the syntax.

## Example of Xvalidate

{% file src="/files/3OhBwjPADNHVYWZFF3x2" %}

## Prerequisites for dynamic validation

The [API Tester](/navigation/api-tester.md) and [Validation API](/spark-apis/validation-api.md) can pick up validations where a validation depends on the formulas of other cells, e.g. city and state combinations. However there a number of requirements are needed for this to work:

* This applies to single cell `Xinput`.
* Data Validations that pertain to Dynamic Validation must be defined using absolute cell references, e.g. `$A$1`.
* For List data validation, Dynamic array functions such as `FILTER`() are not supported. It is recommended to use `OFFSET()` and `MATCH()` functions instead. An example is included in [Validation API](/spark-apis/validation-api.md#sample-api).
* Dynamic validations are only evaluated to `1` level of evaluation for performance considerations. In the Data Validation settings, when defining the formula for the dynamic validation rules (such as the *List, Source*), if the referenced ranges also contain formulas, then the dynamic validation will fail. For example, if the formula in *Source* included `$B$1:$B$2` but the cell `$B$1` also includes a formula instead of just text or a value, then the dynamic validation will fail.

<figure><img src="/files/7EGPrWNtv9bhJ6ZVDjJf" alt=""><figcaption></figcaption></figure>