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

12 comments

  1. Hi,
    I am praveen and I am a student studying in melbourne, I went through some of you videos on youtube about DataScience project found it fascinating , I was working on an automation project on python, I was reading “multiple .xlsx” files from a directory and was trying to combine them and upload it into MySQL database automatically , but once the files have been combined i have been getting many empty columns and rows and also was not able to figure how to clean all the excel files after reading them at once. So once the combined file is generated , I am not able to override it or update the data when I add more .xlsx files into the file directory.

    So I was wondering if you could do a youtube video on it , or could give me some ideas on how to perform this task.

    Thanks
    Praveen

    1. Hi Praveen,

      Thanks for dropping by!

      It sounds like your Excel files might contain columns with different names (if you are appending them), or rows with different keys (if you a merging). It’s kinda hard to tell what the real issue is without looking at the data.

      Can you send me some samples of the data you are working with so I can try to replicate the issue?

      Regards,
      Jay

  2. This course is the best Python-Excel summary the internet yet offers!

    The only prerequisites:
    – you worked a lot with Excel
    – you are interested to work with Python instead of Excel

    Thanks Jay.

    1. Thank you Roger for your kind words!

      I continue to add new tutorials here, and I plan to re-organize the “table of content” so it will be more user-friendly.

      Please stay tuned 🙂

  3. Hi!

    Thanks for all the content.

    The topic 32: “Automate Excel with Python (full control of Excel, User Defined Functions, VBA and more!)” is with the link wrong. Can you change it, please?

    Greetings.

  4. Thank you is not enough for your great blog posts. I’ve fallen in love with Python but don’t know how to go forward and lost, finding your blog is one of the best things happened to me today.

    1. Thanks!
      I’m sharing what I learned along the journey. I benefited from online free resources, and I want to pay it forward 🙂
      If you have any questions feel free to drop a comment or send email!

  5. Hi Jay,
    I am transitioning from Excel to Python for my office projects and this is just what I needed to get started. Thank you for sharing all these tips and methods in such a clean and intuitive way. Your website is the one-stop shop for anyone who is new in transitioning from Excel to Python. Kudos to you and keep up the good work 😊

    Ram

Leave a Reply

Your email address will not be published. Required fields are marked *