• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

How to Use Python in Excel Natively

You are here: Home / Excel Formulas / How to Use Python in Excel Natively
python in excel natively
August 22, 2023 by Mynda Treacy

You can now use Python in Excel natively!

Python runs securely in the cloud, and we write Python in Excel like a formula.

You can load Python libraries to Excel including Pandas, NumPy, Seaborn, Matplotlib and more.

No need to install any add-ins and no clunky separate windows for writing the code.

Note: Python in Excel is currently available to Microsoft 365 Office Insider users on the Beta Channel.

 

Table of Contents

  • Example Excel File Download & Cheat Sheet – Try it out for yourself
  • Python in Excel Examples
  • Python in Excel Video – Step by Step
  • Learn how to use Python in Excel
  • Excel Python Function
  • How to Import Python Libraries
  • Defining Dataframes
  • Python Descriptive Statistics in Excel
  • Python Plots in Excel
  • Generate Code with ChatGPT
  • Excel Python Errors & Troubleshooting
  • Python in Excel Security
  • Shortcuts
  • Licensing

 


Download Python in Excel Example Workbook & Cheat Sheets

Enter your email address below to download the file.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

  • Download the workbook
  • Download Python in Excel Cheat Sheet
  • Download Python Formula Editor Cheat Sheet
 

 

Python in Excel Examples

We can use Python in Excel to create cool charts aka Python Plots, that we don’t have in the Excel chart library:

 

Your browser does not support the video tag.

 

Descriptive statistics are also easy with Pandas in Excel:

Descriptive statistics using Pandas in Excel

And when you use ChatGPT to generate the Python code, you don’t even need to know Python to use it!

Using ChatGPT to write Python Code for Excel

It’s still early days for Python in Excel with much development still to come.

It’s also possible that features, icons and menus in this post will change before it’s generally available as a result of feedback while it’s in preview.

Watch the Python in Excel Video

Subscribe YouTube

Learn How to Use Python in Excel

Excel Python Function

Python is written in a new Excel function, PY.

Typing =PY then pressing the TAB key, puts the formula bar into Python mode with a green banner to the left:

New Python Function PY

You can also switch the formula bar into Python mode via the Formulas tab > Insert Python > Custom Python Formula:

Switch to Python Mode in Excel Formula bar

Python functions are completed with CTRL+ENTER.

How to Import Python Libraries to Excel

The first thing you’ll want to do is import the libraries you want to use in the file.

Simply list them by specifying:

import LibraryName as LibraryShortName

For example, in the screenshot below I’ve imported Pandas, Seaborn and Matplotlib:

Import Python Libraries in Excel

Finish with the text you want to display when the libraries have loaded and press CTRL+ENTER to complete the formula.

Tip: if the cell doesn’t display the Python Object symbol, choose it from the drop down beside the formula bar:

Choose Python Object symbol from formula bar drop down

Defining Python DataFrames in Excel

Next, you’ll want to load your data to a DataFrame.

DataFrames are defined with a new function called xl.

To use it, prefix the formula with the dataframe name, this is often simply df:

DataFrameName = xl("cell range", headers=True/False)

In the image below you can see I’ve named my DataFrame df and it’s on a sheet called ‘Employee Data’ in cells A1:AI471:

Creating a dataframe in Excel Python

Note: There is flexibility on how you use DataFrames in Excel, you can refer to them by name e.g.

df['HourlyRate'].plot()

or simply by using the cell reference e.g.

xl("C3")['HourlyRate'].plot()

Tip: You don’t have to place the Python Libraries and DataFrame in separate cells to the Python code, but by doing so, you make them available in other Python formulas in the workbook.

Because Python cells calculate in row-major order (left to right, top to bottom), the Dataframe must be defined in a cell above the cell containing a Python formula that references it.

This concept applies to different Sheets as well, going left to right. Globals also work across different sheets (left to right).

Python Descriptive Statistics in Excel

Now that we’ve imported the Python libraries and defined the DataFrame, we’re ready to analyse the data.

Let’s start with some descriptive statistics using Pandas.

In a cell below the DataFrame cell, I can simply enter a Python formula.

Remember to start a Python formula enter =PY then TAB, then enter the following code:

df.describe()

And CTRL+ENTER to complete it.

Alternatively, if your DataFrame is on another sheet, as mine is, you can reference the sheet and cell containing the DataFrame with the xl function like so:

Using XL function to refer to a Dataframe on another Excel Sheet

Python returns descriptive statistics for all numeric columns in the DataFrame.

Python Plots in Excel

Charts, or Python plots as they’re known, can be created in a Python formula and the resultant plot is displayed in a single cell.

In the image below I’ve created a Seaborn Hexbin JointPlot.

You can see it references the DataFrame with the Age field on the X axis and MonthlyIncome on the Y axis:

Seaborn Hexbin JointPlot in Excel Cell

Of course, it’s not much use storing charts in a single cell.

We can merge multiple cells to give them more space, but merged cells are a pain.

Instead, it’s better to use the image tools to create a linked picture of the chart that we can move to other sheets in the file:

Use Image tool to create linked picture

This picture can then be resized to suit your report requirements or even copied to PowerPoint or Word etc.

For more chart examples, download the file from the link above.

Generate Code with ChatGPT

We’ve looked at using Python in Excel to return statistics and plots (charts), but there are many uses for Python in Excel, but most Excel users like me, aren’t Python programmers.

I’ve never written Python code before writing this post. So, I turned to ChatGPT-4 for help.

If you’re not familiar with Python, try using ChatGPT-4 with Code Interpreter turned on:

Using ChatGPT 4 with code interpreter turned on

Or if you don’t have a GPT-4 subscription, you can use Bing Chat in Creative mode:

Using Bing Chat in Creative mode to write Python

Excel Python Errors & Troubleshooting

Python cells return the last assignment or last expression.  If None is returned, you will see the value 0 or 'None' in a cell.

Hovering over the error flag will give you the error in a tooltip:

Python in Excel error on hover

And clicking on 'Show Error Message' returns the error in a popup:

Python in Excel error popup

You can also check the Diagnostics pane via the Formulas tab:

Python diagnostics pane

Tip: click the cell reference in the diagnostics pane to go to the cell.

Or try asking ChatGPT or Bing Chat what’s wrong with your code.

Errors you’re likely to experience when working with Python in Excel:

 

#PYTHON! Likely indicates a syntax error. The diagnostics task pane automatically opens if the #PYTHON! error is returned. Check the diagnostics task pane to see details about the error.

 

#CONNECT! Means there are issues with the Python cloud service. If you see this error, click the Reset button in the formulas tab or press CTRL + SHIFT + ALT + F9 to restart the Python Session.

 

#CALC! This error can occur when Excel's calculation engine encounters an unspecified calculation error with an array. Python in Excel calculations can process up to 100 MB of data at a time. Attempting to run a calculation with more than 100 MB of data returns the #CALC! error. Try using a smaller dataset. Or if your dataset is not that large, try restarting the Python session with CTRL + SHIFT + ALT + F9.

 

#BUSY! Means what it says. Stay patient and it should eventually calculate.

 

Tip: whenever a Python formula is entered, it triggers all Python code in the file to recalculate.

You may find it better to turn calculation mode to manual or partial while working in files containing a lot of Python.

Format Stale Values will put a strikethrough any cells that haven’t been recalculated:

Enable Format Stale Values to indicate cells that have not recalculated

#VALUE! or #UNKNOWN! typically means you don't have Python in your version of Excel.

 

#SPILL! There’s data occupying cells the Python values want to spill to. Clear the cells in the spill range.

 

#TIMEOUT! Can indicate a network issue. Try increasing the timeout duration in Excel Options: File tab > Options > Advanced:

Excel Advanced Options

Python in Excel Security

In Excel, the Python code is executed within a secure hypervisor isolated container on Microsoft's Cloud, ensuring enterprise-grade security as part of a compliant Microsoft 365 integrated experience.

When opening untrusted workbooks containing Python in Excel, it adheres to Excel's security policies, preventing Python formulas from running in Protected View or with Microsoft Defender Application Guard and isolating them in a dedicated container to avoid interference with other workbooks. In other words, workbooks containing Python are treated with the same caution as opening a macro enabled workbook.

Click here for more details on how Python in Excel security is handled.

Shortcuts

Everyone loves keyboard shortcuts and with Python comes some new ones you might find handy:

Python Mode

open Python editor in Excel

Opens the Python editor in the current cell for authoring Python formulae in the formula bar.

Output Toggle

toggle between Python object and Excel values

Toggles between "Python Object" and "Excel Values" as the output type for Python formulae.

Toggle Formula Bar Size

expand or collapse the formula bar

Expands or collapses the formula bar.

Edit in Formula Bar

edit in formula bar

Opens the selected cell for editing in the formula bar. If already editing, will toggle between cell and formula bar editor

Edit Mode Toggle

switch editor mode between Enter, Edit or Point

Switches the editor’s mode between Enter, Edit, or Point. e.g. useful for switching between reference selection and editing.

Commit Python Formula

Commit current text as Python formula

Commits the current text as a Python Formula to the cell.

 

Licensing

While Python in Excel is in Preview (beta) you will be able to use this feature as part of your subscription.

After the Preview, you will need to purchase an additional license to use it.

 

python in excel natively
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Formulas Posts

Excel BYROW and BYCOL Functions

Excel BYCOL and BYROW Functions

Excel BYCOL and BYROW functions fundamentally change the way we write formulas that calculate across columns and down rows.
excel dynamic named ranges

Excel Dynamic Named Ranges

Excel Dynamic Named Ranges update automatically to include new data in the ranges referenced in your formulas and PivotTables etc.
functions for financial modelling

Excel Functions for Financial Modeling

Top 23 must know Excel functions for Financial Modeling. Includes example Excel file and step by step instructions.
excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Labs (Formerly, Advanced Formula Environment)

Excel Labs is a long awaited, new improved way to write, name and store Excel formulas, including LAMBDAS with the help of AI.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand


Category: Excel Formulas
Previous Post:excel dynamic named rangesExcel Dynamic Named Ranges
Next Post:Interactive Python Charts in Excelinteractive python charts in excel

Reader Interactions

Comments

  1. Thasneem Muthalib

    September 10, 2023 at 2:47 pm

    Hi, im getting an unexpected failure as ‘#PYTHON!’. Even the Python sample is not working

    Reply
    • Mynda Treacy

      September 10, 2023 at 7:03 pm

      Sounds like a bug. Try Ctrl + Shift + Alt + F9, this will restart the python session.

      Reply
  2. Sam Slokom

    September 1, 2023 at 9:55 am

    Can not get the Python For Excel Cheat Sheet to download.

    Thanks

    Reply
    • Philip Treacy

      September 1, 2023 at 10:15 am

      Hi Sam,

      Can you be specific about what the exact problem is.

      Does the link not download the cheat sheet?

      regards

      Phil

      Reply
  3. Peter Bartholomew

    August 30, 2023 at 8:01 am

    One needs a Chrystal Ball to see where this is going. Does one ditch native LAMBDA and LET and switch all calculation to Python libraries? Or would it run like a dog, showing ‘Busy!’ all day? Does one have to master a plethora of libraries or would 95% of the work require one?

    I would be sad to see Excel sheets gutted since so much has become possible with native Excel formulas. I suspect my writing a Fast Fourier Transform as an Excel formula was pushing the concept of ‘roll your own’ a step too far now as ‘numpy.fft’ has hit the streets – but I have still to learn how to call it!

    Anyway, thanks for the intro, there seems to be so much learning to be done and it is difficult to guess where the highest payoffs will be.

    Reply
    • Mynda Treacy

      August 30, 2023 at 11:50 am

      So far in my experience, Peter, Python calculates slower than native Excel functions, so I won’t be ditching much. For me as an Excel user who knows very little Python, I’m excited to use it for charting and maybe some statistical analysis. But for data cleaning and shaping, modelling tasks I’ll be sticking with Power Query and Power Pivot. I’m sure Python experts will prefer to use Python in Excel for those tasks and now they can.

      Reply
  4. JEAN-PIERRE MALICIEUX-GAUTIEZ

    August 30, 2023 at 4:03 am

    It’s all just awesome.
    Incredible computing power within everyone’s reach.
    A superb tool for discovering, studying and experimenting with Machine Learning

    Reply
    • Mynda Treacy

      August 30, 2023 at 11:02 am

      So pleased to hear you’re excited to use Python in Excel, Jean-Pierre!

      Reply
  5. tony

    August 25, 2023 at 10:27 pm

    You were quick to market with this one! I haven’t seen the new PY function in my Edition yet (although I am on the Beta Channel). Are the new objects and functions callable from VBA do you know?

    Reply
    • Mynda Treacy

      August 25, 2023 at 10:45 pm

      Hi Tony,

      I’m not sure if you can call Python with VBA, sorry.

      Check for daily updates to Excel to see if you get Python. They drip feed it so they can check for adverse effects!

      Mynda

      Reply
  6. Jeff

    August 24, 2023 at 2:29 am

    Thanks for the overview. Just thought I’d share- for Python learning, I found Corey Schafer’s YouTube channel amazingly clear and efficient. The channel was a little bit dormant for a while but it looks like he’s posting again. https://www.youtube.com/@coreyms

    Reply
    • Mynda Treacy

      August 24, 2023 at 10:05 am

      Awesome, Jeff. Thanks for sharing.

      Reply
  7. carlos Lopes

    August 23, 2023 at 7:12 am

    Pretty cool :0

    Reply
    • Mynda Treacy

      August 23, 2023 at 8:37 am

      Indeed, Carlos! Have fun with Python in Excel

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.