Ask AI to Write a Python Script for an Excel Job

Every month, someone copies 12 sheets into one, deletes the same empty rows, and builds the same summary table. It takes an afternoon. A small Python script can do it in about two seconds, every time, without mistakes from tired eyes.

You do not need to learn Python first. In this course, an AI chatbot like ChatGPT, Claude or Gemini writes the script. Your job is to describe the task clearly, run the script and check the result. This lesson covers the first part: asking for a script that works the first time.

What You'll Learn

• Which Excel jobs are worth automating with Python
• What pandas and openpyxl are, in one line each
• The five things to tell the AI about your files
• A reusable prompt that gets a working script
• What a typical AI-written script looks like

When Python beats Excel

Excel is great for looking at data and doing one-off calculations. Python is better when a job is repeated or too big to do by hand. Good signs:

• You do the same steps every week or month.
• You combine many files or sheets into one.
• You copy, paste and clean the same messy columns again and again.
• You split one big sheet into many smaller files, like one per region or client.
• The file is so big that Excel becomes slow.

If a job takes you five minutes once a year, keep doing it in Excel. If it takes an hour every month, automate it.

Two words you will see

• pandas: a free Python tool for working with tables. It can read and write Excel and CSV files, filter rows, add columns and make summaries. Think of it as Excel without the screen.
• openpyxl: a free Python tool that works directly with .xlsx files, including formatting like bold headers, column widths and colors. pandas uses it behind the scenes to read and write Excel files.

You do not need to install anything yet. The next lesson runs everything in your web browser.

The five things to tell the AI

Most bad scripts come from vague requests. "Combine my Excel files" leaves the AI guessing about file names, sheets and columns. Tell it these five things:

1. Input: the file names or pattern, the sheet name, and where the header row is.
2. Columns: the exact column names, copied from your file.
3. Steps: what should happen, in order.
4. Output: the file name and sheet name you want back.
5. Where it runs: say "Google Colab" so the AI includes the steps to upload and download files there.

Also add: "Explain each step in a short comment" and "Do not change my original files." These two lines make the script easier to check and safer to run.

A prompt you can reuse

Here is a real example: combining monthly sales files. Change the details in capitals to match your own files. You can click Run with AI to see the kind of script you get back.

Notice the last rule. Asking the script to print row counts gives you a quick way to check the result, which you will use in lesson 3.

What the script will look like

You will get something like this. Do not worry about understanding every line yet. Just notice the comments that explain each step.

# Step 1: upload the Excel files from your computer
from google.colab import files
uploaded = files.upload()

import pandas as pd

all_tables = []
for file_name in uploaded:
    # Step 2: read the Sales sheet from each file
    table = pd.read_excel(file_name, sheet_name="Sales")
    # Step 3: add the month, taken from the file name
    table["Month"] = file_name.replace("sales_", "").replace(".xlsx", "")
    print(file_name, "rows:", len(table))
    all_tables.append(table)

# Stack all months into one table
combined = pd.concat(all_tables, ignore_index=True)

# Step 4: remove rows with no product
combined = combined.dropna(subset=["Product"])

# Save and download the result
combined.to_excel("sales_2026_combined.xlsx", sheet_name="All Sales", index=False)
print("Total rows:", len(combined))
files.download("sales_2026_combined.xlsx")

Read the comments from top to bottom and you can follow the whole job: upload, read each file, add the month, stack, clean, save, download. That is the skill this course builds: following and checking a script, not writing one from memory.

If the AI asks questions or guesses

Sometimes the AI will make an assumption, like "I assumed the header is in row 1." That is good. Read its assumptions and correct any that are wrong:

• "The header is actually in row 3, because rows 1 and 2 have the company name."
• "Some files have the sheet named Sheet1 instead of Sales."

Small corrections like these are normal. Iterate until the script matches your real files.

Your task for this lesson

Pick one boring Excel job from your own work or studies. Write down the five things: input, columns, steps, output and where it runs. Then paste the prompt above into any free chatbot with your details. Keep the script it gives you. You will run it in the next lesson.

No real job yet? Use the monthly sales example. The next lesson gives you sample files to practice with.

Key Takeaways

• Automate Excel jobs that are repeated, involve many files, or are too big for Excel.
• pandas handles tables in Python, and openpyxl handles Excel files and formatting.
• Tell the AI five things: input, columns, steps, output and where it runs.
• Ask for plain-English comments, no changes to original files, and printed row counts.
• Read the AI's assumptions and correct them before you run the script.