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.
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
- Use Excel and Python together
- Read an Excel file with Python pandas
- Read multiple sheets from the same Excel file with Python pandas
- Read multiple Excel files into Python
- Read very large files into Python (extremely helpful if you can’t open a big file in notepad or Excel)
- Save data to Excel file using Python
- Save multiple dataframes/sheets into the same Excel file
- How to get value and reference the rows, columns, and cells in pandas
- Read data from web page using Python pandas (equivalent to PowerQuery “Get Data From Web”)
- Insert rows into a dataframe
- Insert columns into a dataframe
- Delete rows from a dataframe
- Delete columns from a dataframe
- Merge Excel files in Python (equivalent to the Excel vlookup or PowerQuery merge methods)
- Python list indexing and slicing
- Rename pandas dataframe columns
- Filter in pandas dataframe – part 1
- Filter in pandas dataframe – part 2 (OR, AND, NOT logic)
- Remove duplicates in Python
- Split text to columns in Python
- Sort Excel data using Python
- Create an empty pandas dataframe
- Find and replace Excel data in Python
- Intro to plotting in Python
- PowerQuery Group by in Python
- “Mail merge” – Send mass emails in Python
- Cashflow projections, interest compounding, present value calculation in Python
- How to use Python lambda, map and filter functions
- Replicate xlookup, vlookup, hlookup and index/match in Python
- Python pandas pivot table
- Replicate Excel Goal Seek in Python
- Automate Excel with Python (full control of Excel, User Defined Functions, VBA and more!)