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.
What is Python for Excel feature?
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.
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.
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.
| 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 |
- 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.
- 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.
- 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.
To see the output as values
instead of Python object
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.
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:
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.
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?
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.
- See if you can enable use Python in Excel to get a feel of the technology.
- Install a proper Python IDE like Anaconda, VS Code or something else to learn & practice Python properly.
- Understand the Python programming concepts like variables, conditions, list comprehension, dataframes and EDA. Here is a good article on the process.
- Apply these concepts on your own / business data to solidify your understanding.
- 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 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.
22 Responses to “Master Excel 2007 Ribbon with this Free Learning Guide”
Thank you, kind sir. Well done with the baby making.
I cannot get signed up for your newsletter. I tied both this email address and churchill2001@hotmail.com. never a response.
I cannot get signed up for your newsletter. I tied both this email address and churchill2001_at_hotmail_dot_com. never a response for either attempt.
@Doug, it shows that your email address is pending verification. Can you check your inbox (and may be spam folder too) for an email from me? The subject will be "Activate Subscription to Get your Free Excel Tips E-book"
[...] PPS: If you are struggling with ribbon, you should check out ribbon learning guide. [...]
Very Useful Info..Keep it up..
@Ajay.. you are welcome 🙂
how do u download microsoft excel for free?
http://www.microsoft.com/en-us/default.aspx
Select Office
Free Trial
[...] Excel 2010 UI looks considerably better and less stressful than 2007. The colors are dull and subtle. The icons don’t call for attention unless you want to do something. The menus / ribbons feel smoother and slicker. [Learn to use Excel Ribbon with this Free e-Book] [...]
I can't open this pdf. I get the error message:
You do not have the required license to open this file.
Please request a license from the creator of the file, and add it using the license manager and they try opening it again.
What gives??
I downloaded the file again and it worked this time. Strange. (First file was 116 KB, second was 1644 KB... ???)
[...] More ribbon goodness | Free e-book to learn Excel Ribbon [...]
Hi Chandoo,
thanks for sharing your Excel 2007 learning experience with us; unfortunately the link to the pdf of the free Excel 2007 learning guide seems broken: my Acrobate Readers flags: "Unkown file type or corrupte data".
Have a nice day
Michael
well done this is great
Can somebody just provide a link the classic TAB exportedUI files for MS Office 2003 for us to use in office 2007/2010?. searching online, everybody just wnats to make a buck online with silly Classic Tab installers which do nothing more than inport exportedUI files for you.
Don't give me a ribbon how to guide, just give me free exportedUI files. I should not have to pay anyone for this, it is free XML, MS should have included this to begin with.
thanks
Dear.
There are a set of debit values and a set ot credit values in a column. I want a vba code by whcich the debit value plus a single / multiple credit value is zero that needs to be marked .
finally i will come to know out of the avaibale debits which cannot be used the with avilable credits either single or multiple values.
If multiple matching sets are available let it take the 1st or the 2nd one its not an issue.
Column A Ref
-1000 A
-5000 B
-8000 C
800 A
100 A
100 A
2000 B
3000 B
13000
15000
hi...
how to make this add-ins and display in ribbon... check this sample : http://www.cprsoft.com/GCDemo01.htm
thank you sir...
Please tell me format painter short cut key In excel ?
Thanks In Advance
thankfully.likeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
I am very much happy for such a great opportunity given to excel learners to advance their skills for the betterment of the future. I am a great user of this site and feel proud to have come across this web site.
I appreciate this, because I didn't do much works in my project management studies using gantt chart. As of now are have now learned some advancement.