• Go to xlwings Practice Lab
• Download PDF Guide

This HTML page was converted from the original PDF. Some images and complex layouts may not appear correctly. For the best experience, please download the PDF above.

xlwings Lite: The AI Way

Your Quick Start to Python In Excel

Coding Experience not Required

Module 01

Data Transformations, Visualizations and AI Generated variables

Download Starter Kit

app.tigzig.com

TIGZIG: Co-Analyst OSS xlwings Lite Tools Analyze GPTs MCP

xlwings Lite: Practice Lab

Learn xlwings Lite by practical examples

💡 Quick Start Resources: Download our AI-ready instructions, view guides, and get example workbooks to jumpstart your xlwings Lite journey!

Example Workbooks & Guides

Setup AI

Use Google AI Studio
A powerhouse of AI Tools. Free.

🔗 [aistudio.google.com])(undefined)

[The image shows a partial screenshot of the AI Studio interface with the text "Welcome to AI Studio" and a prompt input box. On the right side, there is a "Run settings" panel with the following details:]

1. Upload
   AI_CODER_INSTRUCTIONS.md

2. Put Starter Prompt from example workbook and hit enter

Prepare Your Data

Best Practice: Setup data as a ‘Table’

Sample data in Excel workbook

Install xlwings Lite Add-in

Installation

1. Click on the Add-ins button in Excel. Usually, it is located on the Home tab, but if you are using an older version of Excel, it could be on the Insert tab.
2. Search for xlwings
3. Click on Add to install:

Image from lite.xlwings.com

Asking AI for Data Transformations

Prompt 1

Instruct AI for metrics and calculations you need

Prompt 1: Creating Derived Variables

See attached few sample rows. Data is formatted as table name RBICC. Gimme code to add the following fields:

• Totals for credit card value and volume
• Average per credit card for value and volume

Keep in mind that the volume is in actuals and value is in Rs. '000. I want the averages as actuals. In addition – I want to do profiling of the banks. So add additional derived variables that could be helpful and insightful – add between 5–10 additional variables based on your judgment.

• Describe new fields in natural language
• Specify data gotchas
• ALWAYS show AI the data in the very first prompt
• Ask AI to create insightful variables, if required

All Prompts in Example Workbook

Run the AI Generated Code

AI will give you a code – paste that into main.py and run it (it will show up the name of function created)

Python Script for xlwings Lite

Generated python

import pandas as pd
import numpy as np
import xlwings as xw
from xlwings import script

import matplotlib.pyplot as plt
import seaborn as sns
import os
import tempfile
from matplotlib.ticker import FuncFormatter

# --- Mandatory Helper Function (as per guidelines) ---
def find_table_in_workbook(book: xw.Book, table_name: str):
    """Searches all sheets for a table with the given name"""
    for sheet in book.sheets:
        if table_name in sheet.tables:
            return sheet.tables[table_name]
    return None

Screenshot content transcription (Excel and xlwings Lite add-in UI):

Excel ribbon and xlwings Lite add-in toolbar (textual representation):

• Ribbon tabs visible:

  • Design
  • Comments
  • Share

• Ribbon groups visible:

  • Sort & Filter
  • Find & Select
  • Add-ins
  • Analyze Data
  • Copilot
  • Create a PDF
  • Excel Labs
  • xlwings Lite

• xlwings Lite add-in pane:

  • Green button labeled create_visualizations
  • Tabs: main.py (checked), requirements.txt

Note: The numbers in the table appear to be data values or counts but their exact meaning is not explicitly stated in the screenshot.

Validate Outputs

The calculations we specifically requested

8 Extra variables from AI based on its judgment

Tip: For AI-derived variables, you can push the boundaries by increasing the variable count, or focus on a particular type of metric—e.g., % compositions, market share, etc.

What if you get an error?

xlwings Lite

import pandas as pd
import numpy as np
import xlwings as xw
from xlwings import script
import matplotlib.pyplot as plt
import seaborn as sns
import os
import tempfile
from matplotlib.ticker import FuncFormatter

# --- Mandatory Helper Function (as per guidelines) ---
def find_table_in_workbook(book: xw.Book, table_name: str):
    """Searches all sheets for a table with the given name"""
    for sheet in book.sheets:
        if table_name in sheet.tables:
            return sheet.tables[table_name]
    return None

File "/lib/python3.12/site-packages/xlwings/pro/udfs_officej":
    await func(*args, **kwargs)
File "/home/pyodide/main_editor.py", line 148, in create_visu
    raise e
File "/home/pyodide/main_editor.py", line 65, in create_visu
    raise Exception("FATAL: Table 'RBICC' not found.")
Exception: FATAL: Table 'RBICC' not found.

Copy the error, paste it into the AI, and ask it to fix it.
AI will share back the revised code or let you know if something required from your end.

Creating Profile Summary

Prompt 2

Describe one or more summarizations – across any level and the KPI’s required

Prompt 2 : Creating Profile Summary

Now give me a nicely formatted summary showing all the profiling variables grouped by category. Use the profiling variables I had asked for, plus the ones you had created.
Put that in a new sheet called RBI_SUMM. Delete any existing sheet if it exists.

But keep in mind - you can’t just do the average of the average by using the earlier derived variables. You’d need to recompute them at the grouped level.

• AI remembers past codes and conversations. You don’t need to specify all the variables one by one.

• But validate output - once in a while it ‘forgets’

• Specify data gotchas

All Prompts in Example Workbook

Profile Summary

Profile Summary output in a new sheet titled
RBI_SUMM

Tip: Ask AI to create as many cuts as you need and have it place the tables one below the others.

All Prompts in Example Workbook

Creating Charts

Prompt 3

Prompt 3 : Creating Charts

Gimme the following charts, all nicely formatted and all. Paste them one below the other into a new sheet called charts.

1. Correlation matrix for all the numeric variables (take only the original variables)
2. Bar chart of top 10 banks by average credit card value
3. Combo bar chart – 2 axis – from the profiling table you created for average volume and value per card
4. Box plots for all the original numeric variables

Tip: Get the basic charts out, and then go for formatting – label fonts, title placement, axis etc

Tip: To get multi-panel chart ask AI to e.g. keep the bar chart as the main chart and add the line chart as a subplot

All Prompts in Example Workbook

Refine Charts

⚠️ It did not place the charts one below the others. Share the image and ask it to fix it.

Tip: Get the basic chart out and share instruction to refine the visuals. E.g

• Reduce correlation matrix size by ~50%
• Increase chart title size and place it top right
• Remove Y-axis line and ticker
• Reduce legend size and keep it on top right

All Prompts in Example Workbook

Validate Always

Validations are sacrosanct – AI or no AI

AI’s make mistakes – from formulas to logic.
Far less than humans – but it happens
Always validate output

# 4. Calculate requested Totals and Averages
print("🧮 Calculating totals and averages...")

# --- Totals ---
df['TOTAL_CC_TXN_VOLUME_NOS'] = df['CREDIT_CARD_POS_TXN_VOLUME_NOS'] + df['CREDIT_CARD_ECOM_VOLUME_NOS']

# Value is in '000s, so we calculate total in '000s first
df['TOTAL_CC_TXN_VALUE_AMT_000'] = df['CREDIT_CARD_POS_TXN_VALUE_AMT'] + df['CREDIT_CARD_ECOM_VALUE_AMT']

# --- Averages (in actuals) ---
# Convert total value to actuals for average calculation
total_value_actual = df['TOTAL_CC_TXN_VALUE_AMT_000'] * 1000

df['AVG_TXN_VOLUME_PER_CARD'] = df['TOTAL_CC_TXN_VOLUME_NOS'] / df['CREDIT_CARDS_NOS']
df['AVG_TXN_VALUE_PER_CARD_ACTUAL'] = total_value_actual / df['CREDIT_CARDS_NOS']

Best Practice: Review the core parts of the code e.g. formulas – they will look just like Excel formulas and intuitive to understand

Tip: For complex logic and formulas, ask AI to walk you through it step by step

Working with AI

Top 4 Tips

• Show what you see
  Share sample records and screen shots of what you see

• One Step at a Time
  Avoid too many things in one instruction.

• Break the App
  Run stress tests to identify scenarios that could mess up your automation

• Run AI Audit
  Ask AI to identify problems with code and logic

Power User Tip

Dictate Your Prompts

Typing long, detailed instructions is slow. Use your operating system's built-in dictation to work faster.

Windows ![Windows logo] and Apple ![Apple logo]

On Windows: Press Win + H to start Voice Typing.

On macOS: Press the Microphone key (F5) or press the Fn key twice.

Ask AI to be Your Guide

Don’t hesitate to ask the simple questions. The AI's only job is to help

"What are these df and def things in the code?"

"I don’t get it. Can you explain in a different way"

It will not judge you
It will be patient
It will not give up till you get it

TIGZIG Co-Analyst

Get the Starter Kit
and All Future Modules
Free

app.tigzig.com

![Open Source]

Amar Harolikar
Specialist – Decision Sciences & Applied Gen AI
Building Practical AI Tools for Analytics & Data Science