DEV Community

Jeremy K.
Jeremy K.

Posted on

How to Create Drop-Down Lists in Excel with Python

Drop-down lists help enforce consistent data entry in Excel. Manually adding them is fine for a few cells, but Python automation is more efficient when you need to add them across many cells, multiple files, or repeatable templates.

Free Spire.XLS for Python offers two direct approaches:

  • Inline list โ€” specify options with DataValidation.Values
  • Cell-range source โ€” reference worksheet cells with DataValidation.DataRange

Prerequisites

Install the free library:

pip install spire.xls.free
Enter fullscreen mode Exit fullscreen mode

Import the required modules:

from spire.xls import *
from spire.xls.common import *
Enter fullscreen mode Exit fullscreen mode

Core Concept: DataValidation

In Free Spire.XLS, a drop-down list is a list-type data validation. Every CellRange exposes a DataValidation property. Configure either of these two settings:

  • DataValidation.Values: a Python list of options
  • DataValidation.DataRange: a CellRange containing the options

Once configured, each target cell displays a drop-down arrow. Users can select from the preset options, and whether manual input is allowed depends on the error alert settings.

Method 1: Inline List with Values

Use this method when the options are fixed and few. No extra worksheet area is needed.

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]
sheet.Name = "Employee Info"

# Headers
sheet.Range["A1"].Text = "Name"
sheet.Range["C1"].Text = "Position"

# Target cells
cellRange = sheet.Range["C2:C10"]

# Inline options
cellRange.DataValidation.Values = ["Intern", "Technician", "Supervisor", "Director"]

workbook.SaveToFile("PositionDropDownList.xlsx", FileFormat.Version2016)
workbook.Dispose()
Enter fullscreen mode Exit fullscreen mode

Note: Inline list values must stay within Excelโ€™s 255-character limit.

Method 2: Range-Based List with DataRange

Use this method when options are numerous or may change over time. Store the options in worksheet cells and reference that range.

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]
sheet.Name = "Employee Info"

# Headers
sheet.Range["A1"].Text = "Name"
sheet.Range["C1"].Text = "Department"

# Source options
sheet.Range["F1"].Text = "HR"
sheet.Range["G1"].Text = "Finance"
sheet.Range["H1"].Text = "IT"

# Target cells
cellRange = sheet.Range["C2:C10"]

# Reference source range
cellRange.DataValidation.DataRange = sheet.Range["F1:H1"]

workbook.SaveToFile("DepartmentDropDownList.xlsx", FileFormat.Version2016)
workbook.Dispose()
Enter fullscreen mode Exit fullscreen mode

For best results, use a single row or column as the source range. Multi-row or multi-column ranges may produce unexpected option ordering.

If you are working with an existing workbook:

workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")

sheet = workbook.Worksheets.get_Item(0)
cellRange = sheet.Range["C3:C7"]
cellRange.DataValidation.DataRange = sheet.Range["F4:H4"]

workbook.SaveToFile("output/DropDownListExcel.xlsx", FileFormat.Version2016)
workbook.Dispose()
Enter fullscreen mode Exit fullscreen mode

Cross-Worksheet Reference

You can also reference options stored on another worksheet:

data_sheet = workbook.Worksheets[1]
data_sheet.Name = "DataSource"
data_sheet.Range["A1"].Text = "HR"
# Add more options as needed

cellRange.DataValidation.DataRange = data_sheet.Range["A1:A10"]
Enter fullscreen mode Exit fullscreen mode

For maximum compatibility, consider using a named range if Excel has trouble with direct cross-sheet validation.

Which Method Should You Use?

Factor Cell-range source Inline values
Maintenance Edit in Excel; no code changes Edit code and regenerate
Worksheet layout Requires source cells No extra cells
Size limit Limited by Excel rows 255-character total limit
Best for Dynamic or many options Fixed or few options

Use inline values for simple, one-off files. Use a cell range when business users need to maintain options directly in the workbook.

Optional: Error and Input Messages

After setting Values or DataRange, you can add user guidance:

# Error alert for invalid input
cellRange.DataValidation.ShowError = True
cellRange.DataValidation.ErrorTitle = "Invalid Input"
cellRange.DataValidation.ErrorMessage = "Please select an option from the drop-down list."

# Input prompt when the cell is selected
cellRange.DataValidation.ShowInput = True
cellRange.DataValidation.InputTitle = "Select an Option"
cellRange.DataValidation.InputMessage = "Choose a value from the drop-down list."
Enter fullscreen mode Exit fullscreen mode
  • ShowError = True: invalid entries trigger a dialog. AlertStyle can be set to Stop, Warning, or Information.
  • ShowInput = True: a tooltip appears when the cell is selected.

Summary

Free Spire.XLS for Python makes Excel drop-down lists easy to automate. Use DataValidation.Values for simple inline lists and DataValidation.DataRange for maintainable, range-based lists. Add error and input messages to improve data quality and user experience.

Top comments (0)