Integrate Python with Excel – from zero to hero

Sharing is caring!

Last Updated on July 14, 2022 by Jay

LAST UPDATED: Nov 08 2021

When I first started this blog, I had one idea in mind – to share my knowledge on how to integrate Python with Excel; and to help others improve their work efficiency. So I’m kicking off a series of short articles to help you learn the skills to power up your Excel. This post is a “living and breathing” summary to help you find tutorials easily, as new tutorials become available, they’ll get added to this post (scroll down to see).

The goal is to help you learn how to use Python to take complete control of Excel, or replicate what Excel can do. Why? Because the power of automation will save you lots of time and energy.

Who is this series for?

If the below sound familiar, you might benefit from reading this series.

• Uses Excel everyday for work & personal projects
• Your work involves lots of data and complex calculation
• Frustrated with how slow Excel is, and google “how to speed up Excel”
• Wants to automate the boring things away
• Works on Excel files > 100MB
• Thinks VBA is outdated

Of course, that’s not the full list… feel free to share your frustrations when working with Excel in the comment section below!

New series & new challenge

I will publish a short article each day for the next 30 days, which is a new challenge for me! Each day I’ll be covering how to use Python to achieve a certain task in Excel, data extraction, querying, macros, PowerQueries, you name it.

Quick Bio

For the new readers, my name is Jay and I have about a decade of experience working in Excel and other MS Office tools. I started learning Python around 2017 to automate some of my personal needs. I fell in love with the language and quickly applied what I learned to the work environment. Python has made my life so much easier in many aspects, it enabled me to:

• Automate a lot of manual processes. One of which used to take 2-3 days to complete by a coworker, now it can be done within minutes.
• Do Excel-impossible things, such as processing multi-million or even billion rows of data.
• Get any data off of the Internet while I sleep.
• Automatically send me an email and a text message on a store’s sales (side business from a family member) at the end of each day.

At the same time, you probably can already tell, that I enjoy coding in Python a lot, which also brought a lot of fun into my life. The Python programming language has an awesome community that believes in open source. I’ve benefited from it, and I hope to pay it forward to readers with similar minds.

Integrate Python with Excel

1. Use Excel and Python together
2. Read an Excel file with Python pandas
3. Read multiple sheets from the same Excel file with Python pandas
4. Read multiple Excel files into Python
5. Read very large files into Python (extremely helpful if you can’t open a big file in notepad or Excel)
6. Save data to Excel file using Python
7. Save multiple dataframes/sheets into the same Excel file
8. How to get value and reference the rows, columns, and cells in pandas
9. Read data from web page using Python pandas (equivalent to PowerQuery “Get Data From Web”)
10. Insert rows into a dataframe
11. Insert columns into a dataframe
12. Delete rows from a dataframe
13. Delete columns from a dataframe
14. Merge Excel files in Python (equivalent to the Excel vlookup or PowerQuery merge methods)
15. Python list indexing and slicing
16. Rename pandas dataframe columns
17. Filter in pandas dataframe – part 1
18. Filter in pandas dataframe – part 2 (OR, AND, NOT logic)
19. Remove duplicates in Python
20. Split text to columns in Python
21. Sort Excel data using Python
22. Create an empty pandas dataframe
23. Find and replace Excel data in Python
24. Intro to plotting in Python
25. PowerQuery Group by in Python
26. “Mail merge” – Send mass emails in Python
27. Cashflow projections, interest compounding, present value calculation in Python
28. How to use Python lambda, map and filter functions
29. Replicate xlookup, vlookup, hlookup and index/match in Python
30. Python pandas pivot table
31. Replicate Excel Goal Seek in Python
32. Automate Excel with Python (full control of Excel, User Defined Functions, VBA and more!)
33. Convert Text/String To Number In Pandas