I do a lot of data analysis. I have done a significant amount of data processing throughout my time at university, and my Bachelor's thesis was essentially a data processing project. For all of it, I ended up using Python for some of the most complicated parts, and I found that with a combination of both Excel and Python, I was able to really improve my data processing capabilities and make it easy to enjoy the relative simplicity of Excel while still managing to benefit from the advanced features of Python.

If you want to use the version of Python that's actually built into Excel nowadays, it's pretty easy. It uses a few Python libraries provided by Anaconda. As for the use cases, you can run your Python scripts, perform data analysis, create charts, glance over spreadsheet insights, visualize your data with plots, and more.

In addition to core libraries that come with it, you also have the flexibility to import more libraries through Anaconda. You need to use a Python import statement in a Python Excel cell to complete the import process. Microsoft recommends Astropy, Faker, IPython, NumPy, Pandas, Prince, qrcode, and a ton of others. You can head to the company’s official website to glance over the entire list. I use Pandas and NumPy the most, but there are countless tutorials online to help you get started.

To be honest, though, I just keep my sheets and my Python work separate. I'll work in Google Sheets or Excel and then, when I'm done, I'll export the file and start working in Python with it. I don't do them at the same time, but I can later reimport my data for further tweaking in Excel after I'm finished with my other processes.

3 Automation and efficiency

Forget complex formulas, just use scripts instead

Excel is great and can be a quick and easy way to control your spreadsheets. With powerful formulas and a huge number of features, it's fantastic. However, if you take the time to learn Python, you can automate a lot of your processes without even need to look at anything complex. For things like data cleaning, merging spreadsheets, and building complex data dumps, Python is fantastic.

As an example, in the past, I have had to merge spreadsheets using a common key between two sheets and then take the data from spreadsheet B and append it to each relevant row in spreadsheet A. This is possible in Excel, but with Python, I have scripts that automate this process for me. I can simply run my script in the same folder as my spreadsheets and it does all the work for me, spitting out a completed CSV with everything that I need.

Of course, you can also read XLSX files with the Pandas library in Python, so Microsoft's own spreadsheet format is still fully supported.

2 Advanced data visualization

Matplotlib and Plotly are both fantastic and significantly better than Excel

If you want to have complete control over how you visualize your data, then Python is a must. Python is used by data scientists all over the world, and for good reason. It's well-capable of producing a fantastic graph or visualizing data in a compelling way, and that's thanks to libraries like matplotlib. Nearly all of the data visualization I have ever done has been through matplotlib, and it gives you complete control over every aspect of your graph.

Like the rest of Python, this is absolutely a learning curve, but I strongly recommend you get to grips with it if you want to visualize your data in the best way possible. Matplotlib is a low-level library for visualization, but you can also try something like Plotly which is a more advanced, more modern library that might be easier to get into. Plotly has a high-level API that you can use for graphing, which makes things significantly easier if you're a newcomer to programming.

1 Seamless integration

Microsoft's Python integration in Excel is great

Thanks to the integration of Python into Excel, it's never been easier to use the two together. You can simply select Formulas at the top and select Insert Python. You can also type =PY to invoke Python in a given cell. Python in Excel relies on the custom Python function x1 () to interface between Excel and Python, which works well with essential objects like ranges, tables, queries, and names.

On top of that, you can then use Excel for data entry, while then benefitting from the power of Python, and you can do it all from the one application. It's a fantastically powerful programming language that I highly recommend getting to grips with because it can make your data analysis so much easier.