---
title: "Read Excel data validation rules in C#"
url: https://recursiveai.org/onedocument/cookbook/inspect-excel-validation-rules/
description: "Read Excel data validation rules in C# with OneDocument for .NET 10. Download a runnable example with sample inputs and see its actual output."
version: "1.0.0"
---

# Read Excel data validation rules in C#

**OneDocument** is a free, open-source Word, Excel and PowerPoint document library for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneDocument.Spreadsheets) · [GitHub](https://github.com/recursive-ai-oss/onedocument-csharp) · [Docs](https://recursiveai.org/onedocument/docs/) · [Download example (.zip)](https://recursiveai.org/onedocument/cookbook/inspect-excel-validation-rules/inspect-excel-validation-rules.zip)

Read Excel’s drop-down and numeric rules, including x14 extensions. Reading rules does not validate cell assignments.

```shell
dotnet add package RecursiveAI.OneDocument.Spreadsheets
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
using RecursiveAI.OneDocument.Spreadsheets;
using var book =
    Workbook.Open("validation.xlsx");
foreach (var rule in
    book.Sheets[0].Validations)
    Console.WriteLine(
        $"{rule.Type}: {rule.Formula1}");
```

validation.xlsx is in the example. The snippet prints:

```text
List: "Yes,No"
WholeNumber: 1
List: Choices!$A$1:$A$2
```

Unzip [the example](https://recursiveai.org/onedocument/cookbook/inspect-excel-validation-rules/inspect-excel-validation-rules.zip), then `dotnet run`.

## The full program

The self-made fixture has an inline Yes/No list on A2:A5, whole numbers between 1 and 10 on B2:B5, and an x14 list on C2:C5 pointing to Choices!A1:A2. Those source cells contain Red and Blue. The numeric rule uses Warning error style; the list rules use Stop.

```csharp
using RecursiveAI.OneDocument.Spreadsheets;

using (var book = Workbook.Open("validation.xlsx"))
{
    foreach (var rule in book.Sheets[0].Validations)
    {
        Console.WriteLine($"{string.Join(" ", rule.Ranges)}: {rule.Type}, x14={rule.IsExtended}");
        Console.WriteLine($"  {rule.Operator}: {rule.Formula1} | {rule.Formula2}");
        Console.WriteLine($"  {rule.ErrorStyle}; blank={rule.AllowBlank}; dropdown={rule.InCellDropDown}");
    }
    book.SaveAs("preserved.xlsx");
}
using var reopened = Workbook.Open("preserved.xlsx");
Console.WriteLine($"Reopened: {reopened.Sheets[0].Validations.Count} rules");
```

```text
A2:A5: List, x14=False
  between: "Yes,No" | 
  Stop; blank=True; dropdown=True
B2:B5: WholeNumber, x14=False
  between: 1 | 10
  Warning; blank=True; dropdown=True
C2:C5: List, x14=True
  between: Choices!$A$1:$A$2 | 
  Stop; blank=True; dropdown=True
Reopened: 3 rules
```

Opening a nonexistent input raises `FileNotFoundException`; use the supplied fixture or an existing XLSX path.

## Variations

**Read bounds and the error style.** Run this against validation.xlsx from the zip:

```csharp
using RecursiveAI.OneDocument.Spreadsheets;
using var book =
    Workbook.Open("validation.xlsx");
var rule = book.Sheets[0].Validations
    .Single(v => v.Type == DataValidationType.WholeNumber);
Console.WriteLine($"{rule.Formula1} to {rule.Formula2}: {rule.ErrorStyle}");
```

```text
1 to 10: Warning
```

## Related recipes

- [Read an Excel file](https://recursiveai.org/onedocument/cookbook/read-excel-file/)
- [Protect an Excel worksheet](https://recursiveai.org/onedocument/cookbook/protect-excel-worksheet/)
- [Create an Excel structured table](https://recursiveai.org/onedocument/cookbook/create-excel-table/)

## The short answer

Open the workbook and enumerate `Worksheet.Validations`. Each item exposes its target ranges, type, operator, both formulas, error style, blank handling and messages. `IsExtended` distinguishes an x14 extension rule from a standard rule. The collection includes both, so a caller does not need to walk the worksheet’s extension markup separately.

The [validation documentation](https://recursiveai.org/onedocument/docs/spreadsheets-styles/#data-validation) describes the supported read properties and authoring operations. Formulas are returned as text: a list formula that points to cells is not itself the list of allowed values.

## How do you view all data validation rules in Excel?

For programmatic inspection, enumerate the validations on every worksheet you want to inspect. The snippet at the top selects the first worksheet; the full program prints all rules on that sheet. A rule may apply to more than one range, so inspect the complete `Ranges` collection rather than assuming one cell or rectangle per rule.

The fixture deliberately includes an extended rule. Its `Formula1` is `Choices!$A$1:$A$2`, while its target range is C2:C5. These mean different things: the target is where the rule applies, and the formula identifies its source list. Looking only at the target cells would not tell you which choices appear in the list.

Inspect `Type` before interpreting the formula strings. For the inline list, the first formula is the quoted text `"Yes,No"`. For the whole-number rule, it is the lower bound, and `Formula2` holds the upper bound. Other operators can use those fields differently. The full output prints the operator alongside the formulas so the numeric limits are not mistaken for two unrelated expressions.

## How do you read data from Excel in C#?

Reading a cell value and reading its validation are separate operations. Use the worksheet’s cell accessors for stored cell values and the `Validations` collection for rules. The x14 rule in this fixture returns a reference to the Choices worksheet; it does not resolve the reference into Red and Blue for you. If your application needs the actual list items, read the referenced cells as a separate step after identifying the source reference.

Do not treat the presence of a rule as evidence that every existing cell satisfies it. The example reports metadata and preserves it on save; it does not audit the workbook’s values or execute Excel’s input UI. An application that accepts spreadsheet data must decide its own validation policy, including how to handle blank cells, warning-style rules and formula-based conditions.

`InCellDropDown` exposes the positive sense of the setting even though the file stores the inverse `showDropDown` flag. Error style also matters: Stop, Warning and Information are different stored policies, not interchangeable error labels. Inspect error and prompt messages when reporting the rule to a user.

## How do you copy data validation rules in Excel?

If the goal is to save the same workbook without losing its rules, open it and call `SaveAs`, as the full program does. Reopening preserved.xlsx shows all three rules, including the x14 rule. This is preservation, not a cross-workbook copy operation.

When changing a rule, the documented operation is to remove the existing validation and add a new one with `DataValidationSpec`. The authoring helpers include inline lists, range lists, whole-number and decimal bounds, and custom formulas. Adding checks Excel’s documented field lengths as implemented by the library: titles are limited to 32 characters, messages to 255, and inline lists to 255. Keep the source reference and target ranges distinct when rebuilding a rule; copying just its visible drop-down text would discard the original relationship to the source cells.
