Automate Excel With Python - Python Excel Tutorial (OpenPyXL)
The first 1,000 people to click this link will get a free career coaching session courtesy of Career Karma: 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: openpyxl.readthedocs.io/en/st...
Code Written in This Tutorial: github.com/techwithtim/ExcelP...
Fix Pip (Windows): • How to Install Pygame ...
Fix Pip (Mac/Linux): • How to Install Pygame ...
⭐️ 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: tech-with-tim.teachable.com/p...
👕 Merchandise: teespring.com/stores/tech-wit...
🔗 Social Medias 🔗
📸 Instagram: / tech_with_tim
📱 Twitter: / techwithtimm
⭐ Discord: / discord
📝 LinkedIn: / tim-ruscica-82631b179
🌎 Website: techwithtim.net
📂 GitHub: github.com/techwithtim
🔊 Podcast: anchor.fm/tech-with-tim
🎬 My KZread Gear 🎬
🎥 Main Camera (EOS Canon 90D): amzn.to/3cY23y9
🎥 Secondary Camera (Panasonic Lumix G7): amzn.to/3fl2iEV
📹 Main Lens (EFS 24mm f/2.8): amzn.to/2Yuol5r
🕹 Tripod: amzn.to/3hpSprv
🎤 Main Microphone (Rode NT1): amzn.to/2HrZxXc
🎤 Secondary Microphone (Synco Wireless Lapel System): amzn.to/3e07Swl
🎤 Third Microphone (Rode NTG4+): amzn.to/3oi0v8Z
☀️ Lights: amzn.to/2ApeiXr
⌨ Keyboard (Daskeyboard 4Q): amzn.to/2YpN5vm
🖱 Mouse (Logitech MX Master): amzn.to/2HsmRDN
📸 Webcam (Logitech 1080p Pro): amzn.to/2B2IXcQ
📢 Speaker (Beats Pill): amzn.to/2XYc5ef
🎧 Headphones (Bose Quiet Comfort 35): amzn.to/2MWbl3e
🌞 Lamp (BenQ E-reading Lamp): amzn.to/3e0UCr8
🌞 Secondary Lamp (BenQ Screenbar Plus): amzn.to/30Dtafi
💻 Monitor (BenQ EX2780Q): amzn.to/2HsmUPZ
💻 Monitor (LG Ultrawide 34WN750): amzn.to/3dSD7tS
🎙 Mic Boom Arm (Rode PSA 1): amzn.to/30EZw9m
🎚 Audio Interface (Focusrite Scarlet 4i4): amzn.to/2TjXsih
💸 Donations 💸
💵 One-Time Donations: www.paypal.com/donate?hosted_...
💰 Patreon: / techwithtim
◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️
⭐️ Tags ⭐️
- Tech With Tim
- Python
- Excel
- Python Tutorial
- Python and Excel
- Automating Excel
⭐️ Hashtags ⭐️
#TechWithTim #PythonExcelTutorial
Пікірлер: 614
Start a high paying tech career making $60k+/year with NO DEBT: coursecareers.com/a/techwithtim
@victor20214
3 жыл бұрын
And support. Thanks tim
@realitynowassigned
3 жыл бұрын
What kind of keyboard are you using. I miss those clicks
@anandsharma2516
2 жыл бұрын
I think some bug is there not accepting the mobile number or land line no. with & without the city code and country code. Or a fake one
@ardit9988
2 жыл бұрын
🇬🇧🇽🇰🌟🌟
@Bart_Depestele
2 жыл бұрын
Did you love yourself amigo? :)
I am working as a Senior Financial Officer dealing with financial reporting. This video gives me a new powerful tool to quickly manipulate and process a large volume of data. I think I will surprize my boss with my new skills! Thank you, Tim!!!
@SwahiliSpicE
Ай бұрын
I'm a tax accountant, this is exactly what I've been doing for the past couple of years. Bringing in so much value to the business, and plugging the cash leakages using coding skills, you get taken more seriously.
Boomers at my work will now treat me as a god
@salsabilahmedshrestho960
2 жыл бұрын
I am at my final year, and just got to know about automation and how seniors don't know it. :v I also wanna feel how you said 😂
@girishbhoite264
2 жыл бұрын
🤣
@mienzillaz
2 жыл бұрын
No, they won't
@videofandude99
2 жыл бұрын
for a quick minute until they start doing themselves to save their jobs
@Mr.Blumister
2 жыл бұрын
Actually it is exactly like that! :D
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..
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!
This is quality stuff. This guy gets how to make a good tutorial video. No wasted time, points are explained enough to understand then moves on.
exactly what i needed, im a civil engineer and learning python. i was coding a building cost estimator desktop app in tkinter and needed python after calculating all materials for me, it should also create an excel workbook and type those results for me. so that i simply print out for the client in the shortest time possible. awesome video. thank you sir. im a complete beginner though, any one with a better way of doing it is welcome
@tonyvelasquez6776
2 жыл бұрын
Python is probably the best way to do that. I don't think you necessarily need tkinter though
@furkankilicaslan
2 жыл бұрын
Hi, I am an industrial engineer and currenly looking for ways to automate the process of some report that we do in excel. tkinter might be a good idea for people that don't have the expertise or the knowledge of python cause they are just going to use the interface. hope you get what you want. Really appreciate if you reply the result if you finish the project. You should try KNIME too. Good luck. Cheers!
Hands done one of the best Python training videos out there I’ve. Method of instruction, clarity and examples are brilliant. Thank you so much.
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.
man, I really appreciate your work and time spent on explanation. I would never sort it out without your help
Wow ! All the basic fundamentals are packed in one demo ! Thanks Tim !
I've been learning Python by following a book and got confused and stuck for so long until I found this video. Thanks
For anyone like me who finds the lettering system tedious and annoying, rather than using the get column letter function you can just use sheet.cell(1,1) for example which would be cell A1, but I think this is nicer especially if youre using nested for loops.
@ignoble_hierarch
Жыл бұрын
Thank you! So, to stick more to the tutorial, it will be like *ws.cell(1, 1).value = "Type something"*. By the way, you have a similar reference system in VBA as well.
@PolytecGuy91
Жыл бұрын
thats the way to handle columns greater than "26" also.
Who the heck is this guy!! Just amazing trainings and super easy to follow along. Keep them coming Tim, much appreciated.
Excellent run-through of this. Very cool to be able to interact with Excel spreadsheets in Python! Thanks for this - Nicely done!
@scoopiezz
10 ай бұрын
i liked ur comment cause it had no likes. It a lonely comment
to those who are thinking @6:52 that you can directly open the workbook or directly save the workbook using that load_workbook command.. no ppl.. you need to first make or upload a workbook at the location of your text editor or some random location then take that location from there .
Awesome video Tim! Ive struggled a bit with python and definently had some 'aha!' oments watching this! Keep up the good work!
Clear, rich, concise and well delivered. Thanks!
AMAZING, Tim. The best demonstrated matter I have ever seen.
by far, the best video for starting excel with python
Was waiting for such a vid for soooo looooong. Thx Tim :)
@UltraProbe
3 жыл бұрын
Haha ikr. I learned a bit of Openpyxl for a script I run everyday, but this should let me do some more interesting stuff ;)
Clear, rich, concise and well delivered. Thanks!. Clear, rich, concise and well delivered. Thanks!.
this is a whole another lvl. of automation with python
the posibilities and list of errors is so good as it helps
Thank you for the install error explanations. Thats something most videos lack
Bruh! You literally made me safe countless hours of work at my job! Lovin' it
@TechWithTim
2 жыл бұрын
Glad I could help!
I have a work that recently needed this--- JUST IN TIME! thank youu
@Salmankhan-qt8uy
2 жыл бұрын
Can u help me with the scenario u have in ur work. Thanks
this was very helpful for my internship (at a messy start up)! Thank you so much Tim!!
Awesome tutorial and my only frustration is time limitations to apply all that you have shared. Thanks, and God bless
This strategy is bomb!!! I won 3/4 just testing it out!!! Thank you for sharing!!
Thanks for a great video. It helped me automate several redundant tasks and opened my mind to the power of openpyxl
Thanks you so much for explaining in so detail and easiest way. Thanks a lot ❤
Thank you Tim for the video. Its helpful for people like are in the process of picking up the rudiments of python. Could you do a video about OpenPyXL + Pandas. How these two work together and what are the pros and cons of using openpyxl and pandas on their own
Great video! It helped me a lot starting in Python! Many thanks!
This is a good example of why I love Python even more than VBA. It is so intuitive and readable compared to most other languages.
@yugiohfanatic1964
Жыл бұрын
totally false statements. Nice try simp!
@dantherman4202
9 ай бұрын
So surreal reading how a guy called yugiohfanatic is calling someone else a simp. The internet is a beautiful place. @@yugiohfanatic1964
This video was a blessing to me, Thanks very much!
Nice video! Great for a beginner to get comfortable with Python and Excel
Thank you so much Tim for sharing this. God bless you and all the best for you
Great work! Thank you for this! Subscribed!
Your videos are straight 🔥🔥🔥🔥 as a programming newbie I'm learning alot from you. Thanks for all you do!
Crystal clear. Thank you.
Great video, it helped me a lot with a project that i have and i only knew how to work with CSV files that didnt helped me at all. Thank you
Thank you for this, it helped me a lot for an college assignment < 3
Cool video! I didn't think that it is so easy(thanks man who make this library). After this video, it's obligatory to subscribe.
Great video. Super useful. Thanks, Tim!
Thank you for your great explanations ❤️
The best video about openpyxl. It is helpful for me a lot.
wow...this is so very helpful...Thank you
Most educational thank you Tim !
As someone who loves Excel and is learning and loving python, I think this module is awesome.
@CrypticConsole
3 жыл бұрын
Than look at pandas
@roccov3614
3 жыл бұрын
@@CrypticConsole I new pandas dealt with data. I didn't know it could access Excel docs. Thanks.
Thanks for your video, you helped me a lot in my work :)
Best tutorial about Excel automation. Thanks, Sir
@1991gsn
2 жыл бұрын
That now kinda useless with power automate desktop built in windows 10/11
For anyone reading there are random real-world use-cases for stuff like this on occasion, I recently did something similar with Word and Powershell. In my case, I would take data from a CSV and generate a document containing stuff like login information, Employee IDs, etc. It can save time when you are doing 25+ of these documents.
@michaelmessiha1281
2 жыл бұрын
why powershell?
@davewiebe2582
2 жыл бұрын
I use powershell alot, it does alot of the same things python does and it's integrated into the OS
@Kitejrpaladin
2 жыл бұрын
Pretty much for the reasons Dave described. When in Rome, do as the Romans do. For general purpose stuff like this in Windows environments, PowerShell is the better tool for the job, not because it's better than Python or anything silly like that but because it's the lingua franca of Windows and has plenty of cmdlets to deal with the tasks you'll run into in a Windows environment but just as importantly the people I work with have a better chance of understanding my code if it's in PowerShell vs Python. For a more specialized task maybe I dip into the .Net APIs using PowerShell (use the native PowerShell solution first as best practice) or pull out C# in a Windows Environment. To choose an example where I would definitely prefer Python is anything in the Data Analytics or Machine learning world. On the operations side if you ever need to deal with networking equipment, the community has built some really good libraries for automating working with routers and switches. Network Automation and SDNs are a thriving space in Python. I actually tooled around with some Unifi stuff in Python while trying to automate performing resets of multiple Unifi Access Points. (Didn't really work out but learned a bit)
dude. this is sooooooo helpful, thank you
You are really talented in carrying information across. Thanks for this video.
@TechWithTim
2 жыл бұрын
Appreciate the comment :)
pains of suffering of reading stackoverflow of excel automation and alien language has come to an end 😌
very clear thank you!
It helped for creating ustom excel file from python! Thanks a lot
Very good, now i'm not tired for some accounting work.
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.
@contraplano3157
2 жыл бұрын
Libreoffice Rocks
Thank you for this great intro!!
Bruhhhh needed a video like this 4 months ago
As usual, very useful. Thank you!
I expected this to be as easy as working with the csv module, but obviously it isnt, and now that i think about it, why would it be. Nevertheless, this video help me understand it even more than i understand the csv module, from installing OpenPyXL, to formulas and cell styling. Great video Tim! Thanks for all that you do for us python noobies☺
Great work. Good to get started.
Using python for sheets is very helpful,I use it on a regular basis
Tim, thanks a lot for detailed video. Simply the best way to use Excel formulas with get_column_letter, can't figure it out myself
Great video! Well explained!
Thank u so much for the video!
This is great! Thanks for sharing!
thx for share your knowlage, I want to learn python and this is so motivating for me....I love programming and learn new things...so again thx very much!new sub here
Ive used this library before its sooooo good
I loved the video and would love a tutorial doing the reverse of the last project showing how to take a workbook and use that information to create a dictionary out of it
tnks tim its very helpfull for me. lov u man
The best tutorial video about python excel I have ever seen $.$
Tim. Could you do an equivalent video Automating LibreOffice Calc ?? It would be fantastic. I'd even give a reasonable financially contribution for the effort.
Thanks, super video tutorial.
Thks for the intro. Great vid.
Thank you for your Great Session
Thank you so much from MO in Bangkok Thailand :)
Great video Tim, thank you 👍
@real_achimedes
3 жыл бұрын
Would love more content on making figures in python, specifically line and dot plots that are commonly used in excel.
Amazing Work
Wow. Thank you so so much for this.
hey I remember asking you about openpyxl several months ago :)
tim really is great! thanks for the video, really informative :)
very nice tutorial, thanks
thanks i will come back for more i need to watch the full video. but yes i admit you helped me with what i wanted thanks brother
Am your new subscriber 🤝✨♥️your content and presentation style is 😘👌
I started the transition from VBA to python+OpenPyXL around 2 weeks ago (after years of experience with VBA) and there it is - Tim's video about this very topic!
@micheleluigimelis426
3 жыл бұрын
Why did you decide to change? What are the benefits of using Python in this case?
@Bayo106
2 жыл бұрын
@@micheleluigimelis426 way less code
@haroldgamarra7175
Жыл бұрын
@@Bayo106 not having to declare variables to begin.
Very well done 👌
Fastest way to get started. Let's go.
Very helpful!
I'm From Brazil!!!!! Perfect Video!!!!
Love your vids bro ❤️❤️
Great starter!
Simple and cool 👍🏻
A very good video. Thanks Tim. I think that you could have saved some time in this video, if you would have added a close, open excel file function within the Python code, and would have called it up each time you test your code. That way, it would close that particular file, save it and reopen it automatically without you needing to do it manually, as you did in this amazing video. Lemogan
@nocodenoblunder6672
2 жыл бұрын
Opening a file is easy obv but dont know how you close a file cause once its open the application itself is usually responsible for this
WOW , as good as always
So great! thank you!
Thanks! Great content!!