5 ChatGPT Prompts That Save Hours in Microsoft Excel

If you live in spreadsheets all day, pairing ChatGPT with Microsoft Excel is the closest thing to hiring a junior analyst who never sleeps. Used well, ChatGPT writes your formulas, cleans your data, builds your pivot tables, drafts your charts, and even codes your VBA macros — in seconds. The trick is knowing exactly what to ask.

Below are five copy-and-paste prompts I teach in my live Excel and AI webinars. Each one is built around the same idea: be specific about your data, your goal, and the format you want back. Drop them into ChatGPT, replace the placeholders with your own column names, and watch hours of busy work disappear.

Why Combine ChatGPT With Microsoft Excel?

Excel is a brilliant calculator with a steep memory tax. Most users know maybe 5% of its functions and reach for Google every time they need INDEX/MATCH, XLOOKUP, LET, LAMBDA, or anything dynamic-array-related. ChatGPT removes that friction. Instead of hunting through forum posts, you describe what you want in plain English and get a working formula tailored to your sheet — usually with an explanation you can actually learn from.

That last point matters: when you use ChatGPT as a tutor instead of just a dispenser, your Excel skills compound. Every prompt is a quick lesson.

Prompt 1: The Formula Translator

This is the prompt I reach for most often. It turns a plain-English description into a clean Excel formula.

Prompt: “You are an Excel expert. My data is in the range A1:F500 with headers in row 1: Date, Region, Product, Rep, Units, Revenue. Write an Excel formula that returns total Revenue for the West region in 2026 only. Use modern Excel functions (XLOOKUP, FILTER, SUMIFS as appropriate) and explain in two sentences why you chose that formula.”

Why it works: you give ChatGPT the exact range, the headers, and the desired output. The “explain in two sentences” line keeps the answer crisp and teaches you something each time.

Prompt 2: The Data Cleanup Crew

Messy customer lists, inconsistent capitalization, mystery whitespace, dates stored as text — every business spreadsheet has them. Let ChatGPT write the cleanup steps for you.

Prompt: “I have a 12,000-row Excel sheet with customer names in column B, email in column C, and phone in column D. Names are inconsistent (mixed case, extra spaces), emails sometimes have trailing spaces, and phone numbers are in three different formats. Give me a step-by-step cleanup plan using only built-in Excel functions and Power Query, with the exact formulas or M code I should use.”

You will get a tidy, numbered playbook — TRIM, PROPER, CLEAN, SUBSTITUTE, and a few Power Query transformations. Run it once and your list is presentation-ready.

Prompt 3: The Instant Pivot Table Coach

Pivot tables intimidate a surprising number of business users. ChatGPT shortens the learning curve.

Prompt: “Given an Excel table named tblSales with columns Date, Region, Product, Rep, Units, Revenue, walk me through building a pivot table that shows Revenue by Region in rows, Quarter in columns, and a slicer for Product. List the exact clicks and which fields go into Rows, Columns, Values, and Filters.”

You get a click-by-click recipe. Even better, ask ChatGPT to follow up with three insights you should look for once the pivot is built — instant analysis prompts you can take into your next meeting.

Prompt 4: The Chart Recommender

Choosing the right chart is half the battle. ChatGPT is a surprisingly good visualization advisor.

Prompt: “I want to show a senior leadership audience how monthly recurring revenue has changed across five product lines over the past 24 months, and highlight the two product lines that are accelerating. Recommend the best Excel chart type, the data layout I need, and three formatting tips that will make the chart easier to read in a board pack.”

The output usually nudges you toward a small-multiple line chart or a slope chart with clean labels — both far more effective than the default cluttered combo chart Excel hands you.

Prompt 5: The VBA and Office Scripts Assistant

You do not need to be a developer to automate Excel anymore. Describe the task, and ChatGPT writes the macro.

Prompt: “Write a VBA macro for Microsoft Excel that loops through every worksheet in the active workbook, finds the last used row in column A, and copies rows where column F equals ‘Overdue’ into a new sheet called ‘Action List’. Add comments so a non-developer can read it, and include error handling.”

Paste it into the VBA editor (Alt+F11), tweak the column letters to match your file, and you have a reusable automation. Office Scripts users can ask for the equivalent in TypeScript instead.

Tips for Getting Better ChatGPT Answers Every Time

A few habits will dramatically improve every Excel-related prompt you write. Tell ChatGPT the version of Excel you use (Microsoft 365, 2021, or web), name your range or table, paste a small sample of your headers, and always state what the answer should look like — a single formula, a step-by-step recipe, or a macro. The more context you give, the less back-and-forth you need.

One more pro tip: when a formula does not work, paste the error message back into ChatGPT and ask for a fix. Most of the time you get a corrected formula in one round.

Ready To Go Deeper?

These five prompts barely scratch the surface of what AI plus Excel can do for your business. If you want hands-on training that walks through prompts like these — plus Microsoft Power BI, Microsoft Copilot, and Claude workflows — join one of my live webinars or browse the full catalog at PCWebinars.com. Bring your toughest spreadsheet, and let’s automate it together.