Transitioning from Excel to Python — A Guide

A breakdown of tools that allow Excel users users to join the Python community

Photo by Pietro Jeng on Unsplash

Colloquially, Excel is the greatest piece of software ever written. There is an endless list of positive qualities about Excel, but its flexibility, ease of use, and open environment for scratch work are the most noteworthy to me. As powerful as Excel is, I find it increasingly lagging in its ability to handle large datasets and perform data science like tasks, which is required for more and more tasks.

In this blog, I will go over all the useful tooling I have found to help Excel users transition their workflows to and augment their workflows with Python. I will break this into three categories: Transitioning Excel Workflows to Python, Using Python to Augment Excel, and Resources for Excel Users Learning Python

Transition from Excel to Python

There are many Excel users who want to learn Python. Fortunately there are many tools that make the transition easy.

Mito

Mito is a spreadsheet that generates Python. You can call it into your Python environment and each edit you make in the spreadsheet will generate the equivalent Python. Here is a demo video of some data exploration:

To install Mito, run these commands in your terminal:

python -m pip install mitoinstaller
python -m mitoinstaller install
python -m jupyter lab

Here is the full Mito documentation.

Lux

One of the many reasons that Excel users come to Python for is for better visualizations. Python allows for more dynamic charts on larger datasets than Excel can access. Lux is great for new Excel users transitioning to Python because it requires very little coding. All you have to do is pass in your dataset and Lux will recommend charts for your data that can you select from.

https://lux-api.readthedocs.io/en/latest/

In the image above, you can see that Lux will recommend different distribution relationships for columns in your data frame, and the user just has to click which one(s) they want to use.

To install Lux in your Python environment, just run:

**pip install lux-api**

Pandas Profiling

This package allows for user friendly Exploratory Data Analysis. To install, run these commands:

import sys
!{sys.executable} -m pip install -U pandas-profiling[notebook]
!jupyter nbextension enable --py widgetsnbextension

Pandas Profiling allows you to easily explore summary statistics, find Null Values, and see correlations etc., in a visual environment. The best part is that it is called right into your Python editor, fitting seamlessly into your workflow.

Using Python to Augment Excel

For those who do not want to start using Python as their primary data analysis tool, but do want to reap the advantages of Python, there are many tools worth trying out.

XLwings

XLwings is an open source Python package that allows you to automate spreadsheet work with Python. You can manipulate your spreadsheet data with the flexibility and power of Python. Handling hundreds of thousands of data entries is a much smoother process with a Python engine running it. You can also use the package to create Python macros, replacing VBA.

To import the package, run this command:

pip install xlwing

This is what sample code from the documentation website looks like:

https://www.xlwings.org/

XLwings is great for using Python to speed up your Excel processes.

Openpyxl

Openpyxl is a Python package that lets you easily read and write Excel files from a Python environment. You can create an Excel workbook from scratch, define cell values, merge cells, insert images etc. Imagine you are using Excel but all the buttons are Python commands that you write. There is more nuance to this description — this is just the high level. Here is the full documentation, if interested.

Also, here is an in depth tutorial of Openpyxl from the Tech with Tim youtube channel:

Resources for Excel Users Learning Python

Many creators are sharing great guides for Excel users to transition to Python. Here are a few I recommend you check out:

1. Practical Business Python — a great website with Python, tutorials, tips, and documentation from the perspective of a business user. It is a great forum for learning how to introduce Python into your business tasks.
2. Derrick Sherill — his youtube channel has many helpful Python tutorials, but also some great videos on automating spreadsheet processes using Python
3. python-excel.org — this site has a list of Python packages built for connecting to spreadsheets, including some mentioned in this article.

I hope these resources were useful. Please leave a comment, if you have any others that you recommend :)