Drop-down lists in Excel worksheets are an indispensable tool for enhancing data accuracy, efficiency, and usability in spreadsheet management. By offering pre-defined options within a cell, they not only streamline data entry processes but also enforce consistency, reducing the likelihood of input errors. This feature is particularly valuable when working with large datasets or collaborative projects where maintaining uniformity across multiple entries is crucial. This article demonstrates how to create customized drop-down lists within Excel worksheets using Spire.XLS for Python, empowering users to create organized and user-friendly worksheets.

• Create Drop-Down Lists Based on Cell Values Using Python
• Create Drop-Down Lists Based on Strings Using Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to: How to Install Spire.XLS for Python on Windows

Create Drop-Down Lists Based on Cell Values Using Python

In Excel worksheets, creating drop-down lists is accomplished through the data validation feature. With Spire.XLS for Python, developers can use the CellRange.DataValidation.DataRange property to create drop-down lists within cells and use the data from the specified cell range as list options.

The detailed steps for creating a drop-down list based on cell values are as follows:

• Create an instance of Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a worksheet using Workbook.Worksheets.get_Item() method.
• Get a specific cell range through Worksheet.Range[] property.
• Set the data range for data validation of the cell range through CellRange.DataValidation.DataRange property to create drop-down lists with cell values.
• Save the workbook using Workbook.SaveToFile() method.

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

# Create an instance of Workbook
workbook = Workbook()

# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets.get_Item(0)

# Get a specific cell range
cellRange = sheet.Range["C3:C7"]

# Set the data range for data validation to create drop-down lists in the cell range
cellRange.DataValidation.DataRange = sheet.Range["F4:H4"]

# Save the workbook
workbook.SaveToFile("output/DropDownListExcel.xlsx", FileFormat.Version2016)
workbook.Dispose()

Create Drop-Down Lists Based on String Using Python

Spire.XLS for Python also provides the CellRange.DataValidation.Values property to create drop-down lists in cells directly using string lists.

The detailed steps for creating drop-down lists based on values are as follows:

• Create an instance of Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a worksheet using Workbook.Worksheets.get_Item() method.
• Get a specific cell range through Worksheet.Range[] property.
• Set a string list as the values of data validation in the cell range through CellRange.DataValidation.Values property to create drop-down lists based on strings.
• Save the workbook using Workbook.SaveToFile() method.

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

# Create an instance of Workbook
workbook = Workbook()

# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets.get_Item(0)

# Get a cell range
cellRange = sheet.Range["D3:D7"]

# Set the value for data validation to create drop-down lists
cellRange.DataValidation.Values = ["Starter", "Technician", "Director", "Executive"]

# Save the workbook
workbook.SaveToFile("output/ValueDropDownListExcel.xlsx", FileFormat.Version2016)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.