Integrate Python with Excel – from zero to hero

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.

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.

This post acts as a summary page (or table of content) for most of the series. Hopefully, it will help readers navigate and find content easier.

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