How to Create a Dropdown List in Excel Like a Pro [Python Tutorial]

Creating a dropdown list in Excel is one of those simple tricks that instantly makes your spreadsheet cleaner, smarter, and easier to use. Whether you're building a form, organizing product data, or preventing input errors, dropdowns help users choose from a preset list instead of typing everything manually. In this tutorial, you’ll learn how to create dropdown lists like a pro — not just in Excel, but also with Python, so you can automate the entire process. Let’s make your data entry faster, accurate, and stress-free.

Install Python Library for Excel Processing

Before creating a dropdown list in Excel, we need to install a Python library. In this tutorial, we’ll use Spire.XLS for Python — a professional Excel handling component that supports tasks like format conversion, cell editing, data processing, and more. It works well for both simple and advanced use cases, and best of all, it offers a free version for personal or small projects, which you can download directly from the official website.

Installing it is easy. Simply run:

pip install spire.xls

Or download the package on its website if you prefer a custom installation.

Create a Dropdown List in Excel Based on Cell Values with Python

Now that the library is installed and ready to use, it’s time to put it into action. In this section, we’ll use Python to create a dropdown list in Excel based on the values already stored in the worksheet. Instead of manually configuring the list inside Excel, Python will automate the entire process, making it faster, cleaner, and easier to maintain—especially when working with large or frequently updated datasets. Let’s walk through the steps and see how to generate a dynamic dropdown list programmatically.

Steps to create a dropdown list in Excel using Python:

• Create a Workbook object and load a sample Excel file.

• Get a worksheet, and use the Worksheet.Range[] property to get the cell range you need.

• Create a dropdown list in the cell range using the CellRange.DataValidation.DataRange property.

• Save the updated Excel file as a new one.

The code below demonstrates how to create a dropdown list in the cell range "C2:C6".

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

# Create an instance of Workbook
workbook = Workbook()

# Load an Excel file
workbook.LoadFromFile("E:/DownloadsNew/excel_dropdown_raw_data.xlsx")

# Get the second worksheet
sheet = workbook.Worksheets.get_Item(1)

# Get a specific cell range
cellRange = sheet.Range["C2:C6"]

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

# Save the Workbook
workbook.SaveToFile("E:/DownloadsNew/DropDownListExcel.xlsx", FileFormat.Version2016)
workbook.Dispose()

Here’s the preview of the output file:

Create Dropdown Lists Based on Strings in Excel with Python

In addition to using existing cell values, you can also create dropdown lists in Excel directly from a set of predefined strings — without having to place them in a separate cell range. This approach keeps your spreadsheet tidy and professional, while Python allows you to quickly define the options and turn them into a functional dropdown list. In the following example, we’ll show how to generate a dropdown list based on custom strings programmatically.

Steps to create a dropdown list based on strings using Python:

• Create a Workbook object and load a sample Excel file.

• Get a worksheet, and use the Worksheet.Range[] property to get the cell range you need.

• Set a string list as the values of data validation in the cell range through the CellRange.DataValidation.Values property to create drop-down lists.

• Save the modified Excel file.

The code below demonstrates how to create a dropdown list in the cell range "F2:F6" based on custom values.

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

# Create an instance of Workbook
workbook = Workbook()

# Load an Excel file
workbook.LoadFromFile("E:/DownloadsNew/excel_dropdown_raw_data.xlsx")

# Get the second worksheet
sheet = workbook.Worksheets.get_Item(1)

# Get a cell range
cellRange = sheet.Range["F2:F6"]

# Set the value for data validation to create drop-down lists
cellRange.DataValidation.Values = ["North America", "Europe", "Asia-Pacific", "Middle East", "South America"]

# Save the Workbook
workbook.SaveToFile("E:/DownloadsNew/ValueDropDownListExcel.xlsx", FileFormat.Version2016)
workbook.Dispose()

Here’s the output Excel file:

The Bottom Line

With just a few lines of Python, you can create professional and dynamic dropdown lists in Excel, whether based on existing cell values or custom strings. Spire.XLS for Python makes this process fast, reliable, and easy to integrate into your projects.

Try Spire.XLS for Python and keep your spreadsheets clean, accurate, and professional!