Performing repetitive tasks, doing complex data analysis, and creating advanced reports can be time-consuming in Microsoft Excel. Here’s where the power of Python comes into play. You can combine Python’s versatility with Excel’s familiarity to supercharge your spreadsheets in no time. Whether you are a financial wizard, data analyst, or just someone who loves to tinker with data on your laptop, explore Python in Excel and take your spreadsheet skills to the next level.
Microsoft Office 2024: Everything you need to know about the latest version
Microsoft hasn't announced it yet, but Office 2024 is in the works, and you can try it right now with some fiddling
What is Python in Excel?
As the name suggests, you can bring Python analytics to Excel with a neat integration. You will be able to seamlessly execute your Python commands and process data in Excel with a single click only. You can type Python code right into your Excel cell and get real-time results in your worksheet. The system runs your Python calculations on Microsoft Cloud.
Excel uses a core set of Python libraries provided by Anaconda. As for the use cases, you can run Python codes, perform data analysis, create charts, glance over spreadsheet insights, visualize your data with plots, and more.
In addition to the core libraries, 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 more. You can head to the company’s official website to glance over the entire list.
Join the Microsoft 365 Insider Program
Python in Microsoft Excel is currently available in Preview only. To gain access, you need to participate in the Microsoft 365 Insider Program and pick the Current Channel (Preview). Here’s how.
- Open Microsoft Excel and click File in the top left corner.
- Scroll to Account. Expand Microsoft 365 Insider and select Change Channel.
- Join the Current Channel (Preview) and check for updates after some time.
- Your computer should be able to install the latest version of Office apps and enable Python support in Excel.
You can also choose Beta Channel Insider to access Python features in Excel that haven’t been released yet in the Current Channel (Preview).
Python in Excel availability
As of now, Python in Excel is available on Windows only. You can’t access it on Mac, iPhone, iPad, Android, or the Web. You can still open Python containing workbooks on unsupported platforms, but Python cells may throw an error when recalculated.
It doesn't even require having a local version of Python installed on your computer. Python in Excel runs in the Microsoft Cloud with a standard version of the language only. This means that any tweaks you made to your local version of Python won’t be considered in Excel calculations.
Since Python support for Excel is still in the Preview stage, Excel users can add and edit Python formulas with premium compute speeds. Once the Preview is over, access to premium computer speeds will require a paid license.
Use Python in Excel
There are a couple of ways to use Python in Excel. You can simply select Formulas at the top and select Insert Python. You can also type =PY in a cell to enable Python. It basically lets you enable the Python formula in a selected cell. Once it shows PY symbol, you can type a relevant Python code directly into the cell.
Apart from =PY, Python in Excel supports several keyboard shortcuts. You can check the Microsoft support page to learn more.
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. Let’s go through some examples.
- Use x1 (“D1”) to refer cell D1
- Type x1 (“F1:C4”) for the range F1:C4
- For a table with headers named Calendar, use x1 (“Calendar[#All]”, headers=True)
Here, the #All specifier refers to the entire table. You can also use #Data to refer to just the data rows, #Headers to refer to just the header row, #Totals to refer to just the total row, and more. It’s entirely up to you to analyze the type of data you need by using a Python formula.
As for the Python output type, you have a couple of options for how the software returns Python calculations. You can choose to execute the formula output in Python objects or Excel values.
Python on Microsoft Excel also supports DataFrame (two-dimensional data structure) and the ability to create Plots and Charts.
Excel and Python: A match made in data heaven
Python integration in Excel opens up a world of possibilities for automating tasks in Excel spreadsheets. Whether you want to create an efficient workflow, check out new ways of handling data, or go through complex calculations, explore Python in Excel to get the job done. If Python is a bit too technical for you, alternatively you could record macros to automate your repetitive tasks in Excel.