[Public] What is Excel's limitation for data validation lists and how can I work around it with IronXL?

Excel limitation: Data validation for List of Strings

Excel has a known limitation when using data validation with a list of strings:
If the total length of the list (including separators such as commas) exceeds 255 characters, Excel will not allow you to put it more, but the validation rule can still be applied.
(Please note that this issue occurs when you manually enter the list of strings in the Source field, rather than selecting data from an existing range in the Excel sheet.)

Workaround for the Excel:
To avoid this limitation, you can:

• Create a list of values in a column or row within the worksheet.

• Use a formula reference (e.g., =C1:C100) in the data validation settings to point to that range.

This approach allows you to create a dropdown list without being restricted by the 255-character limit.

Workaround for IronXL:
IronXL previously used the method AddStringListRule to apply a list of strings for data validation. However, due to the same character limit in Excel, using this method may allow the file to be saved, but it can cause Excel to crash when the file is opened if the list exceeds 255 characters. 

As you can see in the code which is trying to pass the list of string to create dropdown list on document which is able to write a file but crash later when open on the Excel.

To solve this, IronXL provides AddFormulaListRule, which is available through the DataValidations property of the Worksheet object.

Instead of passing the string list directly, we recommend the following approach:

1. Write the list of values into a range of cells on the worksheet.
2. Use AddFormulaListRule to reference that range as the source for the dropdown list.

This method ensures that the file is both readable and stable in Excel, even with long lists of values (which is maximum of 32,767 items).

For the code example below I using hidden work sheet to create list of values and referenced the range to the sheet I want with formula.

example output files:

Excel file:
validationRuleExample.xlsx

Console Application include both String List and Formula List:

ConsoleApp2.zip