How to Automate Excel Formulas using Python. Openpyxl Tutorial #9

hello everyone in this video we will learn how to automate formula in excel using open pi excel so as always first we need to import open pi excel and create our workbook and worksheet object let's run python command once and make sure it's working and we get no error means everything is fine let's quickly go back to our xlc and what i would like to do here is create a sum and average for this b column so manually we can do using some function we can say sum here we can select the cell that we would like to include in sum and complete the bracket hit and enter so this way we can get the sum similarly we can get the average using average formula select cells hit and enter and that's how we can get the sum and average manually we can automate this using open pi excel all we need to do is target that specific cell where we want to create a sound and insert the formula we can say ws and i want to create a sum in this b11 column i'll say target that be 11 here i simply need to pass the same formula we use the formula is equal to sum b2 colon b9 i can even simply copy this formula and paste that in here once again we are making this change in actual sheet so we need to run a save command i can run my python file again and go back to our excel sheet and you can see we have created the sum function here without sending manual comma similar way we can create average for this column so to do that we simply have to pass that location so we want to create that in b12 cell i will pass here b12 and the formula so i'll say average from b2 and go all the way till b9 so let's go back to excel file i'll close this one and reopen and you can see we get the average b2 to b9 why is that oh because we missed here to put the equal to sign so if i put the equal to sign again and read in my python program let's go back there close this one and reopen excel file you can see we get the average of this column and i can simply double click and see that it's actually render as an actual formula sometime we have to work with user defined formula so let's work on that scenario for instance here i have balance and interest rate column and what i want to calculate is total balance after one year so how it will calculate it is first we have to get the balance and multiply that with interest rate and we will get the interest after one year and we will need to add that interest into our actual balance so final value total balance after one year will be 12 12 and the formula for this will be a balance into interest rate plus balance see how we can automate this scenario in excel and i will copy this for reference so we can use that in our python file now let's paste that formula here first we will create column header so let's go back to our excel file real quick we want to use d column add our header in d1 cell i can simply use ws d1 name it balance after a year i can save my actual seat now in our python command i made a mistake here it's supposed to be wb let's clear that and rerun our python program no errors so if i go back and reopen our excel files we can see new column header here if you see other column headers are in bold so i want to make this bold and we can do that using open pi excel font styling that we have seen in other video first we need to import style module from open pi excel dot styles import font and first let's define a font style so i'll say font we just want to make it bold so i'll say bold equal to true and i can directly assign to d1 i'll say here ws d1 dot font equal to this font hole let's rerun our python file one more time no error let's go back and open actual seat again and you can see this has been bold also like this font style is applied to cambria but for all other it's a real so let's apply that as well in this font i can simply say name equal to arial i can read in my python file again close this one reopen and you can see that is applied one more change we need to make is font size so all other headers are 10 we can make this 10 as well by simply adding size attribute and set it at 10. so next time when we run our python command the font style for d1 will be set to bold font style will be arial and size will be set to a 10. we can use our for loop that we used in our other video with the range function to target d column so i will say for i in range and we want to target from d2 till d9 so we can start from 2 and go all the way till 9 but we have to include 9 so we have to specify here 9 plus 1 so it's 10 and here i can say print i to make sure our formula is working and you can see we are getting i value printed from 2 to 9. now let's work on our actual code this is our formula so first we need to get this balance column and it's in a b column we'll start from b 2 and get value all the way till b9 so we know how we can do that and directly say ws dot cell here specify the row we want to start so we want to start at 2 column we want to target b column so it's a 2 and here we can specify dot value we don't want to just get value for second column we want to get from second column all the way till 9 columns so we can use i here and save that in variable we will call this balance let's make sure this is working so i will print my balance variable and if i redone my command you can see we are getting values for b column so it start from 880 to 1500 can see that here the next step is to get the interest column we can do similar way here all we need to do is replace the column number for balance we were getting second column for interest we have to get third column here column number three everything remains same and i will rename my variable interest thing we need to do according to our formula multiply balance with interest then add the balance we can do that simply is balance into interest that's at the bracket here to be sure again add to a balance and i will assign a new variable to this final valence we have to append this final balance to this d column we can do that simply by using again same logic but this time instead of extracting we will assign a value so i will say wsl row will remain i and this time column will be 1 2 3 and 4. so column number is equal to 4 value i want to assign is final balance after this value is finalized we need to send w dot save command let's close our excel sheet and re-run our python command now error let's go back to our excel file and you can see here we get d column populated with the value that we specified according to our user defined formula so that's how you can create custom formula and add the data back to excel thank you for watching and i will see you in another video

Channel: Python Bits

Views: 16,033

Keywords: python tutorial, python programming, learn python, python for beginners, read excel in python, write excel in python, excel read and write in python, openpyxl tutorials, read excel files in python, python how to read microsoft excel files, openpyxl, microsoft excel python, excel python, python xlsx, python openpyxl, openpyxl tutorial, automate excel formula, excel formulas

Id: JjeSXWIX9SY

Length: 8min 47sec (527 seconds)

Published: Mon Jan 18 2021