On August 22, 2023, a game-changing announcement was made: Python is now integrated with Excel. This news was incredible because Python and Excel are both powerful tools for analysts and scientists, and they can now work together.
Python is a popular programming language, loved by businesses and students. But, Excel is a vital tool for organizing, manipulating, and analyzing data. Yet, until now, it hasn't been easy to make them collaborate.
Here I'll provide everything you need to start using Python in Excel.
Getting Started
If you're excited to see what Python can do in Excel, you can begin now. Windows users can sign up to give it a try, and Mac users will have access in the future. Learn more about Python in Excel and take a step towards getting certified in "Data Analysis with Python in Excel" with Anaconda.
Using Python
To use Python, click a cell, then go to the Formulas tab on the ribbon, and choose Insert Python.
You can also access Python in a cell by using the =PY function.
Now you’re ready to go.
Python in Excel offers endless opportunities for data analysis, visualization, deep stats, rich libraries, and easy sharing.
Excel Cells and Formula Bar with Python
To connect Excel and Python, we use a special function called xl(). It can work with Excel items like tables, names, and ranges.
For instance, to refer to cell B2, you write xl("B2"). If you need to mention the range from B2 to C5, it's xl("B2:C5").
You can also edit code in the formula bar, similar to coding, using the shortcut Ctrl + Shift + U.
Output types
You get to choose if you want the result to be a Python object or an Excel value.
If you intend to use the result in another Python calculation later, make it a Python object. But if you want to do an Excel analysis on the result, keep it as an Excel value.
Calculations and Errors
When you have Python code in a cell, it runs from the top to the bottom, just like usual. But when Python is in an Excel worksheet, it calculates row by row. It begins with Row 1, then goes to Row 2, Row 3, and so on.
Python cells in Excel can show errors like #PYTHON!, #BUSY!, and #CONNECT!
No matter your data science level, understanding Python in Excel gives you a competitive edge and keeps you ahead of the game.
Python Libraries in Excel
Bringing Python into Excel opens doors to a wide range of Python libraries such as pandas for data manipulations, Seaborn and Matplotlib for advanced visualization, Statsmodel for statistical modelling, Scikit-learn for machine learning and forecasting, and more.