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

[@thecriticalpoint]

Thank you for knocking down barriers to python automation in excel. I've seen some other videos where a guy was doing all kinds of wacky stuff to address cells. I'm grateful that you do what you do.

[@santoshgujar5237]

Thank you, Sir, you teach Best, Thank you, Sir, please make more videos on python excel, Thank you, Sir

[@vaishnavividyapati6123]

Hello. Great video I am getting type error for formula of subtracting two col values..how can I resolve?

[@setsototitipana8433]

Great content man, love your work. Would it be possible to access some of the feature of excel like goal seek for example using openpyxl or any other python module?

[@AndreMalanUK]

Great easy to follow video. Very helpful for something I am trying to automate with python and excel. Thank-you!

[@python-bits]

Glad it was helpful!

[@ismahenelarbi5403]

Thank you for this helpful tutorial. Is there a way I can get the result of the formula in python (as opposed to the formula itself ?)

[@gandharvtikhe6345]

Superb this is very helpful... can you please tell how to copy paste with formats?

[@venkatrayapai7112]

Excellent boss

[@python-bits]

Thanks!

[@ravivarma5499]

nice explanation thank you

[@SandeshMahankal]

Truly Appreciated. It's really really Great. Sir, I've one question, Please let me know. I am working on Jupyter Notebook Only. So How do I perform the same thing on Jupyter? Please Please Let me know Sir. Thanks for your Support.

[@python-bits]

Thanks! Jupyter is just a fancy version of python terminal. It allows you to execute chunk of code instead of whole file. Start writing line(s) of code and execute them; it should run as long as they are in order (i.e., make sure to call methods/variables after they're defined or imported).

[@PA_Offical]

Great video. But i input formula to MS-Excel . because value after input to excel is "str" . Formula not active. can y help me !!

[@avshekraj]

Wow, so simple and crisp. Subscribed. 🎉 How can we do this same thing using pandas? I wish I knew this earlier to clear the interview, I got rejected in.

[@python-bits]

Thanks and welcome

[@surendarsakthivel38]

My excel has some formulas in it. When I read the excel values using openpyxl, it reads the formula instead of values. Please suggest a way to read values instead of formula.

[@abhisheksinghrajput4022]

Did u find any solution 👀

[@surendarsakthivel38]

@@abhisheksinghrajput4022 Nope.. I changed my entire approach instead !!

[@abhisheksinghrajput4022]

@@surendarsakthivel38 u can use Excel macro to copy paste the cell by value and run macro from python that would help

[@python-bits]

You can apply data_only=true filter to exclude formula. Ex: openpyxl.load_workbook('sample.xlsx', data_only=True)

[@nagendravishwamitra3652]

Sir how to create vlookup formula between 2 worksheets using openpyxl

[@python-bits]

Will upload new video on this.

[@LeonaMeyer]

Very good video. But when I run this I get error: AttributeError: 'tuple' object has no attribute 'Font' (if I make my headings of totals all bold) ie [A8:A9]

[@champak_chacha251]

How can i remove formula =SUM(A1:A10) from excel using python?

[@pradeepmanney6299]

How to print the average value to screen.. i.e .. wb[B12]

[@python-bits]

You can do something like this: cell_values = [] for row in ws['B1':'B12']: for row_val in row: cell_values.append(row_val.value) avrg = sum(cell_values)/len(cell_values) print(avrg)

[@congaibui1270]

Hi sir, I'm very grateful for your sharing, but I want to ask why should I use python instead of excel? The formula in excel is dynamic. If I insert a column between col A and col B after I wrote the formula, the excel formula still works, the python formula will not work (I think)

[@python-bits]

Ideally, this kind of automation should be used for repetitive work. For example, if you have to merge two excel files on daily basis then automation is best option.

[@StefanoVerugi]

As a python beginner, and long time Excel user, I find the code.py approach a better way of showing what happens inside the worksheet, both for colleagues and myself at later stage. If incorporated in a Jupyter notebook you can almost avoid Excel altogether for your reports, the learning curve to achieve it is not too steep

[@mohanannadurai8611]

Thannnqqqqqqqqqqqq✨