Published on

How to Use ChatGPT for Excel: The Complete 2026 Guide

Authors
  • avatar
    Name
    PromptShelf Editorial
    Twitter

Learning how to use ChatGPT for Excel usually starts with disappointment. You ask for a formula and get one back that almost works. It references the wrong column, assumes a function your version does not have, or quietly breaks on the one row that matters. The tool is not the problem. The input is. ChatGPT cannot see your spreadsheet, so it fills the gaps with guesses.

Used well, ChatGPT is the fastest way to write a formula you half-remember, fix a #VALUE! error you have been staring at for ten minutes, or plan a pivot table without trial and error. This guide covers how to use ChatGPT for Excel across seven steps, from describing your data to writing macros, with copy-paste prompts and one prompt I ran live so you can see exactly what comes back. It is written for analysts, accountants, operations people, and anyone who lives in spreadsheets and wants to move faster without learning to code.

What ChatGPT can and cannot do with Excel

ChatGPT is strong at the parts of Excel that are really language problems. Writing a formula is translating an intention ("sum sales for the West region in Q1") into syntax. Explaining an error is reading a message and knowing the usual causes. Planning a pivot table is deciding which fields go where. ChatGPT does all of that well because it has seen millions of examples.

What it cannot do is see your file. It does not know your column headers, your sheet names, your data types, or that column D is text formatted to look like numbers. The free tier also cannot run Excel for you, so it cannot confirm a formula returns the right answer. That means every output is a draft you test, not a result you trust. One more limit worth stating plainly: ChatGPT will invent function names and cell behaviors that sound right and are wrong, so you verify before you ship. With those boundaries set, here is the workflow.

Step 1: Describe your data before you ask for anything

The single biggest upgrade to your results is telling ChatGPT what your spreadsheet looks like before you ask for a formula. Give it the sheet name, the columns and what each holds, the data types, and your Excel version. Excel 365 has functions like XLOOKUP and FILTER that Excel 2019 does not, and a formula for the wrong version fails on paste.

A 20-second setup message saves three rounds of "that returns an error." Use this:

Prompt: "I'm working in Microsoft Excel 365 on Windows. My data is on a sheet named 'Sales'. Columns: A = Date (real dates), B = Rep (text), C = Region (text), D = Product (text), E = Amount (currency). Data runs from row 2 to row 5000, row 1 is headers. From now on, write every formula to match this exact layout and tell me which cell to put it in. Confirm you understand before I ask my first question."

Telling it to confirm first stops it from guessing. Once it has your structure, every formula afterward lands closer on the first try. Keep that chat open for the whole session so you do not re-explain your sheet each time.

Step 2: Get formulas written for your exact layout

This is what most people come for. Describe the result you want in plain English, name the columns, and ask for the formula plus a one-line explanation. The explanation matters because it tells you whether ChatGPT understood the task, and you can catch a wrong assumption before pasting.

Prompt: "You are an Excel expert. Using my 'Sales' sheet (A=Date, C=Region, E=Amount), write one formula that sums Amount for rows where Region is 'West' and the Date falls in Q1 2026 (January 1 to March 31, 2026). I'm on Excel 365. Give me the formula, tell me which cell to put it in, and explain in one sentence how it handles the date range."

Ask for one formula, not five options. If you want alternatives ("can you do this without a helper column?"), ask as a follow-up. Always check the column letters in the returned formula against your real sheet, because that is where ChatGPT guesses most often.

What this prompt actually returns (tested 2026-06-15)

We ran the Step 2 prompt on free ChatGPT exactly as written above, with the example brief baked in (sum Amount for Region "West" in Q1 2026 on the 'Sales' sheet). Reproduced verbatim:

Put this formula in the cell where you want the Q1 2026 West-region total to appear (for example, G2):

=SUMIFS(Sales!E:E,Sales!C:C,"West",Sales!A:A,">="&DATE(2026,1,1),Sales!A:A,"<="&DATE(2026,3,31))

How it handles the date range: It sums values in column E only for rows where the Region in column C is "West" and the Date in column A is between January 1, 2026 and March 31, 2026, inclusive.

Editor's notes. The formula is correct. SUMIFS is the right function here, and the date-range pattern, using ">="&DATE(2026,1,1) paired with "<="&DATE(2026,3,31), is the reliable way to bound dates because it builds real date values instead of comparing against text. The bounds are inclusive, which matches the brief. Two things to check before you trust the number: this only works if column A holds real dates, not dates stored as text, because text dates make the criteria silently return 0 with no error to warn you. And the whole-column references (A:A, C:C, E:E) are fine on a small sheet but recalculate slower on large ones, so tighten them to your real row range, like E2:E5000, if the file is big. The formula is also hard-coded to 2026, so swap the DATE arguments when you reuse it for another period.

Step 3: Fix formulas and errors

When a formula returns #VALUE!, #REF!, #N/A, or just the wrong number, ChatGPT is faster than searching forums, as long as you paste three things: the exact formula, the exact error, and what you expected to happen. Without the error text it guesses; with it, it usually names the cause on the first reply.

Prompt: "This Excel formula returns #N/A and I expected it to return a price. Here is the formula exactly as written: =VLOOKUP(F2, Products!A:C, 3, FALSE). F2 contains a product code like 'SKU-1043'. The Products sheet has codes in column A. Walk me through the three most likely causes of the #N/A in order of probability, and give me the corrected formula for each. I'm on Excel 365."

Asking for ranked causes is the trick. You get a short diagnostic list (trailing spaces, text-versus-number mismatch, the lookup value not existing) instead of one guess that may not be your case. When you find the fix, paste it back and ask it to explain why it worked so the lesson sticks.

Step 4: Translate between functions and versions

Spreadsheets accumulate old formulas. You inherit a workbook full of nested IF statements or a VLOOKUP that breaks every time someone inserts a column. ChatGPT is good at converting one approach to a cleaner one and at downgrading a formula for an older Excel version a colleague still uses.

Prompt: "Convert this nested IF formula into a cleaner equivalent using IFS or a lookup table, whichever is more maintainable, and explain the trade-off in one sentence. Formula: =IF(B2>90,"A",IF(B2>80,"B",IF(B2>70,"C",IF(B2>60,"D","F")))). I'm on Excel 365. Keep the same grade boundaries."

This is also how you future-proof a fragile VLOOKUP. Ask it to rewrite the formula as XLOOKUP so inserting columns does not break it, or to rewrite an XLOOKUP as INDEX/MATCH for a teammate on Excel 2016. State both versions explicitly so it gives you something that actually pastes.

Step 5: Plan and build pivot tables

ChatGPT cannot click through the PivotTable dialog for you, but it can tell you exactly which fields to drag where, which saves the guess-and-check most people do. Describe your columns and the question you want answered, and ask for the field layout in Rows, Columns, Values, and Filters.

Prompt: "I have a sales table with these columns: Date, Rep, Region, Product, Amount. I want a pivot table that shows total Amount by Region (as rows) broken out by Product (as columns), with a filter for year. Tell me exactly which field goes in Rows, Columns, Values, and Filters, what aggregation to use on Amount, and one formatting tip to make it readable. I'm on Excel 365."

You get a step-by-step field map you can build in under a minute. Follow up with "how do I add a calculated field that shows each region's share of total sales as a percentage?" when you need more than raw totals.

Step 6: Write macros and Office Scripts

For repetitive jobs, ChatGPT writes VBA macros and Office Scripts (the JavaScript-based automation in Excel on the web and 365). You do not need to read code to use them, but you do need to describe the task precisely and test on a copy of your file first, because a macro can change data in ways Ctrl+Z will not fully undo.

Prompt: "Write an Excel VBA macro that loops through every worksheet in the active workbook and, on each sheet, deletes any fully blank rows in the used range. Add a comment above each block explaining what it does, and tell me how to run it from the Developer tab. Assume I have never used VBA before."

Always run a generated macro on a duplicate workbook the first time. Read the comments, confirm it does only what you asked, and keep an untouched backup until you trust it. If it errors, paste the error and the line number back and ask for a fix.

Step 7: Clean and transform messy data

Exported data arrives messy: inconsistent capitalization, dates stored as text, names in one column that need splitting, duplicates. ChatGPT can hand you the formula or the Power Query step to fix each one. Describe the mess and the target state.

Prompt: "Column A has full names like 'john SMITH' in mixed capitalization. I want column B to show 'John Smith' in proper case, and columns C and D to hold the first and last name separately. Give me the formulas for B, C, and D for Excel 365, and note any row that would break if someone has a middle name or a single-word name."

Asking it to flag edge cases ("what breaks if there's a middle name?") is what separates a usable answer from one that corrupts 30 rows you do not notice until later. For larger cleanups, ask "should I do this with formulas or Power Query?" and it will tell you when the dataset is big enough to justify the extra setup.

A short workflow: from raw export to a clean summary

Here is how the steps fit together on a real task. Say you export a CSV of last quarter's orders and need a one-page summary by region for a Monday meeting.

Start by opening the file and telling ChatGPT your structure, the Step 1 move. Paste your column headers, say what each one holds, and name your Excel version. This takes twenty seconds and it is the difference between formulas that paste cleanly and a back-and-forth of errors.

Next, clean what is broken. If the dates came in as text, ask for the fix from Step 7. If names are in mixed case, ask for the proper-case formula. Do the cleanup before any analysis, because a pivot table built on text-formatted dates groups them wrong and you will not catch it until someone questions the numbers.

Then write the summary formulas. Ask for the regional totals using the Step 2 pattern, one formula at a time, and check each against a couple of rows you can add up by hand. When you need the breakdown by product or by month, move to a pivot table and ask ChatGPT for the exact field layout from Step 5.

Finally, if this is a report you will rebuild every quarter, ask for an Office Script or macro from Step 6 that handles the repetitive parts: the blank-row cleanup, the formatting, the same three formulas. Test it on a copy. Next quarter the job takes five minutes instead of an hour.

The pattern underneath all of it is the same. Describe precisely, ask for one thing, verify before you trust it, then automate what repeats. That order is what keeps ChatGPT useful instead of a source of confident, wrong answers.

Common mistakes to avoid

The mistakes that waste the most time are predictable.

Not stating your Excel version. A FILTER or XLOOKUP formula is useless on Excel 2019, and you will not know why it errors until you check. Say your version in the first message.

Trusting the answer without testing. ChatGPT cannot run your sheet. It is confident even when wrong about a function's behavior. Paste the formula, check it against a few rows you can verify by hand, then roll it out.

Pasting confidential data into the free tier. Customer names, salaries, account numbers, and anything covered by an NDA should not go into a public chat tool. Describe the structure with fake values instead. The formula is the same whether the example name is "John Smith" or a real client.

Accepting invented functions. If a formula uses a function you have never seen, search Microsoft's documentation before relying on it. ChatGPT occasionally invents plausible-sounding functions that do not exist.

Reusable prompts to keep

These are the prompts worth saving for any Excel session.

Prompt: "You are an Excel 365 expert. Before I ask anything, here is my data structure: [paste sheet name and columns]. Write all formulas to match this layout and tell me which cell each goes in."

Prompt: "Write one Excel 365 formula that [describe the result in plain English], using columns [list them]. Give the formula, the target cell, and a one-sentence explanation. Don't give me alternatives unless I ask."

Prompt: "This formula returns [exact error] and I expected [result]. Formula: [paste]. List the three most likely causes in order of probability with a corrected formula for each."

Prompt: "Explain what this Excel formula does in plain English, step by step, as if teaching someone who knows basic Excel but not this function: [paste formula]."

Prompt: "I want a pivot table that answers [question] from columns [list]. Tell me exactly which field goes in Rows, Columns, Values, and Filters, and what aggregation to use."

Prompt: "Write an Excel 365 macro (or Office Script) that [describe the repetitive task]. Add a comment above each step, tell me how to run it, and assume I've never used VBA. I'll test it on a copy first."

Save the ones you reach for most in a notes file. A prompt you can paste and fill in is faster than rewriting the setup every time.

FAQ

Can ChatGPT read my Excel file? The free tier cannot open or read files. It only sees what you type, so you describe your columns and data in the chat. Paid tiers and some connected tools can analyze an uploaded spreadsheet, but the free version works entirely from your text description. For most formula and error questions, a good written description is all you need.

Will ChatGPT's formulas always work? No. ChatGPT writes formulas that are usually correct but sometimes reference the wrong column, assume a function your version lacks, or miss an edge case. It cannot run your sheet to confirm the result. Treat every formula as a draft, paste it in, and check it against a few rows you can verify by hand before relying on it.

Which Excel version should I tell ChatGPT I'm using? State your exact version, because functions differ. Excel 365 and Excel 2021 have XLOOKUP, FILTER, SORT, and LET. Excel 2019 and earlier do not, so they need older patterns like INDEX/MATCH or helper columns. If you skip this, you may get a formula that errors on paste with no obvious reason.

Is it safe to paste my company's data into ChatGPT? Not into the free tier with real confidential data. Customer names, financials, salaries, and NDA-covered material should stay out of public chat tools. The fix is simple: describe your structure with fake sample values. A formula written for "Region = West" and an example amount works identically on your real data.

Can ChatGPT write Excel macros if I've never coded? Yes. It writes VBA macros and Office Scripts from a plain-English description, with comments explaining each part and instructions for running them. You do not need to read code to use them, but you must test on a copy of your file first, since a macro can change data in ways undo will not fully reverse.

Can ChatGPT build a chart in Excel for me? Not directly, because it cannot click inside your workbook. What it can do is tell you which chart type fits your data and the exact steps to build it, or write a macro that creates the chart for you. Describe what you want to show, a trend over time or a comparison across categories, and it will recommend the chart and the fields to use. For a one-off, the manual steps are faster; for a report you rebuild often, ask for the macro.

How to use ChatGPT for Excel starting today

The fastest way to learn how to use ChatGPT for Excel is to take the formula or error you are stuck on right now and run it through Step 1 and Step 2 above. Describe your sheet, ask for the formula with a one-line explanation, then test it before you trust it. That single habit, describe then verify, is what turns ChatGPT from a source of almost-right answers into a genuine time-saver.

Open a spreadsheet you are working in today, copy the Step 1 prompt, and fill in your columns. You will feel the difference on the first formula.

Related: more ChatGPT guides by use case