Automate Excel With Python - Python Excel Tutorial (OpenPyXL)

Рет қаралды 1,575,577

Күн бұрын

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 KZfaq 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

Пікірлер: 612

@TechWithTim 3 жыл бұрын

Start a high paying tech career making $60k+/year with NO DEBT: coursecareers.com/a/techwithtim

@victor20214 3 жыл бұрын

And support. Thanks tim

@realitynowassigned 2 жыл бұрын

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? :)

@derekwest8636 2 жыл бұрын

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!

@lilrex2015 2 жыл бұрын

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.

@NextManu 2 жыл бұрын

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..

@gl4989 3 жыл бұрын

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

@chaitanyakolluri2839 2 жыл бұрын

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.

@mrCetus 2 жыл бұрын

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.

@dmitryg9613 Жыл бұрын

man, I really appreciate your work and time spent on explanation. I would never sort it out without your help

@preisback24 2 жыл бұрын

I've been learning Python by following a book and got confused and stuck for so long until I found this video. Thanks

@jewelsoncagakit8423 6 ай бұрын

Wow ! All the basic fundamentals are packed in one demo ! Thanks Tim !

@VladArnoldner Жыл бұрын

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.

@relhage100 2 жыл бұрын

Clear, rich, concise and well delivered. Thanks!

@simoneriksson548 2 жыл бұрын

Awesome video Tim! Ive struggled a bit with python and definently had some 'aha!' oments watching this! Keep up the good work!

@deanlawson6880 Жыл бұрын

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

@omuk4584 2 жыл бұрын

Who the heck is this guy!! Just amazing trainings and super easy to follow along. Keep them coming Tim, much appreciated.

@manndipp Жыл бұрын

by far, the best video for starting excel with python

@addledanorak8297 3 жыл бұрын

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 ;)

@realitynowassigned 2 жыл бұрын

Thank you for the install error explanations. Thats something most videos lack

@CarlFritz24 Жыл бұрын

AMAZING, Tim. The best demonstrated matter I have ever seen.

@MaxRuso Жыл бұрын

the posibilities and list of errors is so good as it helps

@ianbraganza3065 2 жыл бұрын

Thanks for a great video. It helped me automate several redundant tasks and opened my mind to the power of openpyxl

@boldtecmedia3003 2 жыл бұрын

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!

@MrSuspicious0 2 жыл бұрын

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.

@blackchristiangeek 2 жыл бұрын

Awesome tutorial and my only frustration is time limitations to apply all that you have shared. Thanks, and God bless

@zukofire6424 Жыл бұрын

this was very helpful for my internship (at a messy start up)! Thank you so much Tim!!

@tridibbiswas3361 3 жыл бұрын

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

@akankshc5707 3 жыл бұрын

this is a whole another lvl. of automation with python

@sandragraham8289 Ай бұрын

This strategy is bomb!!! I won 3/4 just testing it out!!! Thank you for sharing!!

@pythonenthusiast9292 3 жыл бұрын

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 .

@user-id5wb1or2y 9 ай бұрын

Clear, rich, concise and well delivered. Thanks!. Clear, rich, concise and well delivered. Thanks!.

@rishabhtripathi593 Ай бұрын

Thank you for this, it helped me a lot for an college assignment < 3

@tenshi_tries 3 жыл бұрын

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

@Mr.Blumister 2 жыл бұрын

Bruh! You literally made me safe countless hours of work at my job! Lovin' it

@TechWithTim 2 жыл бұрын

Glad I could help!

@cabechomp Жыл бұрын

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

@123456789mumbai Жыл бұрын

Thanks you so much for explaining in so detail and easiest way. Thanks a lot ❤

@mrtyecee Жыл бұрын

Your videos are straight 🔥🔥🔥🔥 as a programming newbie I'm learning alot from you. Thanks for all you do!

@murilosilvestre7736 2 жыл бұрын

This video was a blessing to me, Thanks very much!

@titojulyharyanto3397 7 ай бұрын

Thank you so much Tim for sharing this. God bless you and all the best for you

@robertlaplante8812 Жыл бұрын

Great video! It helped me a lot starting in Python! Many thanks!

@shinichixxxx 2 жыл бұрын

Crystal clear. Thank you.

@Mario3cush 2 жыл бұрын

Great video. Super useful. Thanks, Tim!

@aungphyo9237 2 ай бұрын

The best video about openpyxl. It is helpful for me a lot.

@berghoutcful 2 жыл бұрын

Great work! Thank you for this! Subscribed!

@15chris45chris Жыл бұрын

Nice video! Great for a beginner to get comfortable with Python and Excel

@cantwaitforit 2 жыл бұрын

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.

@diminati8659 3 жыл бұрын

As usual, very useful. Thank you!

@roccov3614 3 жыл бұрын

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.

@thinula5635 2 жыл бұрын

Best tutorial about Excel automation. Thanks, Sir

@1991gsn 2 жыл бұрын

That now kinda useless with power automate desktop built in windows 10/11

@sinaalizadeh1 Жыл бұрын

Thank you for your great explanations ❤️

@trakinas7304 2 жыл бұрын

Thanks for your video, you helped me a lot in my work :)

@strydomobile 2 жыл бұрын

You are really talented in carrying information across. Thanks for this video.

@TechWithTim 2 жыл бұрын

Appreciate the comment :)

@AnilKumar-bh4vt 3 жыл бұрын

Using python for sheets is very helpful,I use it on a regular basis

@sharongur64 Жыл бұрын

Most educational thank you Tim !

@kychemclass5850 2 жыл бұрын

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.

@hemalpatel9708 Жыл бұрын

It helped for creating ustom excel file from python! Thanks a lot

@ameyanerlekar9101 2 жыл бұрын

tim really is great! thanks for the video, really informative :)

@KaosDragonLOC 11 ай бұрын

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

@user-fz7db4ls3i Жыл бұрын

dude. this is sooooooo helpful, thank you

@unpatel1 2 жыл бұрын

Thank you for this great intro!!

@capincrunch1184 3 жыл бұрын

Bruhhhh needed a video like this 4 months ago

@someone...000 Жыл бұрын

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☺

@rostamr4096 3 ай бұрын

wow...this is so very helpful...Thank you

@johannesperez9548 2 жыл бұрын

This is great! Thanks for sharing!

@dariorodrigotorrejon4004 2 жыл бұрын

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

@Balgalw Жыл бұрын

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

@pyvfx 3 жыл бұрын

tnks tim its very helpfull for me. lov u man

@haikamu7178 Жыл бұрын

Very good, now i'm not tired for some accounting work.

@joeyzalman8254 Жыл бұрын

very clear thank you!

@duzyolek 3 жыл бұрын

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.

@hubertkarwowski1646 2 жыл бұрын

The best tutorial video about python excel I have ever seen $.$

@mrpancakes 3 жыл бұрын

hey I remember asking you about openpyxl several months ago :)

@expat2010 Жыл бұрын

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 8 ай бұрын

So surreal reading how a guy called yugiohfanatic is calling someone else a simp. The internet is a beautiful place. @@yugiohfanatic1964

@nickyfranshel1210 3 жыл бұрын

pains of suffering of reading stackoverflow of excel automation and alien language has come to an end 😌

@eshanwijetunge2518 2 жыл бұрын

Thank you for your Great Session

@dlo009 2 жыл бұрын

Thks for the intro. Great vid.

@milesgardner9988 2 жыл бұрын

Wow. Thank you so so much for this.

@279momo 2 жыл бұрын

Thank you so much from MO in Bangkok Thailand :)

@MrJonas1995 Жыл бұрын

Great video! Well explained!

@ahmedsoliman779 Жыл бұрын

Amazing Work

@startjourney8631 3 жыл бұрын

Am your new subscriber 🤝✨♥️your content and presentation style is 😘👌

@vigneshsrinivasan3914 Жыл бұрын

Great work. Good to get started.

@seamushand8439 9 ай бұрын

Thanks Tim. What are the advantages in using Python rather then VBA ?

@joen5000 3 жыл бұрын

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

@Mio-ev2iz 8 ай бұрын

Thank u so much for the video!

@craiglobo2165 3 жыл бұрын

Ive used this library before its sooooo good

@real_achimedes 3 жыл бұрын

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.

@deadchannel7637 3 жыл бұрын

Love your vids bro ❤️❤️

@Kitejrpaladin 2 жыл бұрын

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)

@user-jt6uu1ru7s 2 жыл бұрын

Simple and cool 👍🏻

@vandrujemesk8651 Жыл бұрын

Thanks, super video tutorial.

@DanStone1025 2 жыл бұрын

Tim, thanks for sharing your knowledge. Question for you (or any of your other followers if they care to help.)!!! How would you set a cell alignment to center. I have looked through the "working with styles" in the help files, but cannot get the alignment to work. Any help would be great.