In employee records, order forms, project trackers, and other Excel templates, some fields should only contain predefined values, such as departments, task statuses, approval results, or product categories.
If users enter these values manually, the same option can easily appear in different forms. For example, a task status might be entered as In Progress, In progress, or an abbreviated variation. This creates extra cleanup work later when the workbook is filtered, summarized, or imported into another system.
Adding drop-down lists to these cells gives users a controlled set of choices and helps reduce inconsistent data at the point of entry.
This article shows how to create Excel drop-down lists in C# in three common scenarios:
Use fixed values as drop-down options
Use a cell range in the current worksheet as the data source
Use data from another worksheet as the drop-down source
Install the Required Excel Library
This article uses Spire.XLS for .NET to create and modify Excel files. It supports Excel data validation, including list-based drop-downs, and does not require Microsoft Excel to be installed on the machine running the code.
You can install it through NuGet Package Manager Console:
Install-Package Spire.XLS
Then import the required namespace:
using Spire.Xls;
The implementation depends mainly on where the drop-down values come from.
Create an Excel Drop-Down List from Fixed Values
If the available options are limited and unlikely to change often, the simplest approach is to define them directly in a string array.
For example, a task management sheet may restrict the task status to:
Not Started
In Progress
Completed
On Hold
The following example creates a simple task table and adds a status drop-down list to cell D2.
using Spire.Xls;
namespace CreateExcelDropdown
{
class Program
{
static void Main(string[] args)
{
// Create a Workbook object
Workbook workbook = new Workbook();
// Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Task Management";
// Add headers
sheet.Range["A1"].Text = "Task ID";
sheet.Range["B1"].Text = "Task Name";
sheet.Range["C1"].Text = "Owner";
sheet.Range["D1"].Text = "Status";
// Add sample data
sheet.Range["A2"].Text = "T001";
sheet.Range["B2"].Text = "Prepare Project Plan";
sheet.Range["C2"].Text = "Alice Johnson";
// Define drop-down options
string[] statusValues =
{
"Not Started",
"In Progress",
"Completed",
"On Hold"
};
// Apply the drop-down list to D2
sheet.Range["D2"].DataValidation.Values = statusValues;
// Auto-fit columns
sheet.AllocatedRange.AutoFitColumns();
// Save the workbook
workbook.SaveToFile(
"TaskStatusDropdown.xlsx",
ExcelVersion.Version2016);
workbook.Dispose();
}
}
}
The key line is:
sheet.Range["D2"].DataValidation.Values = statusValues;
DataValidation.Values accepts a string array and uses the array items as the available list values.
This approach works well for fixed choices such as:
Status
Priority
Enabled / Disabled
Approval result
Fixed categories
If the options are numerous or change frequently, hard-coding them in the application is less convenient. In that case, storing the values in worksheet cells is usually easier to maintain.
Create a Drop-Down List from a Cell Range
Sometimes the available options already exist inside the workbook.
For example, an employee worksheet may contain a department list in F2:F5:
| Cell | Value |
|---|---|
| F2 | Sales |
| F3 | Finance |
| F4 | IT |
| F5 | Human Resources |
The Department field can then use that range as its drop-down source.
using Spire.Xls;
namespace CreateDropdownFromRange
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Employees";
// Create the employee table
sheet.Range["A1"].Text = "Employee ID";
sheet.Range["B1"].Text = "Employee Name";
sheet.Range["C1"].Text = "Department";
sheet.Range["A2"].Text = "E001";
sheet.Range["B2"].Text = "John Smith";
// Create the department source list
sheet.Range["F1"].Text = "Department List";
sheet.Range["F2"].Text = "Sales";
sheet.Range["F3"].Text = "Finance";
sheet.Range["F4"].Text = "IT";
sheet.Range["F5"].Text = "Human Resources";
// Get the source range
CellRange departmentRange = sheet.Range["F2:F5"];
// Use the range as the drop-down source
sheet.Range["C2"].DataValidation.DataRange =
departmentRange;
sheet.AllocatedRange.AutoFitColumns();
workbook.SaveToFile(
"DepartmentDropdown.xlsx",
ExcelVersion.Version2016);
workbook.Dispose();
}
}
}
The main difference here is:
sheet.Range["C2"].DataValidation.DataRange = departmentRange;
Using a worksheet range as the data source makes the list easier to maintain.
For example, if another department is added later, the source data can be updated in the workbook rather than duplicated as a long list of hard-coded values in C#.
This approach is useful when the source values already belong to the same worksheet.
In larger business templates, however, storing helper values beside the main data can make the sheet look cluttered. A more common design is to keep these values on a separate worksheet.
Create a Drop-Down List from Another Worksheet
In real-world templates, business data and lookup values are often stored separately.
For example, a workbook might contain:
Employees: stores employee informationOptions: stores departments, job titles, statuses, and other lookup values
This keeps the main worksheet cleaner and makes the source values easier to manage.
The following example creates a drop-down list whose values come from another worksheet.
using Spire.Xls;
namespace CreateCrossSheetDropdown
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
// Get the employee worksheet
Worksheet employeeSheet = workbook.Worksheets[0];
employeeSheet.Name = "Employees";
// Add the options worksheet
Worksheet optionsSheet =
workbook.Worksheets.Add("Options");
// -------------------------
// Employees worksheet
// -------------------------
employeeSheet.Range["A1"].Text = "Employee ID";
employeeSheet.Range["B1"].Text = "Employee Name";
employeeSheet.Range["C1"].Text = "Department";
employeeSheet.Range["A2"].Text = "E001";
employeeSheet.Range["B2"].Text = "John Smith";
// -------------------------
// Options worksheet
// -------------------------
optionsSheet.Range["A1"].Text = "Department List";
optionsSheet.Range["A2"].Text = "Sales";
optionsSheet.Range["A3"].Text = "Finance";
optionsSheet.Range["A4"].Text = "IT";
optionsSheet.Range["A5"].Text = "Human Resources";
// Allow data validation to reference another worksheet
workbook.Allow3DRangesInDataValidation = true;
// Get the department source range
CellRange departmentRange =
optionsSheet.Range["A2:A5"];
// Apply the source range to the Department field
employeeSheet.Range["C2"]
.DataValidation.DataRange = departmentRange;
employeeSheet.AllocatedRange.AutoFitColumns();
optionsSheet.AllocatedRange.AutoFitColumns();
workbook.SaveToFile(
"CrossSheetDropdown.xlsx",
ExcelVersion.Version2016);
workbook.Dispose();
}
}
}
One setting is easy to overlook when the validation source is on another worksheet:
workbook.Allow3DRangesInDataValidation = true;
This allows the data validation rule to reference a range outside the current worksheet.
After enabling it, you can set the source range normally:
employeeSheet.Range["C2"].DataValidation.DataRange =
optionsSheet.Range["A2:A5"];
This layout works well for business templates that need centrally maintained lookup values.
A workbook might be organized like this:
Workbook
│
├── Employees
│ ├── Employee ID
│ ├── Employee Name
│ └── Department ▼
│
└── Options
├── Sales
├── Finance
├── IT
└── Human Resources
If end users do not need to see the helper data, the Options worksheet can also be hidden.
Apply the Same Drop-Down List to Multiple Cells
The previous examples apply data validation to a single cell, but real templates usually need the same list across many rows.
For example, to apply the department list to C2:C100:
employeeSheet.Range["C2:C100"]
.DataValidation.DataRange = departmentRange;
The same approach works with fixed values:
string[] statusValues =
{
"Not Started",
"In Progress",
"Completed",
"On Hold"
};
sheet.Range["D2:D100"].DataValidation.Values =
statusValues;
Applying validation to a range is simpler than looping through cells one by one and is usually a better fit for generated Excel templates.
Which Approach Should You Use?
The main difference between the three approaches is where the drop-down values are stored.
| Data Source | Implementation | Best For |
|---|---|---|
| Fixed strings | DataValidation.Values |
Status, priority, approval results, and other fixed options |
| Current worksheet range | DataValidation.DataRange |
Simple templates with a small amount of helper data |
| Another worksheet |
DataValidation.DataRange + Allow3DRangesInDataValidation
|
Business templates with centrally managed lookup values |
For a small fixed set such as Yes / No or Enabled / Disabled, a string array is usually the simplest choice.
If the values change regularly or come from business data, using a cell range is easier to maintain.
For long-lived templates such as employee forms, order forms, or project tracking workbooks, keeping lookup values on a dedicated worksheet is often the cleaner approach.
Practical Considerations
1. Avoid Hard-Coding Frequently Changing Options
Suppose a department list originally contains:
Sales
Finance
IT
and later needs:
Customer Service
If the entire list is hard-coded in C#, the application must be updated and redeployed.
If the values come from a database, configuration source, or admin system, a more maintainable workflow is:
Read the latest values from the business system
Write them to an
OptionsworksheetPoint the data validation rule to that range
This keeps the generated workbook aligned with the current business data.
2. Keep the Source Range in Sync
If the drop-down list points to:
A2:A5
but the actual list later grows to A8, the new values will not appear unless the validation source range is updated.
For dynamic data, calculate the final row when generating the workbook.
For example:
int lastRow = 8;
employeeSheet.Range["C2:C100"]
.DataValidation.DataRange =
optionsSheet.Range["A2:A" + lastRow];
This makes the source range follow the actual number of available options.
3. Excel Drop-Down Lists Do Not Replace Server-Side Validation
Excel data validation helps reduce user input errors, but it should not be treated as the only validation layer if the data will later be imported into a database or business system.
For example, even if the Department field uses a drop-down list, the import process can still verify that the selected department is currently valid.
This is important because Excel validation can sometimes be bypassed through copy and paste, external editing tools, or direct file manipulation.
4. Dependent Drop-Down Lists Require Additional Logic
Some lists depend on a previous selection, for example:
Country → City
Product Category → Product
Department → Job Title
A simple fixed list is not enough in these cases.
Dependent drop-downs usually require a combination of:
Named ranges
Data validation formulas
Excel functions such as
INDIRECT
It is therefore worth deciding whether the options are independent or hierarchical before designing the workbook template.
Conclusion
Drop-down lists are a practical way to improve data consistency in Excel templates generated with C#.
This article covered three common approaches:
Creating a drop-down list from fixed string values
Using a cell range in the current worksheet as the data source
Referencing values stored on another worksheet
For small and stable option sets, a string array is usually sufficient. For values that need regular maintenance or come from business systems, storing the options in worksheet cells and using them as the validation source is generally more flexible.
Choosing the data source based on how the options are maintained makes the resulting Excel file easier to use and easier to keep in sync with the rest of the application.
Top comments (0)