Python in Excel : best practices, building simple summary tables with Data interpretation & troubleshooting.

Security & Sandbox: Python in Excel operates in a restricted environment. It cannot access, read, or modify files stored on your local drive for security reasons. It only interacts with data within the currently active workbook. 

In my experience, Python in Excel should be used for building aggregations table, create unique charts (Seaborn/Matplotlib), and apply Machine Learning models directly . Avoid using Python to replace simple Excel formulas in master data tables (e.g., SUM, VLOOKUP, SUMIFS or row-level calculations).Python in Excel runs in the cloud, so it can be significantly slower and unnecessarily complex for simple tasks.

Create aggregate table

1.Prepare data

Ensure your data range is clean and contains only necessary columns. Including a "Master Table" with hundreds of columns will severely slow down performance and make coding unnecessarily difficult. Always convert your data into a "Table" format (Ctrl+T) to reference column names easily.

 

To start using Python, input =PY( into a cell. This will turn the cell into a Python cell, allowing you to write and execute Python code directly.

In formular bar, select Output is “Excel value”. This will convert the Python results (like a DataFrame or List) into a visible table or values on your spreadsheet. If you leave it as “Python Object”, it will only show a small icon representing the data.

2. Create a Market Summary Table

Input this code into cell, remember to change the parameters following your table

#df :Define dataframe(python data table)

#XL(): acts as a bridge, converting your Excel Tables or Ranges into Python DataFrames.

df = xl("Table1[#All]", headers=True) 

# group data(sumifs),must declare ALL columns you intend to use or calculate

market_analysis = df.groupby('Market').agg({

    'COGS total': 'sum', #Sum column COGS total by Market, you can change to Mean(average),Max

    'Profit': 'sum',

    'Containers': 'sum',

    'Turnover': 'sum'

}).reset_index() #if no reset_index() here, the table will have 1 blank row.

# 2.add calculation columns: cost-profit/total container

market_analysis['COGS_per_Container'] = market_analysis['COGS total'] / market_analysis['Containers']

market_analysis['Profit_per_Container'] = market_analysis['Profit'] / market_analysis['Containers']

market_analysis['margin per container']=market_analysis['Profit_per_Container']/(market_analysis['Profit_per_Container'] +market_analysis['COGS_per_Container'])

# calculate total revenue for next formular ref

total_revenue = market_analysis['Turnover'].sum()

# Calculate Market Share

market_analysis['Market_Scale_%'] = (market_analysis['Turnover'] / total_revenue) 

# 3. sort and display

market_analysis = market_analysis.sort_values(by='COGS_per_Container', ascending=False)

market_analysis #this will displays the df(data table) to excel range

Result:

In the example above, Northern Alliance and Central Hub Global are the two markets with the largest Market Scale % (15% and 28%, respectively), yet their Margin per Container is significantly low (4% and 3%).

This raises a critical question: Why does the organization maintain business with these low-profit customers instead of focusing solely on high-margin markets?

1. Production Stability: Their massive order volumes ensure production continuity and stability, keeping the factory running at optimal capacity.

2. Internal Pricing: In the case of Central Hub Global, as it is the Parent Company, the transfer pricing is set low, naturally resulting in lower recorded profits.

3. High Order Efficiency (Northern Alliance): Despite the low buying price, Northern Alliance offers Low SKU Complexity and massive volumes. With no transportation or storage costs, they provide high order value efficiency—doing more with less effort.

4. Hidden Costs in Other Markets: While other markets show higher margins, they often involve hidden operational complexities not captured in the COGS, such as:

• Production downtime due to MOQ (Minimum Order Quantity) violations.

• High SKU complexity leading to warehouse and logistics overhead.

• Delayed ETD (Estimated Time of Departure) costs and inconsistent forwarder fees.

3. Create a Item group Summary Table

Input this code into cell, remember to change the parameters following your table

df = xl("Table1[#All]", headers=True) #thí

# group data(sumifs)

Product_analysis = df.groupby('Item Group').agg({

    'COGS total': 'sum', 

    'Profit': 'sum',

    'Containers': 'sum',

    'Turnover': 'sum'

}).reset_index() 

# 2.add calculation columns

Product_analysis['COGS_per_Container'] = Product_analysis['COGS total'] / Product_analysis['Containers']

Product_analysis['Profit_per_Container'] = Product_analysis['Profit'] / Product_analysis['Containers']

Product_analysis['margin per container']=Product_analysis['Profit_per_Container']/(Product_analysis['Profit_per_Container'] +Product_analysis['COGS_per_Container'])

total_revenue = Product_analysis['Turnover'].sum()

Product_analysis['Market_Scale_%'] = (Product_analysis['Turnover'] / total_revenue) 

# 3. sort and display

Product_analysis = Product_analysis.sort_values(by='COGS_per_Container', ascending=False)

Product_analysis

Result:

Interpret results: Item Group profitability 

1. The "High-Volume Hole" (Case: Desert Glaze): $80.4M in Revenue (9% Market Scale) but a $1.2M Loss.

Desert Glaze is a massive operation that is bleeding the company dry. With a $40,680 COGS/container, the production cost is far too high for its market price.

In reality:Desert Glaze is a type of product required complex processes, ufnortunately, this is the proprietary compound developed by the organization, it's the brand's signature. So Brand may considered "Process Re-engineering"

 

2. The "Efficiency Star" (Case: Timeless Timber): 39% Margin and $22,117 Profit per Container.

 Timeless Timber is the most efficient group. Even with a smaller scale (5%), it provides "Clean Profit."

In reality:Timeless Timber is made from popular and high-demand materials that customers love. Priority Capacity

 

3. The "Reliable Engine" (Case: Aether Alloy): 43% Market Scale ($372M) and a solid 23% Margin.

Aether Alloy is the backbone of the organization. It successfully combines high volume with high efficiency ($5,733 profit/container).

 This group provides the necessary cash flow to sustain the entire factory's operations.Priority Capacity

 

4. The "Operational Burden" (Case: Starlight Solder) 1,672 Containers but only 3% Margin ($672 profit/container).

 Starlight Solder requires massive labor and logistics for a tiny return. When profit per container is this low, any minor production error or shipping delay turns the group into a loss.  Exit or Sell.

 

5. Hidden Complexity (Case: Inner Infinity & Heritage Walnutt:

Inner Infinity is losing money (-6% Margin), and Heritage Walnut shows a relatively low margin (11%) compared to Aether Alloy.

These groups often involve High SKU Complexity and Low Packing Density, which "eats" the profit before it reaches the bank. Should considere outsource.

4. Common error :

When these happens, check 2 cases below:

1. The excel file is belong to a folder under non-office365 user

2. The account in the excel file is non-office365 user