Ravindra BagaleCourses & study guides मराठी Track your progress

29. Python in Excel

Practice and Answer Key: Analyse 8 Orders with Python in Excel

By Last updated:

Goal of this practice: Load a Table into Python, group it, count late orders, pivot it and check every result with an Excel formula.

Suppose Ravina has 8 fictional orders in an Excel Table named tblPractice (A1:E9). She has Python in Excel. All data is fictional practice data.

Order ID City Platform Amount Mins
BLK-4001 Kolhapur Blinkit 350 10
AMN-4002 Nagpur Amazon Now 420 18
BLK-4003 Kolhapur Blinkit 180 16
AMN-4004 Pune Amazon Now 610 12
BLK-4005 Nagpur Blinkit 290 9
AMN-4006 Kolhapur Amazon Now 230 14
BLK-4007 Pune Blinkit 505 20
BLK-4008 Nagpur Blinkit 145 11

Exercise (one under the other):

  1. In one Python cell load tblPractice and return the total Amount.
  2. Group by City: total Amount and average Mins (1 decimal). Return it as Excel values.
  3. Count the orders with Mins more than 15.
  4. Make a pivot table: City in rows, Platform in columns, sum of Amount, 0 for empty.
  5. Return the median Amount.
  6. Check the total, the Kolhapur Amount and the late count with Excel formulas.

Expected result (check your numbers):

Check Expected
Total Amount 2730
Kolhapur Amount / AvgMins 760 / 13.3
Nagpur Amount / AvgMins 855 / 12.7
Pune Amount / AvgMins 1115 / 16
Late orders (>15) 3 (AMN-4002, BLK-4003, BLK-4007)
Median Amount 320
Pivot Kolhapur Amazon Now 230, Blinkit 530
Pivot Nagpur Amazon Now 420, Blinkit 435
Pivot Pune Amazon Now 610, Blinkit 505
Answer key (open after you try) 
df = xl("tblPractice[#All]", headers=True)
df["Amount"].sum()

df.groupby("City").agg(Amount=("Amount", "sum"), AvgMins=("Mins", "mean")).round(1)

(df["Mins"] > 15).sum()

pd.pivot_table(df, values="Amount", index="City", columns="Platform", aggfunc="sum", fill_value=0)

df["Amount"].median()

Excel checks: =SUM(tblPractice[Amount]) = 2730, =SUMIFS(tblPractice[Amount],tblPractice[City],"Kolhapur") = 760, =COUNTIFS(tblPractice[Mins],">15") = 3.

Common mistakes:

What you see Why Fix
#PYTHON! "df is not defined" The groupby cell is above or to the left of the load cell Put the load cell first in row-major order
Groupby shows only one number You wrote .sum() on the whole DataFrame column without groupby Use df.groupby("City")[…]
#SPILL! Excel-value output overlaps other cells Clear the area or move the cell
Header row counted as data headers=True missing Use xl("tblPractice[#All]", headers=True)

Ravindra Bagale's rule: Python calculates, Excel confirms.

Quick recap

  • Python in Excel runs Python from a cell in the Microsoft Cloud with Anaconda libraries; Business/Enterprise have it, Family/Personal have a preview; not on iPad, iPhone or Android.
  • Start with Formulas › Insert Python, =PY or Ctrl+Alt+Shift+P; commit with Ctrl+Enter.
  • xl("Table[#All]", headers=True) reads a whole Table as a pandas DataFrame.
  • Return results as Python objects to reuse them, as Excel values to chart or format them (Ctrl+Alt+Shift+M).
  • groupby with agg and pivot_table give the same numbers as GROUPBY and PIVOTBY; Matplotlib and seaborn draw charts as image objects.
  • Cells calculate in row-major order; the code has no network or file access, and up to 100 MB of data per calculation.