The first 1,000 people to click this link will get a free career coaching session courtesy of Career Karma: https://rebrand.ly/tech-with-tim-51321
Welcome to another video! In this video, I will cover how we can use python to automate Excel. I’ll be going over everything from creating workbooks to accessing individual cells and stylizing cells. There is a ton of things that you can do with Excel but I’ll just be covering the core/base things in OpenPyXl.
📄 Resources 📄
OpenPyXL Docs: https://openpyxl.readthedocs.io/en/stable/
Code Written in This Tutorial: https://github.com/techwithtim/ExcelPythonTutorial
Fix Pip (Windows): https://www.youtube.com/watch?v=AdUZArA-kZw
Fix Pip (Mac/Linux): https://www.youtube.com/watch?v=E-WhAS6qzsU&t=4s
⭐️ Timestamps ⭐️
00:00 | Introduction
02:14 | Installing openpyxl
03:19 | Testing Installation
04:25 | Loading an Existing Workbook
06:46 | Accessing Worksheets
07:37 | Accessing Cell Values
08:58 | Saving Workbooks
09:52 | Creating, Listing and Changing Sheets
11:50 | Creating a New Workbook
12:39 | Adding/Appending Rows
14:26 | Accessing Multiple Cells
20:46 | Merging Cells
22:27 | Inserting and Deleting Rows
23:35 | Inserting and Deleting Columns
24:48 | Copying and Moving Cells
26:06 | Practical Example, Formulas & Cell Styling
💰 Courses & Merch 💰
💻 The Fundamentals of Programming w/ Python: https://tech-with-tim.teachable.com/p/the-fundamentals-of-programming-with-python
👕 Merchandise: https://teespring.com/stores/tech-with-tim-merch-shop
🔗 Social Medias 🔗
📸 Instagram: https://www.instagram.com/tech_with_tim
📱 Twitter: https://twitter.com/TechWithTimm
⭐ Discord: https://discord.gg/twt
📝 LinkedIn: https://www.linkedin.com/in/tim-ruscica-82631b179/
🌎 Website: https://techwithtim.net
📂 GitHub: https://github.com/techwithtim
🔊 Podcast: https://anchor.fm/tech-with-tim
🎬 My YouTube Gear 🎬
🎥 Main Camera (EOS Canon 90D): https://amzn.to/3cY23y9
🎥 Secondary Camera (Panasonic Lumix G7): https://amzn.to/3fl2iEV
📹 Main Lens (EFS 24mm f/2.8): https://amzn.to/2Yuol5r
🕹 Tripod: https://amzn.to/3hpSprv
🎤 Main Microphone (Rode NT1): https://amzn.to/2HrZxXc
🎤 Secondary Microphone (Synco Wireless Lapel System): https://amzn.to/3e07Swl
🎤 Third Microphone (Rode NTG4+): https://amzn.to/3oi0v8Z
☀️ Lights: https://amzn.to/2ApeiXr
⌨ Keyboard (Daskeyboard 4Q): https://amzn.to/2YpN5vm
🖱 Mouse (Logitech MX Master): https://amzn.to/2HsmRDN
📸 Webcam (Logitech 1080p Pro): https://amzn.to/2B2IXcQ
📢 Speaker (Beats Pill): https://amzn.to/2XYc5ef
🎧 Headphones (Bose Quiet Comfort 35): https://amzn.to/2MWbl3e
🌞 Lamp (BenQ E-reading Lamp): https://amzn.to/3e0UCr8
🌞 Secondary Lamp (BenQ Screenbar Plus): https://amzn.to/30Dtafi
💻 Monitor (BenQ EX2780Q): https://amzn.to/2HsmUPZ
💻 Monitor (LG Ultrawide 34WN750): https://amzn.to/3dSD7tS
🎙 Mic Boom Arm (Rode PSA 1): https://amzn.to/30EZw9m
🎚 Audio Interface (Focusrite Scarlet 4i4): https://amzn.to/2TjXsih
💸 Donations 💸
💵 One-Time Donations: https://www.paypal.com/donate?hosted_button_id=CU9FV329ADNT8
💰 Patreon: https://www.patreon.com/techwithtim
⭐️ Tags ⭐️
– Tech With Tim
– Python Tutorial
– Python and Excel
– Automating Excel
⭐️ Hashtags ⭐️
The first 1,000 people to click this link will get a free career coaching session courtesy of Career Karma: https://rebrand.ly/tech-with-tim-51321 !!
Hi Tim , I am new to learning python could you please tell me from which playlist I have to start to watch video and how ahead to step by step .
Actually, this sort of scripting could be used to insert data and images and stuff.. from elsewhere, files, or folders of files and concatanating them into your .xlsx for presentation format.
How do you open the excel if you do not know the name of the excel file
Recently started learning python! Fantastic tutorial, keep up this great content! Never thought Python could automate so much. As a financial analyst I use excel every day. Now with this knowledge, I can automate tasks that are somewhat repetitive and save loads of time!.
Just had a problem with the last line of code, can't seem to get it to work. When entering the fonts code, it shows up as "TypeError: 'module' object is not callable".
Any ideas? here's my code:
for col in range(1,6):
ws[get_column_letter(col) + '1'].fonts = fonts(bold = True, color = "0099CCFF")
How does it know to iterate through rows? I know you say “for row in range” but the name row is arbitrary right? You coulda said “for i in range”
When you save a workbook, is there a way for the calculations in the workbook to be executed so that the next time you run it you get the newly calculated values based on any new inputs without actually opening the excel file. I have tried using the formulas library for this. But I am running into problems with complicated calculations. I was wondering if there was another way to do this. Thanks!
looks like you are in need of some rest …and sleep !!!!
YOU ARE THE BEST!!!!!!
Is it possible to write a code that prints selected worksheets in a particular order.? Like using the while loop?
Hi is it possible to install this module when using MS Visual Code? and how to? all CMD commands mentioned do not work, thanks
Hi Tim, sorry if this is kinda silly question. it is possible if openpyxl to read multiple file in a folder. Process a file, close process and process next file?
Awesome tutorial and my only frustration is time limitations to apply all that you have shared. Thanks, and God bless
Who the heck is this guy!! Just amazing trainings and super easy to follow along. Keep them coming Tim, much appreciated.
Can we check the cell whether cell is protected or editable?? Using python?
Why we have to use python to control excel, we can control excel within excel itself?
Hello teacher, may i ask a question , im looking for something like VBA with python, can we do that? writing macros for excel with python?
Hanzhen harmonic drive gear , robot arm joint , over 30 years experience ,
@Tech With Tim, for the first for loop, for row in range(1, 11) how does the compiler know youre trying to loop through the worksheet?
Fastest way to get started. Let's go.
Excellent.But I want to know if there is any way to insert a cell instead insert a row ?. Can you help me. Thank you
What’s the point of openpyxl if you can do all of this with pandas
Great video, but this does not automate excel with python, the title is misleading.
YO need help after I install like you said but when I run the import part it says there was no openpyxl so my head crack to see any trouble
New to python and starting my own project working with excel files. Tried to go through the documentation for openpyxl and was wasting alot of time learning unnecessary information based on what I was trying to accomplish. You've done a wonderful job with this and I was able to easily follow along and experiment along the way. Put together a py file for my notes on this module. Tim is Great!
how to append one (new)data frame to the excel sheet with OpenPyXL.Please let me know
recently got into learning python.
my mind is blown with the versatility of python. I now wish i had done software engineering back in school..
There is no AVG function in that library?
Getting errorin line wb = load_workbook('FILE.XLSX')
As Tim noted, you cannot save the spreadsheet changes if it is opened in Excel (or in my case, Libre Office) for 'normal' use.
I did figure a sort of workaround to this problem.
If you open the spreadsheet as read only, your python code will run without a problem. Now to view the changes simply reload the spreadsheet.
The reload command in Libre Office can be found under File >>> Reload (or press the keys Alt, f, l in that order(Note l is lowercase L))
Yes, you have to remember to reload the spreadsheet every time you make a change, but it is quicker than the close, reopen way, you could put a reminder in your python code.
Note, if you want to edit the read only open file, there is a button on the top right that says Edit Document. but remember after editing it needs to be saved and reopened as read only.
Use the python command try when doing a workbook save, and if it throws and exception, then ask the user to go through the close/reopen read only routine.
i tried this in google colaboratory and i couldnt make it work! 🙁 could someone help me?
im trying to delete all the . (dots) from a specific excel row
you can make a video how to takes videos from instagram and upload them to tiktok automaticly. Like 100 videos per day. It is possible?
Hello, plz I need help!
I got a table that got a 3 lines and 4 columns (like what's shown)
C D R
A 0 0 F – F
B 0 0 F – F
I want to automatically create other tables which will rewrite , the "0" into "1" , one by one , writing every table/possbility
also in the "R" row , for each "0" there is an "F" and if there is a "1" we write a "T" ; following the order from left to right ; so the lines can have :
| 0 | 0 |F – F |
| 1 | 0 |T – F |
| 0 | 1 |F – T |
| 1 | 1 |T – T |
I am completely a newbie in excel and I need this for a homework , plz help
I think the "R" row needs variable values , do they even exist in excel ??
Pretty Please take a piece of your time to explain it to me , thx!
BTW, the "T" and the "F" are not logic functions , thx !!
PS: I got excel 2019
So with this, you can very easily store all json data in an excel file
This is the sheet
Actually did something similar a month ago, where I converted the excel file in to base64 in the end and sent it through an API to then generate it back into xlsc format through typescript.
PyXl is amazing btw, anyone who watches this should try it. Very easy to use.
I think using sheet.cell(row = r, column = c) is also good, rather than converting the column number to a character, also I think the reason that Grades.xlsx didn't show any data is because you used ws.active, but at the time, the active sheet was an empty one.
I've been learning Python by following a book and got confused and stuck for so long until I found this video. Thanks
Thanks for your tutorial, I ran into a problem when I was automating my excel file, I want to fill another column ( date column ) according to the previous column so that it is filled with a condition. How can I do that? Thanks in advance.
why are you looking younger in each new youtube video?
this data is json you can simple use pandas and do it in 2 lines of code: df = pd.read_json("data.json") , df.to_excal("NewGrades.xlsx")
Need to upgrade my pip on a Mac. Anyone done this before ?
Please upload at 30 frames per second to reduce bandwidth. Great video though.