Excel Limitation Regarding Data Validation for Strings

已更新:八月 19, 2025

Translated

View the article in English

Microsoft Excel has a known limitation for the length of string lists, which states that the total length of the list, including separators like commas, can be no more than 255 characters.

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, as shown below.

Excel Limitation Issue

Solution for the Excel Limitation on Excel

The standard workaround for this limitation is to create a list of values in a column or row within the worksheet, rather than entering the list of strings in the Source Field. As shown below.

Example Excel Limitation Solution

Afterwards, use a formula reference, for example, C1:C100, in the data validation and point to that range.

This approach circumvents the limitation, allowing you to create a dropdown list without being restricted by the 255-character limit.

Problem on IronXL

IronXL uses the method AddStringListRule to apply a list of strings for data validation. However, due to the same character limitation in Excel mentioned above, the saving method would still work. However, it's essential to be aware that if the list exceeds 255 characters, it could lead to a serious issue: Excel crashing when the file is opened. Here's an example illustrating the problem.

using IronXL;
using IronXL.DataValidations;

// Set your license key here if you have one
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY";
var workBook = WorkBook.Create(ExcelFileFormat.XLSX);

var sheet = workBook.CreateWorkSheet("Validation Rule Example using");
// Apply rules from row 2 down to row 500
var validationRangeStart = "2";
var validationRangeFinish = "500";

// --- 1. Define All Column Headers ---
sheet["A1"].Value = "String List Test";
sheet["B1"].Value = "Formula List Test";
sheet["A1:B1"].Style.Font.Bold = true;

// 256
string[] arrayString = new string[]
{
    "SKU-A01-R", "SKU-A02-R", "SKU-A03-R", "SKU-A04-R", "SKU-A05-R",
    "SKU-B01-G", "SKU-B02-G", "SKU-B03-G", "SKU-B04-G", "SKU-B05-G",
    "SKU-C01-B", "SKU-C02-B", "SKU-C03-B", "SKU-C04-B", "SKU-C05-B",
    "SKU-D01-Y", "SKU-D02-Y", "SKU-D03-Y", "SKU-D04-Y", "SKU-D05-Y",
    "END-001", "END-002", "END-003", "END-004", "END-005", "END-010", "END-001"
}; // total char with seperator 257 (230 char + 26 sperator [,] )

// no problem when write to a file
var skuValidation = sheet.DataValidations.AddStringListRule(sheet[$"A{validationRangeStart}:A{validationRangeFinish}"].RangeAddress, arrayString);

// set error box
skuValidation.ShowErrorBox = true;
skuValidation.ErrorBoxTitle = "Invalid SKU";
skuValidation.ErrorBoxText = "Please select a SKU from the list.";
skuValidation.ErrorAlert = ErrorAlert.Stop;

//attempt to save the file
var fileName = "CategoryProductCatalog.xlsx";
workBook.SaveAs(fileName);

using IronXL;
using IronXL.DataValidations;

// Set your license key here if you have one
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY";
var workBook = WorkBook.Create(ExcelFileFormat.XLSX);

var sheet = workBook.CreateWorkSheet("Validation Rule Example using");
// Apply rules from row 2 down to row 500
var validationRangeStart = "2";
var validationRangeFinish = "500";

// --- 1. Define All Column Headers ---
sheet["A1"].Value = "String List Test";
sheet["B1"].Value = "Formula List Test";
sheet["A1:B1"].Style.Font.Bold = true;

// 256
string[] arrayString = new string[]
{
    "SKU-A01-R", "SKU-A02-R", "SKU-A03-R", "SKU-A04-R", "SKU-A05-R",
    "SKU-B01-G", "SKU-B02-G", "SKU-B03-G", "SKU-B04-G", "SKU-B05-G",
    "SKU-C01-B", "SKU-C02-B", "SKU-C03-B", "SKU-C04-B", "SKU-C05-B",
    "SKU-D01-Y", "SKU-D02-Y", "SKU-D03-Y", "SKU-D04-Y", "SKU-D05-Y",
    "END-001", "END-002", "END-003", "END-004", "END-005", "END-010", "END-001"
}; // total char with seperator 257 (230 char + 26 sperator [,] )

// no problem when write to a file
var skuValidation = sheet.DataValidations.AddStringListRule(sheet[$"A{validationRangeStart}:A{validationRangeFinish}"].RangeAddress, arrayString);

// set error box
skuValidation.ShowErrorBox = true;
skuValidation.ErrorBoxTitle = "Invalid SKU";
skuValidation.ErrorBoxText = "Please select a SKU from the list.";
skuValidation.ErrorAlert = ErrorAlert.Stop;

//attempt to save the file
var fileName = "CategoryProductCatalog.xlsx";
workBook.SaveAs(fileName);

Imports IronXL
Imports IronXL.DataValidations

' Set your license key here if you have one
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY"
Dim workBook = WorkBook.Create(ExcelFileFormat.XLSX)

Dim sheet = workBook.CreateWorkSheet("Validation Rule Example using")
' Apply rules from row 2 down to row 500
Dim validationRangeStart = "2"
Dim validationRangeFinish = "500"

' --- 1. Define All Column Headers ---
sheet("A1").Value = "String List Test"
sheet("B1").Value = "Formula List Test"
sheet("A1:B1").Style.Font.Bold = True

' 256
Dim arrayString() As String = { "SKU-A01-R", "SKU-A02-R", "SKU-A03-R", "SKU-A04-R", "SKU-A05-R", "SKU-B01-G", "SKU-B02-G", "SKU-B03-G", "SKU-B04-G", "SKU-B05-G", "SKU-C01-B", "SKU-C02-B", "SKU-C03-B", "SKU-C04-B", "SKU-C05-B", "SKU-D01-Y", "SKU-D02-Y", "SKU-D03-Y", "SKU-D04-Y", "SKU-D05-Y", "END-001", "END-002", "END-003", "END-004", "END-005", "END-010", "END-001" }

' no problem when write to a file
Dim skuValidation = sheet.DataValidations.AddStringListRule(sheet($"A{validationRangeStart}:A{validationRangeFinish}").RangeAddress, arrayString)

' set error box
skuValidation.ShowErrorBox = True
skuValidation.ErrorBoxTitle = "Invalid SKU"
skuValidation.ErrorBoxText = "Please select a SKU from the list."
skuValidation.ErrorAlert = ErrorAlert.Stop

'attempt to save the file
Dim fileName = "CategoryProductCatalog.xlsx"
workBook.SaveAs(fileName)

$vbLabelText $csharpLabel

Within the code, we are trying to pass a list of strings named arrayString as the source of a list of strings to create a dropdown list. We then try to save it as CategoryProductCatalog.xlsx. However, due to these limitations, when opening the file with Microsoft Excel, it displays a message stating that the file is corrupted and cannot be opened.

alt text

Solution for IronXL

To properly address this Excel limitation, IronXL provides the method AddFormulaListRule, which is available from the DataValidations property. This method is similar to the solution for working with Excel.

We first write the list of values into a range of cells on the worksheet. Then use AddFormulaListRule to reference that range as the source of the dropdownlist. Effectively circumventing the limitation by the same methods that we did to the Excel solution, but in this instance, it's programmatic.

Using AddFormulaListRule properly circumvents the Excel limitation but also ensures that the file remains both readable and stable when opened in Excel, even with long lists of values.

Code Example

Below is the additional example code that circumvents this issue. Using the same workbook, we first create a worksheet and then define all column headers. We input the list of strings from the previous example, as well as new data to illustrate the functionality. We then call AddFormulaListRule with the validation range from above. We then finally save it as validationRuleExample.xlsx. To ensure that our workbook is not corrupted and can be used normally, we open it in Excel. This step is crucial as it demonstrates the successful implementation of our code.

using IronXL;
using IronXL.DataValidations;
using System;

// Set your license key here if you have one
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY";
var workBook = WorkBook.Create(ExcelFileFormat.XLSX);

var sheet = workBook.CreateWorkSheet("Validation Rule Example using");
// Apply rules from row 2 down to row 500
var validationRangeStart = "2";
var validationRangeFinish = "500";

// --- 1. Define All Column Headers ---
sheet["A1"].Value = "String List Test";
sheet["B1"].Value = "Formula List Test";
sheet["A1:B1"].Style.Font.Bold = true;

string[] arrayString2 = new string[] 
{ 
    "SKU-A01-R", "SKU-A02-R", "SKU-A03-R", "SKU-A04-R", "SKU-A05-R",
    "SKU-B01-G", "SKU-B02-G", "SKU-B03-G", "SKU-B04-G", "SKU-B05-G",
    "SKU-C01-B", "SKU-C02-B", "SKU-C03-B", "SKU-C04-B", "SKU-C05-B",
    "SKU-D01-Y", "SKU-D02-Y", "SKU-D03-Y", "SKU-D04-Y", "SKU-D05-Y",
    "END-001", "END-002", "END-003", "END-004", "END-005", "END-010", "END-001",
    "IRON01","IRON02","IRON03","IRON04","IRON05","IRON06","IRON07","IRON08","IRON09","IRON10",
    "IRON11","IRON12","IRON13","IRON14","IRON15","IRON16","IRON17","IRON18","IRON19","IRON20",
    "IRON21","IRON22","IRON23","IRON24","IRON25","IRON26","IRON27","IRON28","IRON29","IRON30",
    "IRON31","IRON32","IRON33","IRON34","IRON35","IRON36","IRON37","IRON38","IRON39","IRON40",
    "IRON41","IRON42","IRON43","IRON44","IRON45","IRON46","IRON47","IRON48","IRON49","IRON50",
    "IRON51","IRON52","IRON53","IRON54","IRON55","IRON56","IRON57","IRON58","IRON59","IRON60",
    "IRON61","IRON62","IRON63","IRON64","IRON65","IRON66","IRON67","IRON68","IRON69","IRON70",
    "IRON71","IRON72","IRON73","IRON74","IRON75","IRON76","IRON77","IRON78","IRON79","IRON80",
    "IRON81","IRON82","IRON83","IRON84","IRON85","IRON86","IRON87","IRON88","IRON89","IRON90",
    "IRON91","IRON92","IRON93","IRON94","IRON95","IRON96","IRON97","IRON98","IRON99","IRON100"
}; // total char with seperator 257 (230 char + 26 sperator [,] )

//Write the contents of your array to column A of the data sheet.
for (int i = 0; i < arrayString2.Length; i++)
{
    // Note: IronXL row and cell indices are 1-based.
    sheet[$"A{i + 1}"].Value = arrayString2[i];
}

// Create the formula string that points to our data range.
// This will look like: 'ValidationData'!$A$1:$A$100

string formula = $"'{sheet.Name}'!$A$1:$A${arrayString2.Length}";
Console.WriteLine($"Generated formula for validation: {formula}");

var sku2Validation = sheet.DataValidations.AddFormulaListRule(sheet[$"B{validationRangeStart}:B{validationRangeFinish}"], formula);

sku2Validation.ShowErrorBox = true;
sku2Validation.ErrorBoxTitle = "Invalid SKU2";
sku2Validation.ErrorBoxText = "Please select a SKU from the list.";
sku2Validation.ErrorAlert = ErrorAlert.Stop;

// --- Final Touches ---
sheet.AutoSizeColumn(0, true);
sheet.AutoSizeColumn(1, true);

var fileName = "validationRuleExample.xlsx";
workBook.SaveAs(fileName);

Console.WriteLine($"Successfully created '{fileName}' with 2 validation rules example.");

using IronXL;
using IronXL.DataValidations;
using System;

// Set your license key here if you have one
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY";
var workBook = WorkBook.Create(ExcelFileFormat.XLSX);

var sheet = workBook.CreateWorkSheet("Validation Rule Example using");
// Apply rules from row 2 down to row 500
var validationRangeStart = "2";
var validationRangeFinish = "500";

// --- 1. Define All Column Headers ---
sheet["A1"].Value = "String List Test";
sheet["B1"].Value = "Formula List Test";
sheet["A1:B1"].Style.Font.Bold = true;

string[] arrayString2 = new string[] 
{ 
    "SKU-A01-R", "SKU-A02-R", "SKU-A03-R", "SKU-A04-R", "SKU-A05-R",
    "SKU-B01-G", "SKU-B02-G", "SKU-B03-G", "SKU-B04-G", "SKU-B05-G",
    "SKU-C01-B", "SKU-C02-B", "SKU-C03-B", "SKU-C04-B", "SKU-C05-B",
    "SKU-D01-Y", "SKU-D02-Y", "SKU-D03-Y", "SKU-D04-Y", "SKU-D05-Y",
    "END-001", "END-002", "END-003", "END-004", "END-005", "END-010", "END-001",
    "IRON01","IRON02","IRON03","IRON04","IRON05","IRON06","IRON07","IRON08","IRON09","IRON10",
    "IRON11","IRON12","IRON13","IRON14","IRON15","IRON16","IRON17","IRON18","IRON19","IRON20",
    "IRON21","IRON22","IRON23","IRON24","IRON25","IRON26","IRON27","IRON28","IRON29","IRON30",
    "IRON31","IRON32","IRON33","IRON34","IRON35","IRON36","IRON37","IRON38","IRON39","IRON40",
    "IRON41","IRON42","IRON43","IRON44","IRON45","IRON46","IRON47","IRON48","IRON49","IRON50",
    "IRON51","IRON52","IRON53","IRON54","IRON55","IRON56","IRON57","IRON58","IRON59","IRON60",
    "IRON61","IRON62","IRON63","IRON64","IRON65","IRON66","IRON67","IRON68","IRON69","IRON70",
    "IRON71","IRON72","IRON73","IRON74","IRON75","IRON76","IRON77","IRON78","IRON79","IRON80",
    "IRON81","IRON82","IRON83","IRON84","IRON85","IRON86","IRON87","IRON88","IRON89","IRON90",
    "IRON91","IRON92","IRON93","IRON94","IRON95","IRON96","IRON97","IRON98","IRON99","IRON100"
}; // total char with seperator 257 (230 char + 26 sperator [,] )

//Write the contents of your array to column A of the data sheet.
for (int i = 0; i < arrayString2.Length; i++)
{
    // Note: IronXL row and cell indices are 1-based.
    sheet[$"A{i + 1}"].Value = arrayString2[i];
}

// Create the formula string that points to our data range.
// This will look like: 'ValidationData'!$A$1:$A$100

string formula = $"'{sheet.Name}'!$A$1:$A${arrayString2.Length}";
Console.WriteLine($"Generated formula for validation: {formula}");

var sku2Validation = sheet.DataValidations.AddFormulaListRule(sheet[$"B{validationRangeStart}:B{validationRangeFinish}"], formula);

sku2Validation.ShowErrorBox = true;
sku2Validation.ErrorBoxTitle = "Invalid SKU2";
sku2Validation.ErrorBoxText = "Please select a SKU from the list.";
sku2Validation.ErrorAlert = ErrorAlert.Stop;

// --- Final Touches ---
sheet.AutoSizeColumn(0, true);
sheet.AutoSizeColumn(1, true);

var fileName = "validationRuleExample.xlsx";
workBook.SaveAs(fileName);

Console.WriteLine($"Successfully created '{fileName}' with 2 validation rules example.");

Imports IronXL
Imports IronXL.DataValidations
Imports System

' Set your license key here if you have one
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY"
Dim workBook = WorkBook.Create(ExcelFileFormat.XLSX)

Dim sheet = workBook.CreateWorkSheet("Validation Rule Example using")
' Apply rules from row 2 down to row 500
Dim validationRangeStart = "2"
Dim validationRangeFinish = "500"

' --- 1. Define All Column Headers ---
sheet("A1").Value = "String List Test"
sheet("B1").Value = "Formula List Test"
sheet("A1:B1").Style.Font.Bold = True

Dim arrayString2() As String = {"SKU-A01-R", "SKU-A02-R", "SKU-A03-R", "SKU-A04-R", "SKU-A05-R", "SKU-B01-G", "SKU-B02-G", "SKU-B03-G", "SKU-B04-G", "SKU-B05-G", "SKU-C01-B", "SKU-C02-B", "SKU-C03-B", "SKU-C04-B", "SKU-C05-B", "SKU-D01-Y", "SKU-D02-Y", "SKU-D03-Y", "SKU-D04-Y", "SKU-D05-Y", "END-001", "END-002", "END-003", "END-004", "END-005", "END-010", "END-001", "IRON01", "IRON02", "IRON03", "IRON04", "IRON05", "IRON06", "IRON07", "IRON08", "IRON09", "IRON10", "IRON11", "IRON12", "IRON13", "IRON14", "IRON15", "IRON16", "IRON17", "IRON18", "IRON19", "IRON20", "IRON21", "IRON22", "IRON23", "IRON24", "IRON25", "IRON26", "IRON27", "IRON28", "IRON29", "IRON30", "IRON31", "IRON32", "IRON33", "IRON34", "IRON35", "IRON36", "IRON37", "IRON38", "IRON39", "IRON40", "IRON41", "IRON42", "IRON43", "IRON44", "IRON45", "IRON46", "IRON47", "IRON48", "IRON49", "IRON50", "IRON51", "IRON52", "IRON53", "IRON54", "IRON55", "IRON56", "IRON57", "IRON58", "IRON59", "IRON60", "IRON61", "IRON62", "IRON63", "IRON64", "IRON65", "IRON66", "IRON67", "IRON68", "IRON69", "IRON70", "IRON71", "IRON72", "IRON73", "IRON74", "IRON75", "IRON76", "IRON77", "IRON78", "IRON79", "IRON80", "IRON81", "IRON82", "IRON83", "IRON84", "IRON85", "IRON86", "IRON87", "IRON88", "IRON89", "IRON90", "IRON91", "IRON92", "IRON93", "IRON94", "IRON95", "IRON96", "IRON97", "IRON98", "IRON99", "IRON100"}

'Write the contents of your array to column A of the data sheet.
For i As Integer = 0 To arrayString2.Length - 1
	' Note: IronXL row and cell indices are 1-based.
	sheet($"A{i + 1}").Value = arrayString2(i)
Next i

' Create the formula string that points to our data range.
' This will look like: 'ValidationData'!$A$1:$A$100

Dim formula As String = $"'{sheet.Name}'!$A$1:$A${arrayString2.Length}"
Console.WriteLine($"Generated formula for validation: {formula}")

Dim sku2Validation = sheet.DataValidations.AddFormulaListRule(sheet($"B{validationRangeStart}:B{validationRangeFinish}"), formula)

sku2Validation.ShowErrorBox = True
sku2Validation.ErrorBoxTitle = "Invalid SKU2"
sku2Validation.ErrorBoxText = "Please select a SKU from the list."
sku2Validation.ErrorAlert = ErrorAlert.Stop

' --- Final Touches ---
sheet.AutoSizeColumn(0, True)
sheet.AutoSizeColumn(1, True)

Dim fileName = "validationRuleExample.xlsx"
workBook.SaveAs(fileName)

Console.WriteLine($"Successfully created '{fileName}' with 2 validation rules example.")

$vbLabelText $csharpLabel

Output Excel Files

As you can see, the Excel file is stable and doesn't cause any crashes after using AddFormulaListRule. To try it out yourself, please refer to this link for a demo of the application used in this article, along with the Excel file. This demo will allow you to see the process in action and understand how to apply it in your work.