Blog

If you’ve ever thought, “There has to be a faster way to do this in Excel,” you’re in the right room. This guided tutorial shows you—step by step—how to automate Excel with ChatGPT. We’ll start simple (prompting ChatGPT to write formulas you can paste straight into Excel) and graduate to low-code automation patterns using Office Scripts, Power Query, and a small Python bridge.

You’ll leave with reusable snippets, a mini case study, and a deployment checklist. Let’s turn spreadsheet grind into a smooth, scalable workflow.

What you’ll build

A one-click “Sales Ops Automation” workflow that:

• Cleans and types your data
• Adds helper columns (Net Revenue, Status normalization, Category mapping)
• Generates stakeholder-ready summaries using ChatGPT
• Exports a daily/weekly report without manual fiddling

Prerequisites (zero-fluff)

1. Excel: Microsoft 365 (Windows/macOS/Web). You’ll need Tables, Power Query, Dynamic Arrays, LAMBDA/LET (Microsoft 365 or Excel 2021+ recommended). See Excel function references for XLOOKUP, LET, TEXTSPLIT, etc.
2. ChatGPT access:
   • Easiest path: Use ChatGPT in the browser to co-write formulas and text.
   • For automation: OpenAI API key (Team/Enterprise/API recommended for governance). OpenAI states that API data isn’t used to train models by default (opt-in needed). For enterprise/Team/edu offerings, business data isn’t used to train models by default. Review data controls before you send any sensitive data.
3. Optional accelerators (pick any):
   • Office Scripts in Excel (TypeScript automation with a no-code recorder; integrates with Power Automate).
   • Power Automate (cloud flows; Excel Online (Business) connector; hundreds of connectors).
   • Python (pandas) if you prefer a scriptable bridge.

Sample dataset

Part 1 — Fast wins with ChatGPT: “Formula co-pilot”

1) Normalize messy text

Goal: Trim spaces, standardize casing, remove non-breaking spaces from CustomerName.

Formula (Excel 365/2021+)

excel

=PROPER(TRIM(SUBSTITUTE(CLEAN([@CustomerName]),CHAR(160),"")))

Why it helps: CLEAN + SUBSTITUTE(…CHAR(160)) removes weird web characters; TRIM strips excess spaces; PROPER standardizes name casing.

2) Robust revenue math (after discount + shipping)

Add a new column NetRevenue:

excel

=ROUND([@Quantity]*[@UnitPrice]*(1-[@Discount]) + [@ShippingCost], 2)

Tip: Always ROUND money.

3) Bullet-proof lookups with XLOOKUP

Map SalesRep to territory from a separate Table RepMap:

excel

=XLOOKUP([@SalesRep], RepMap[Rep], RepMap[Territory], "Unknown", 0)

XLOOKUP handles exact matches by default and supports not-found messages.

4) Split “City, State” to two columns using TEXTSPLIT

If you had a combined field like CityState ("Mumbai, Maharashtra"):

excel

=TEXTSPLIT([@CityState], ", ")

Dynamic arrays spill cleanly.

5) Wrap logic in LET (clean, fast, maintainable)

excel

=LET(
 qty, [@Quantity],
 price, [@UnitPrice],
 disc, [@Discount],
 ship, [@ShippingCost],
 net, qty*price*(1-disc)+ship,
 ROUND(net,2)
)

LET names sub-expressions, improving performance and readability.

6) Create a reusable LAMBDA (turn formulas into functions)

Define Name Manager → New → Name: NETREV and Refers to:

excel

=LAMBDA(qty, price, disc, ship, ROUND(qty*price*(1-disc)+ship, 2))

Now call in the table:

excel

=NETREV([@Quantity], [@UnitPrice], [@Discount], [@ShippingCost])

(Managing/porting LAMBDAs is easier if you keep a “Definitions” sheet.)

Pro move: Build a “Library” workbook of your LAMBDAs and import/export as needed.

Part 2 — Low-code automation with Power Query (+ ChatGPT as your M-coauthor) – Automate Excel with ChatGPT

Why Power Query? It’s Excel’s built-in ETL engine; you can load CSVs/folders, clean, merge, and refresh with one click.

Steps (numbered, beginner-friendly)

1. Load data: Data → Get Data → From Table/Range → Sales.
2. Set data types: Mark Date/Text/Decimal types.
3. Trim/Clean: Transform → Format → Clean; Trim.
4. Add calculated column NetRevenue:
   • Add Column → Custom Column:
   powerqueryCopyEditeach Number.Round([Quantity]*[UnitPrice]*(1-[Discount]) + [ShippingCost], 2)
5. Normalize Status: Replace values (delivered, Delivered, DELIVERED → Delivered).
6. Close & Load to a new sheet as a Table.

A tiny M snippet to remove NBSPs and trim:

powerquery

= Table.TransformColumns(
    Source,
    {{"CustomerName", each Text.Proper(Text.Trim(Text.Clean(Text.Replace(_, Character.FromNumber(160), "")))), type text}}
)

Resource: Power Query M language reference/spec if you want deeper control.

Heads-up: Teach ChatGPT to “think in M.” Paste an example column + desired transformation and ask it to propose the M expression. You’ll learn by shipping.

Part 3 — Office Scripts: one-click clean-and-calculate – Automate Excel with ChatGPT

Office Scripts lets you record or write a TypeScript routine and run it in Excel (and from Power Automate). Perfect for repeatable tasks.

Example script (drop-in starter)

1. In Automate tab → New Script.
2. Paste and save as CleanAndCompute.ts.

typescript

function main(workbook: ExcelScript.Workbook) {
  const ws = workbook.getWorksheet("SalesData") ?? workbook.getActiveWorksheet();
  const tbl = ws.getTable("Sales");

  // Ensure data types/formats
  tbl.getColumnByName("OrderDate").getRangeBetweenHeaderAndTotal().setNumberFormatLocal("yyyy-mm-dd");

  // Add NetRevenue if missing
  if (!tbl.getColumns().some(c => c.getName() === "NetRevenue")) {
    tbl.addColumn(-1, { name: "NetRevenue", values: [] });
  }

  // Compute NetRevenue via Excel formula for each row
  const headers = tbl.getHeaderRowRange().getValues()[0];
  const colIndex = (name: string) => headers.indexOf(name) + 1;

  const qCol = colIndex("Quantity");
  const pCol = colIndex("UnitPrice");
  const dCol = colIndex("Discount");
  const sCol = colIndex("ShippingCost");
  const nCol = colIndex("NetRevenue");
  const body = tbl.getRangeBetweenHeaderAndTotal();

  // Write formula to the NetRevenue column (uses structured refs)
  body.getColumn(nCol - 1).setFormulaR1C1(
    `=ROUND(RC[${qCol - nCol}]*RC[${pCol - nCol}]*(1-RC[${dCol - nCol}])+RC[${sCol - nCol}],2)`
  );

  // Trim/Clean CustomerName in-place
  const custRange = body.getColumn(colIndex("CustomerName") - 1);
  const values = custRange.getValues().map(r => {
    const v = (r[0] ?? "").toString().replace(String.fromCharCode(160), " ");
    return [v.trim().replace(/\s+/g, " ").replace(/\b\w/g, c => c.toUpperCase())];
  });
  custRange.setValues(values);
}

Why this is great: One click. No copy-paste. Reusable across files. You can attach it to a button or trigger it from a flow. API and fundamentals docs here.

Part 4 — ChatGPT for narrative summaries (Power Automate or Python bridge)

Sometimes you need a daily/weekly digest: “Top products, revenue by region, anomalies, and next actions.” Two ways:

A) Power Automate (no/low code)

• Use “When a file is created or modified” (OneDrive/SharePoint) → “List rows present in a table” (Excel Online (Business)) → Compose/Format data → ChatGPT (OpenAI) HTTP action → Send email.
• The Excel connector has known behaviors (e.g., header naming constraints, row limits). Check connector docs for gotchas.

Flow prompt (body excerpt):

pgsql

You are a helpful analyst. Given JSON of the 'Sales' table,
1) compute total NetRevenue and YoY/DoD if available,
2) list top 5 products by NetRevenue,
3) flag any State with returns > 5% of orders,
4) suggest 3 actions.

Return a concise Markdown report under 200 words.

B) Python (pandas + OpenAI)

python

# pip install pandas openpyxl requests
import pandas as pd, os, requests, json

API_KEY = os.getenv("OPENAI_API_KEY")
df = pd.read_excel("sales.xlsx", sheet_name="SalesData")
df["NetRevenue"] = round(df["Quantity"]*df["UnitPrice"]*(1-df["Discount"]) + df["ShippingCost"], 2)

summary_payload = {
  "model":"gpt-4.1-mini",
  "input":[{
    "role":"user",
    "content": f"""
    Summarize this sales table (CSV below). 
    1) total net revenue, 2) top 5 products, 3) states with return rate > 5%, 
    4) 3 next actions. Keep it under 200 words.
    CSV:
    {df.to_csv(index=False)[:15000]}
    """
  }]
}

resp = requests.post(
  "https://api.openai.com/v1/responses",
  headers={"Authorization": f"Bearer {API_KEY}", "Content-Type":"application/json"},
  data=json.dumps(summary_payload)
)
print(resp.json()["output_text"])

Data governance note: Review OpenAI data-use controls and enterprise privacy if you plan to send real business data. API data is not used for training by default; enterprise/Team controls add retention and compliance options.

(Developers can also explore the Assistants API and built-in tools like file search or code interpreter for richer workflows; see the overview and tools pages.)

Part 5 — Walkthrough (numbered) to ship your first automation

Scenario: Weekly email to Sales leadership with cleaned KPIs + highlights.

1. Create the Sales table (structure above).
2. Power Query: Import → type columns → add NetRevenue custom column → replace values in Status. Refresh = one click.
3. Enhance with formulas: Add a ReturnsFlag: excelCopyEdit=IF([@Status]="Returned",1,0)
4. KPIs with dynamic arrays (on a Summary sheet):
   • Total Revenue: excelCopyEdit=SUM(Sales[NetRevenue])
   • Top 5 Products: excelCopyEdit=TAKE(SORT(UNIQUE(Sales[Product]),1,TRUE),5)
   • Return Rate: excelCopyEdit=DIVIDE(SUM(Sales[ReturnsFlag]), ROWS(Sales[OrderID]))
5. Wrap helpers with LET (clean and fast): excelCopyEdit=LET( t, Sales[NetRevenue], SUM(t) )
6. Office Script: Add and run CleanAndCompute.ts to standardize names, ensure formulas, and round values.
7. Generate narrative:
   • Power Automate route (email stakeholders) or
   • Python route (console/generate Markdown paste).
8. Schedule:
   • Power Automate: trigger every Monday 08:00 IST, run Excel step + ChatGPT summary + send email. (Connector list for region availability).
9. QA: Validate totals vs. manual pivot; archive outputs; iterate on prompts.

At least 6 real snippets (you get more)

Excel (Text cleanup)

excel

=PROPER(TRIM(SUBSTITUTE(CLEAN([@CustomerName]),CHAR(160),"")))

Excel (Dynamic categorization via nested LOOKUP)

excel

=XLOOKUP([@Product], CatMap[Product], CatMap[Category], 
         XLOOKUP([@Category], CatDefault[Category], CatDefault[SuperCategory], "Other"))

DAX (if you export to Power BI later)

DAX

Net Revenue :=
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[Discount]) + Sales[ShippingCost] )

Return Rate % :=
DIVIDE ( SUM ( Sales[ReturnsFlag] ), COUNTROWS ( Sales ) )

SQL (if your data originates in a database)

sql

-- Normalize status, aggregate by product
WITH t AS (
  SELECT
    Product,
    CASE 
      WHEN LOWER(TRIM(Status)) LIKE 'return%' THEN 'Returned'
      WHEN LOWER(TRIM(Status)) LIKE 'deliver%' THEN 'Delivered'
      ELSE 'Pending' 
    END AS StatusNorm,
    Quantity, UnitPrice, Discount, ShippingCost
  FROM dbo.RawSales
)
SELECT 
  Product,
  SUM(Quantity*UnitPrice*(1-Discount)+ShippingCost) AS NetRevenue,
  SUM(CASE WHEN StatusNorm='Returned' THEN 1 ELSE 0 END)*1.0/COUNT(*) AS ReturnRate
FROM t
GROUP BY Product
ORDER BY NetRevenue DESC;

Power Query M (custom column, as used earlier)

powerquery

= Table.AddColumn(Source, "NetRevenue", each Number.Round([Quantity]*[UnitPrice]*(1-[Discount]) + [ShippingCost], 2), type number)

Office Scripts (TypeScript) — provided above, reusable.

Python (pandas + OpenAI Responses API) — provided above.

Troubleshooting (common issues → causes → fixes)

Performance tips & best practices

• Prefer LET + named ranges for heavy formulas—reduces recalculation overhead.
• Push heavy transforms to Power Query (types, merges, splits). It’s faster than cell-by-cell formulas for large volumes.
• Use Tables everywhere: structured references = fewer broken formulas.
• Minimize volatile functions (INDIRECT, OFFSET) in big models.
• Cache summaries: Use PivotTables or Power Query aggregations, then ask ChatGPT to summarize the result, not the raw detail.
• Governance: For production, prefer OpenAI API / Team / Enterprise (admin controls, retention options) over ad-hoc copy/paste into consumer chats.
• Document your LAMBDAs: Keep a “Definitions” sheet and export/import strategy.

Security & privacy notes (non-negotiable)

• Minimize PII in prompts. Mask names/IDs.
• Use API with org controls for real data; consumer ChatGPT is fine for synthetic or schema-only prompts. Review usage policies for what’s permitted.
• Retention awareness: Understand how your plan handles retention and opt-out/opt-in. (Enterprise/Team/API: not used for training by default.)
• Least-privilege storage: Store API keys in secure secrets, never in worksheets.

Comparison box — ChatGPT API + Office Scripts vs Power Automate (cloud flows)

Connector details and constraints: see Excel Online (Business) connector docs.

Mini case study — “From 5 hours to 20 minutes, weekly”

Context: A retail ops analyst (India & USA regions) manually cleaned a 1,500-row weekly CSV, calculated net revenue, flagged returns, and wrote email summaries. Time: ~5 hours/week.

Automation:

• Power Query cleans + types on refresh.
• Office Script computes NetRevenue, trims names, ensures formats.
• Power Automate triggers every Monday 08:00 IST → pulls new file → feeds a summary prompt to ChatGPT via API → emails leadership.

Impact after 4 weeks:

• Processing time: ~20 minutes/week (96% faster).
• Error rate: From “occasional mismatches” to zero formula drift (no manual copy).
• Stakeholder satisfaction: Open/click rates up +28% (short, consistent AI-drafted summaries).
• Scalability: Added a second region with no extra analyst time.

FAQ: Automate Excel with ChatGPT

1) Can I automate Excel without coding?
Yes. Start with Power Query (clicks, no code) and Tables. For narratives, copy table summaries into ChatGPT. For scheduled jobs, Power Automate is no/low code.

2) Do I need VBA?
Not necessarily. Prefer Office Scripts for cross-platform, modern automation. VBA still works great in legacy/on-prem environments.

3) Is my company data safe with ChatGPT?
Use the API, Team, or Enterprise. API data isn’t used for training by default; enterprise offerings add retention and admin controls. Avoid pasting sensitive data into consumer chats.

4) What if XLOOKUP isn’t available?
Use INDEX/MATCH or upgrade. (XLOOKUP is documented in Microsoft 365.)

5) Will this work on Mac or the web?
Yes—Excel for Microsoft 365 on Mac and the web supports Office Scripts and modern functions (availability evolves; check docs).

6) Power Automate can’t update my table—why?
Numeric-only headers, row caps, or file locks can break flows. Rename headers and review connector notes.

7) Can ChatGPT write Power Query M code?
Yes. Provide examples and the exact transformation; validate the output. Use the M reference/spec to tune.

8) How do I keep formulas fast?
Use LET, reduce volatiles, and aggregate with Pivots/Power Query before asking ChatGPT to summarize.

9) Can I attach files directly to ChatGPT for analysis?
With the API/Assistants, you can send files or pre-summarized text for analysis; see OpenAI docs for file tools.

10) What should I screenshot for my tutorial or SOP?
Follow the “Screenshots list” above—hit tables, PQ steps, script, flow, and final email.