Python is in Excel! – Here is a complete getting started guide for you

Recently Microsoft announced Python support for Excel. This is a BIG news for everyone using Excel to analyze data or find insights. In this article, let me give you a proper introduction to the Python in Excel feature and how to use it. 

If you prefer video, check out my Excel for Python is here video.

Introduction to Python in Excel


What is Python for Excel feature?

When you have "python in excel" it will show up in Formula ribbon

You can now write Python code natively in Excel cells and return the output as either Python objects or Excel values. For example, you want to perform quick statistical analysis of your sales data in the range A1:D10. You can use the below Python code to do this now.

=XL(“A1:D10”, headers=True).describe()

How do I enable Python for Excel?

This “preview” feature is only available with Excel 365 beta users as of September 2023.

If you have Excel 365, you can go to File > Account to enable “insider” program. More details on eligibility and instructions are here – https://insider.microsoft365.com/en-us/join/windows

After you’ve joined the program, update your Office from File > Account page.

Office update may be needed to get python in Excel

After the update, if you have Python for Excel, it will show up in the formula ribbon, as depicted below.

If you don’t have it yet, just wait a few weeks. It will show up eventually.

When you have "python in excel" it will show up in Formula ribbon

How to use Python in Excel:
A Quick Tutorial

Open Excel and load any of your data files. Alternatively, if you need sample data, copy paste the below table into Excel.


Sample Data (copy paste) 📃

.tg {border-collapse:collapse;border-color:#aaa;border-spacing:0;}
.tg td{background-color:#fff;border-color:#aaa;border-style:solid;border-width:1px;color:#333;
font-family:Arial, sans-serif;font-size:14px;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg th{background-color:#f38630;border-color:#aaa;border-style:solid;border-width:1px;color:#fff;
font-family:Arial, sans-serif;font-size:14px;font-weight:normal;overflow:hidden;padding:10px 5px;word-break:normal;}
.tg .tg-2fdn{border-color:#9b9b9b;text-align:left;vertical-align:top}
.tg .tg-baey{background-color:#f56b00;border-color:#9b9b9b;font-weight:bold;text-align:left;vertical-align:top}
.tg .tg-ai3s{background-color:#FCFBE3;border-color:#9b9b9b;text-align:left;vertical-align:top}

Sales Person Product Country Date Sales Boxes
Gigi Bohling Manuka Honey Choco India 20-Jul-23 8162 742
Barr Faughny White Choc Canada 16-Aug-23 2485 355
Marney O’Breen Peanut Butter Cubes India 14-Jul-23 10255 733
Wilone O’Kielt Mint Chip Choco India 2-Jul-23 16800 800
Gunar Cockshoot Orange Choco New Zealand 2-Aug-23 2842 203
Andria Kimpton Baker’s Choco Chips Canada 18-Jul-23 9373 427
Beverie Moffet Fruit & Nut Bars India 14-Jul-23 6573 598
Mallorie Waber Baker’s Choco Chips India 24-Jul-23 3598 150
Barr Faughny Spicy Special Slims Canada 11-Jul-23 5138 571
Dennison Crosswaite White Choc Canada 22-Jul-23 1547 258
Ches Bonnell 99% Dark & Pure New Zealand 16-Aug-23 12901 993
Andria Kimpton Organic Choco Syrup USA 16-Jul-23 7161 651
Gunar Cockshoot Fruit & Nut Bars New Zealand 19-Jul-23 11935 1492
Beverie Moffet After Nines India 18-Aug-23 5089 268
Gunar Cockshoot Peanut Butter Cubes USA 11-Jul-23 9247 578
Andria Kimpton Peanut Butter Cubes India 22-Jul-23 10731 826
Gigi Bohling After Nines Australia 4-Jul-23 9730 609
Gunar Cockshoot Eclairs USA 1-Aug-23 3150 287
Karlen McCaffrey 99% Dark & Pure USA 6-Aug-23 2247 205
Roddy Speechley Peanut Butter Cubes USA 1-Jul-23 2765 213
Brien Boise Caramel Stuffed Bars India 3-Aug-23 7112 647
Wilone O’Kielt Organic Choco Syrup UK 27-Aug-23 3787 345
Dennison Crosswaite Peanut Butter Cubes Canada 29-Aug-23 2674 168
Gigi Bohling White Choc India 14-Aug-23 378 54
Karlen McCaffrey Raspberry Choco Australia 7-Jul-23 7217 401
Marney O’Breen Spicy Special Slims New Zealand 19-Aug-23 735 147
Mallorie Waber Organic Choco Syrup UK 3-Jul-23 4690 427
Karlen McCaffrey Manuka Honey Choco India 24-Jul-23 8008 572
Wilone O’Kielt Spicy Special Slims Australia 18-Jul-23 12586 2518

Sample Data (download) 📁

Download the sample data file.

  1. Once you have some data in Excel, press CTRL ALT SHIFT P to enable Python mode. If you get a “welcome to Python screen” complete the tour and then press the shortcut again.
  2. Using your mouse or keyboard, select the data in your workbook. Excel should write the necessary XL() command to capture your data into Python as a dataframe.
  3. To see the dataframe you just built, press CTRL Enter. Excel will display a “Python Object” in the cell.

DATAFRAME: a dataframe is a python concept for storing data. They are like Excel tables. Each column of dataframe has one kind of data.

How to create a dataframe in Excel Python

To see the output as values
instead of Python object

converting python output to Excel values

You can see the “actual” values of your Python code anytime. Just select the cell with Python output and either press CTRL+ALT+SHIFT+M or right click on the cell and choose “Python Output” > Excel values option.

10 Python Coding Examples

Use these code samples to play with Python in Excel. Before starting.

  • Copy the above table of sample data and paste it in Excel (in range A1:F30). Alternatively, download this file with the data.
  • To type the code, enter python mode (CTRL ALT SHIFT P) or use the formula =PY( in a cell.

Example 0

Construct dataframe 👩‍💻

df = xl("A1:F30", headers=True)

 

Explanation & Output 💻

This will just create a dataframe named df and return that to the cell. You can either leave it or see the underlying data (which will be same as A1:F30) by changing the output style.

Example 1

Description of the data 👩‍💻

df.describe()

 

Explanation & Output 💻

This will generate a dataframe with statistical descriptions for all your number columns. Example output is shown below.

dataframe describe - sample output

Example 2

Description of the data, all columns 👩‍💻

df.describe(include="all")

 

Explanation & Output 💻

This will generate a dataframe with statistical descriptions for all your columns. Perfect for situations when you have some text, dates and numbers in your data. Sample output shown below:

dataframe description for all columns

Example 3

Unique Product Names 👩‍💻

df["Product"].unique()

 

Explanation & Output 💻

This will generate a python array (ndarray) that has all the product names with duplicate values removed.

Example 4

Add “Sales per Box” calculated column to the dataframe 👩‍💻

df["sales per box"]=df["Sales"]/df["Boxes"]

 

Explanation & Output 💻

This will add a new column [“sales per box”] to the dataframe with the calculation logic: sales divided by boxes. You can use the same approach to add many other columns

Example 5

Add “Sales as percentage” calculated column to the dataframe 👩‍💻

total_sales = sum(df.Sales)
df["Sales as a percentage"] = df["Sales"]/total_sales
df

 

Explanation & Output 💻

First, we calculate the “total_sales” and keep it in a variable. Then we use that variable to calculate the sales as a percentage.

💡 TIP: Do you notice the different ways in which you can refer dataframe columns? You can use dot notation (ex: df.Sales) or bracket notation (ex: df[“Sales”])

HOMEWORK PROBLEMS

Can you add below columns to the df dataframe?

  • Sales value rounded to nearest thousand.
  • Month number of the sales date
  • Flag each record as “Canada” or “Non-Canada”

Example 6

Group Sales by Date and Show a Pivot 👩‍💻

df.groupby(by="Date").sum()

Explanation & Output 💻

This creates a default groupby (similar to pivot in Excel) of your data by showing totals by date. This will sum() all the number columns in your dataframe. See the below sample output.

groupby operation and output in Python (for Excel)

Example 7

Group Sales by Date but only show Sales & Boxes columns 👩‍💻

df.groupby("Date")[["Sales", "Boxes"]].sum() 

Explanation & Output 💻

This creates a customized groupby with Sales & Boxes columns totals by Date. Use this pattern when you don’t want to summarize certain things (like Sales per box).

Example 8

Create a bar graph with Daily Sales 👩‍💻

import matplotlib.pyplot as plt
plt.bar(df["Date"], df["Sales"])

 

Explanation & Output 💻

We import the plotting library matplotlib.pyplot and use that to generate a bar graph with default settings.

Sample output is shown below:

 

Example 9

Create a bar graph with Daily Sales – another method 👩‍💻

df_groups = df.groupby("Date")["Sales"].sum()
df_groups.plot(kind="bar")

 

Explanation & Output 💻

This code uses the built-in plotting function of the pandas library to generate the bar graph. Notice how this doesn’t show missing dates.

Sample output is shown below:

Example 10

Filter the dataframe to show all records where the product has the word “Choc” 👩‍💻

df[df["Product"].str.contains("Choc")] 

Explanation & Output 💻

This code generates a new dataframe that contains all rows where the Product column has the word “Choc” in it.

MORE HOMEWORK PROBLEMS

  • Can you filter all the records that have either “Choc” or “choc”?
  • Create a bar graph of this data to show total sales by each product


How does Python in Excel work?

You need internet connection to run Python code in Excel. All the code you write is executed in Microsoft Cloud. This also means your data travels on the network to Microsoft Cloud and returns with the result.

What happens if your code has an error?

 

python error panel - excel

If there is an error in your Excel Python code, you will see a new error message #PYTHON! in Excel.

You will also see #BUSY! when Excel is running your Python code (in Microsoft Cloud).

In case of an error in your code, Excel automatically opens the Python Diagnostics tab and displays more information there.

Execution order of your code

The python code you write in Excel will run in row-major order. This means, the code runs row by row, left to right. See this illustration to understand the process.

Resources to Learn Python 🐍

Now that you are familiar with Python in Excel, you may want to learn more. May I suggest using the below approach.

  1. See if you can enable use Python in Excel to get a feel of the technology.
  2. Install a proper Python IDE like Anaconda, VS Code or something else to learn & practice Python properly.
  3. Understand the Python programming concepts like variables, conditions, list comprehension, dataframes and EDA. Here is a good article on the process.
  4. Apply these concepts on your own / business data to solidify your understanding.
  5. If you need practice datasets, try Kaggle.

📺 Python Videos

Python in Excel (video by Chandoo)

[NEW]

How to use Python as an Excel Person – FREE Masterclass + 3 Projects

[300k+ views, 1.5 hours long]

End to End data manipulation with Pandas – 10 Examples

[35k views, 18 mins]

Python Playlist on Chandoo


Python in Excel (video by Leila Gharani)

Python for Beginners (video by Mosh)

📚 Python Books

  • Python Crash Course 2nd Edition by Eric Matthes – https://amzn.to/3PBzYRK

This is the book we all (Jo, kids & I) read and really loved it. The explanations and examples are easy enough to get started. There is enough variety to please everyone. 

  • Automate boring stuff with Python – https://amzn.to/3Py5T5w

More practical if you want to get things done with Python. I read it a few times and really like the practicality of the book.

  • Python Data Science Handbook – https://amzn.to/3MFKOUK

Python is particularly useful for doing data science & building machine learning models. This is an area of focus for me in the next months. I suggest getting the Python Data Science book once you have strong foundation in the language.

Note: I am using affiliate link for these books. 

💻 Microsoft Resources

As part of the Python for Excel launch, Microsoft also added many resources and example pages to their website. Check out these pages.

  • Getting started with Python in Excel
  • Introduction to Python – Microsoft Learn Course
  • Using “dataframes” in Excel
  • Power Query and Python in Excel
  • Libraries supported by Excel Python

The post Python is in Excel! – Here is a complete getting started guide for you appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Original source: https://chandoo.org/wp/introduction-to-python-in-excel/

Close Menu