# 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>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.coherent.global/build-spark-services/set-data-validations.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.