Excel Data Validation Using Python Openpyxl Tutorial #10

In this video, we will automate data validation in Excel. This will help you keep your data clean and accurate while avoiding manual errors.

[gearoidmulligan]

Is anyone else getting an error saying ‘Worksheet’ has no attribute ’add_data_validation’. And also what if you have an excel file with multiple sheets but you only want to apply it to one specific sheet?

[devkumar]

for me its working fine only on python end on showing any data validation list in .xls

[rlvczxo]

is it possible to get the values from "worksheet1" and add the Data validation (dropdown list button) into "worksheet2"?

[rachelramphal]

How could I do this if I already have a workbook with sheet1?

So I have the file cookies.xlsx that has data in sheet1.

I want to add sheet2 with data validation to the same cookies.xlsx without losing my data in sheet1.

How would I do this? Thanks!

[gautam]

Hi. Great tutorial one question. Can you please help me

Can we fix a default value for cell where we are putting this validation. For example. after saving new generated excel file we want all status to be set as "not started" at beginning ???

[chihathimy]

Can you help me? I have code sample of your but don't work validation.

import openpyxl
from import DataValidation

wb =
sheet = wb.active

valid_options = '"Not Started, In Progress, Completed"'

rule = DataValidation(type='list', formula1=valid_options, allow_blank=True)

rule.error = 'Your entry is not valid'
rule.errorTitle = 'Invalid Entry'

rule.prompt = 'Please select from the list'
rule.promptTitle = 'List Selection'


# Add the data-validation object to the worksheet


rule.add('C2:C100')

wb.save('file.xlsx')