[Python] Update formulas in excel using Python

I am trying to update formulas in an existing excel which has data and tables but I am failing to update the formulas so that they would update the data for example:

=UNIQUE(Table1[Names])


I tried to leave it in excel and update it, also tried to enter the formula to a cell but either @ appears or formula fails:

sheet['A1'].value = '=UNIQUE(Table1[Names])'


Also, I want to create a dropdown list using Python:

dropdown_range = "'Data'!A1:A10"
dv = DataValidation(type="list", formula1=dropdown_range, showDropDown=True)

target_sheet.add_data_validation(dv)
dv.add(target_sheet['B3'])


But that fails corrupts the excel file. Any help would be great.

Continue reading...