What are the Top 5 uses for Python in Excel in 2023
Microsoft has announced Native Python programming support directly in its spreadsheet application Excel (available immediately in preview) and it’s a game changer for Business Analysts, Data Analysts, and general office workers globally.
What is Python?
Python is, in 2023 arguably the most popular programming language in the world (Tiobe Index). More importantly, it has become the de-facto language to use in data science, machine learning, data engineering and statistical analysis tools. That means you want to build AI - you’re likely to have to learn Python.
What is Python in Excel?
Python in Excel is Native programming support in Microsoft Excel for Python. You can write and run python code directly in Excel.
With a click of a button, I can insert Python Code directly into cells and the formulas bar of Excel, bringing with it the full power of the Python programming language as well as access to thousands of pre-written libraries (modules), that will help you be more productive and do amazing things to impress your boss and make you more valuable to any organization!
Why does this matter in 2023?
Microsoft Excel in 2023 is by far the most widely used business application in the world. Estimates put this spreadsheet app used by more than 750 million people globally, and will likely reach 1bn users in the next 5 years. That’s 1 in 8 people on the planet using it. The flexibility that Excel provides has made it popular over the decades for a wide variety of tasks - from people creating business forecasts, managing financial accounts, creating invoices to doing complex maths formulas on it or even creating birthday cards.
Many companies use Excel to manage their business, get day to day tasks done and create processes and work that keeps businesses ticking along.
For years, the only native programming application for creating macros (repeatable tasks) within Excel was VBA. A lot of people didn’t know how to use VBA and/or the support to learn it just wasn’t available to hundreds of millions of people. With the advent of Python programming support natively in Excel (without a user having to install anything) is a game changer for millions of non-programmers out there, to do some pretty amazing, powerful stuff to help people do many things that just were not possible before.
FOUNDERBOUNTY STARTUP COURSES
Here is a selection of our top 5 uses for Python in Excel:
- Graphing and Plotting Complex Visuals
With Native Python Support and the ability to tap into a vast number of free open-source libraries out there you can start building out more complex visual charts and graphs than the standard charting capabilities of Excel.
seaborn python visualization library
For example you can use the open source seaborn library to take your excel data and change it into visually stunning charts and graphs all in a few python lines of code. And with a ton of sample code out there, it is not even that complicated for first time users to create fantastic looking charts to tell better stories to their users.
2. Statistical analysis
Giving python access in the familiar environment of a well known business tool opens a new world of data analysis possibilities and potential previously limited to data scientists and developers. All of a sudden, regression analysis, forecasting and complex powerful statistics which were not available to regular users, are now available.
Using the python library pandas or statsmodel you get access to a range of algorithms to apply to your Excel data. Imagine you had a large data set in Excel of house prices, house features and location information. You can then use a statistical model to try and predict
To attempt to do this in Excel previously using formulas alone is out of reach for most Excel users, but with access to python libraries that can do it for you, users can perform complex statistics analysis with ease.
Register your domain here:
3. An entry point into programming for beginners
Computer programming (coding) can be difficult and daunting for so many people out there, especially people without training. It can be complicated. Excel is a lot easier to understand, cells, data, columns, rows, headers, manipulation of data, and formulas.
But here’s a little secret, they are both very similar and understanding how to manipulate and change data in Excel is actually one of the best ways to learn programming. Especially for Visual Learners Many people have done it in the past 20 years using VBA.
As an entry point programming language, using Excel to see what you code and what changes is makes to the data; working with loops and objects, is one of the best ways to enter the coding world. You can make things happen as you learn what is going on under the hood. Once you’ve got comfortable with Python in Excel and getting things working, you can take that newly found python knowledge you’ve learned and directly apply it outside of Excel. Python is one of the most widely used programming languages out there, from building websites, machine learning models, automated scripting or even raspberry pi weather detectors. It is pervasive and none of the skills you learn are lost.
4. Automating Excel work without VBA
Visual Basic for Applications (VBA) was first launched for Microsoft Excel in 1993! That was 30 years ago and was the de-facto native programming language available to code inside Excel. VBA helped people create Macros to automate repeatable steps in Excel or to import data and transform it by itself , but was notoriously unwieldy, difficult to learn and prone to people not advanced in VBA, building pretty unrobust macros and automation.
Now with the advent of Native Python Support, it’s going to be easier to develop automated scripts in a more fundamental Object Oriented Programming (OOP) language that has a huge support and following on the internet. This means snippets of more understandable and shareable code will be available to automate a lot of Excel tasks. A more robust way of creating Macros and potentially eliminating hours and hours of work and resources.
If you learn and get better at Python in Excel > for some types of tedious, laborious, repetitive tasks, you can automate it using some python code, hit run and get a coffee instead. Keep it to yourself and don’t tell your boss and work colleagues how much time it really took! 😀
5. Machine Learning!
Yes you heard that correctly. Actual Machine Learning is now perfectly possible within Excel using Python in Excel and your data sets.
To understand how this is possible, you probably need a super brief condensed guide on how Machine Learning (ML) models have worked over the past few years.
Machine learning data scientists have used python and usually some large data set for their machine learning. For example, in supervised learning, training data (a subset of the data in your data set) is used to train up a model. (e.g. a model to tell whether or not a row of data represents an automobile or not). Once the model has been trained up, they’ll use the remaining data to test their model and see if it can make accurate predictions. Fine tuning the model, to make it more accurate, then you can let it run independently and use this ML model on any similar set of data that comes in to automatically make predictions.
Before Python in Excel - most data scientists would do this using either their own development environment on jupyter notebooks - which can be a daunting entrypoint to many non-programmers.
Now Excel users can have access to the power of machine learning models and apply it to their own data and make ML models sing and dance all within Excel. That means access to different open source machine learning libraries, algorithms and techniques. For the first time, regular Excel users can become Machine Learning power users!
Conclusion
2023 will be known as a powerful leap forward for businesses in all industries. Microsoft teaming up with Anaconda to release Python in Excel is as powerful today as when VBA was released in Microsoft Excel 5.0 in 1993, 30 years ago. It will change the way business analysts, accountants, financial services admin staff and middle office workers globally will work for the next 20-30 years and possibly more. It is the most powerful business application tool being put in the hands of people for a long time. Without having to dive into complicated development, people have access to tools that the big gun (and very well paid) data scientists have had for years. With big power comes big responsibility (and opportunity)!
Learn how to build a business:
EDITORS CHOICE: FOUNDERBOUNTY UNIVERSITY.
Founderbounty is a great resource with courses from real entrepreneurs that an teach you about business, its basics and building one from scratch. From why content is important to specific topics this may be a great all-round resource.
A great all round way with some interesting topics on building a business but usually very personalised on certain topics. This may not be so suitable for people starting from scratch and looking on ways of building a startup unless your looking for something specific.
3. Masterclass courses
A well know site with lots of talk about people who have made it but no real resources on how they did it and specific points on what you should be doing in 2023 to build a successful business and what venture capital really are looking for to be able to fund you.