Data Validation in Excel Using C#

Data validation in Excel ensures that users input only valid data in worksheets. In designing forms, collecting data, or building financial models, data validations help maintain structure and minimize user errors. In this post, we will show you how to apply data validation in Excel using C# programmatically.

This article covers the following topics:

C# Excel Data Validation Library

Data validation ensures that users enter valid and expected input in Excel spreadsheets. We will use the Aspose.Cells for .NET API to add different types of data validation in Excel. It provides a powerful and flexible approach to define rules such as dropdown lists, numeric ranges, date limits, and custom formulas, all without requiring Microsoft Excel.

Install the Aspose.Cells for .NET via NuGet package in your project:

PM> Install-Package Aspose.Cells

Then import the relevant namespace:

using Aspose.Cells;

Create Dropdown List Validation in Excel using C#

You can restrict user input to a predefined set of options by creating a dropdown list validation in Excel. This ensures consistent values, especially useful in forms or templates.

Follow these steps to create a dropdown list in Excel using C#:

  1. Create a new Workbook object.
  2. Access the first worksheet using its index.
  3. Define the target CellArea for validation.
  4. Access the worksheet’s Validations collection.
  5. Add a new validation using the Add() method.
  6. Set the validation type to ValidationType.List.
  7. Define the dropdown options using the Formula1 property (comma-separated).
  8. Add the cell area to the validation using the AddArea() method.
  9. Save the workbook to generate the validated Excel file.

Here’s the C# code example that implements these steps:

Creating a Dropdown List Validation in Excel Using C#

Creating a Dropdown List Validation in Excel Using C#

Restrict to Whole Numbers in Excel using C#

Whole number validation ensures users enter only valid integer values in Excel, which is ideal for quantity fields, age inputs, or budget sheets where decimals or text aren’t acceptable.

Let’s say you want to restrict a cell to accept only numbers between 1 and 100. Without this rule, users could accidentally enter invalid inputs like 150 or abc, causing calculation errors or logic issues in your spreadsheet.

With Aspose.Cells for .NET, you can enforce this validation rule programmatically in C# without needing to configure it manually in Excel.

The following code snippet shows how to restrict users to input only the allowed values:

Restrict to Whole Numbers in Excel using C#

Restrict to Whole Numbers in Excel using C#

Apply Date Range Validation in Excel using C#

Date validation helps you ensure users only enter valid dates. It is useful in planning tools, attendance records, booking sheets, and any scenario where you need dates within a specific range.

For example, imagine you’re building a project scheduling template, and you want to restrict users to enter dates only within the year 2024. Allowing users to enter a date outside this range (like 2023 or 2025) could break formulas or create inconsistent records.

Aspose.Cells for .NET makes it easy to apply date validations to specific cells, so users can only input dates that meet your criteria.

The following code snippet demonstrates how to ensure that users can enter only dates between January 1, 2024, and December 31, 2024. Anything outside the range will trigger an error, helping you maintain cleaner, more accurate data across your spreadsheet.

Use Formula-Based Validation Validation in Excel Using C#

Sometimes, simple dropdowns or fixed number ranges aren’t enough, especially when your rules depend on the values in other cells. With formula-based validation, you can define custom rules using Excel-style formulas. These rules can reference other cells and dynamically evaluate whether the input is valid. For example, you might want to ensure that the value in cell B1 is always greater than the value in A1. It is a common scenario in price comparisons, scoring sheets, or date sequences.

Aspose.Cells for .NET fully supports this feature and lets you define validation using custom formulas just like in Excel.

The following code sample shows how to apply formula-based validations in Excel using C#.

Configure Input and Error Messages in Excel Using C#

Applying data validation is only part of the solution. Guiding users when they enter incorrect data is just as important, and that’s where custom alerts and messages play a key role.

Aspose.Cells for .NET allows you to set up helpful input messages and error alerts that appear when users select a cell or enter invalid data. These messages improve user experience, reduce confusion, and make your Excel templates more professional.

For instance, when a user clicks into a cell, you can show a tooltip like

“Only values from 1 to 100 are allowed.”

And if they enter an incorrect value, Excel can display a dialog box saying:

“Invalid Entry: Please enter a number between 1 and 100.”

You can also customize how Excel responds to invalid input by choosing whether to block the user entirely (Stop), allow them to proceed with a warning (Warning), or simply display an informational message (Information).

Follow these steps to configure validation alerts using C#:

  1. After setting up the validation rule, set the InputTitle and InputMessage to show help text when a user selects the cell.
  2. Define the ErrorTitle and ErrorMessage to explain what went wrong if validation fails.
  3. Choose an AlertStyle — options include Stop, Warning, and Information.
  4. Set ShowError to true to enable the validation alert.
  5. Save the workbook.

These alerts make your spreadsheet more intuitive and user-friendly, especially when you’re creating templates for others to use or reuse frequently.

They alerts enhance usability and clearly guide users on what to enter.

Configure Input and Error Messages in Excel Using C#

Configure Input and Error Messages in Excel Using C#

Try it Free

Get a free temporary license to test the full capabilities of Aspose.Cells for .NET without any feature limits.

Free Resources

Explore more about Excel automation and validation using these resources:

Conclusion

In this article, we’ve shown how to implement data validation in Excel using C# with Aspose.Cells for .NET. From dropdowns to custom formulas, you can build intelligent and error-proof spreadsheets without relying on Excel itself.

If you have any questions or feedback, please feel free to post them on our free support forum. We’d love to help!

See Also