Expert guide

How to Use ChatGPT to Write Excel Formulas

The prompt structure that gets working formulas the first time, and how to prove they are right

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: ChatGPT writes reliable Excel formulas when you give it four things: your Excel version, the exact column layout (letters and header row), where the result goes, and one example of the correct answer. Ask for the formula plus a plain-English explanation, then test it on rows where you already know the result. For workbooks that drive billing, payroll or reporting, have the logic built and tested properly by an Excel consultant.

Most people try ChatGPT for Excel the same way: they type "formula to sum sales by region" and get something that looks right but references columns that do not exist. The problem is not the AI. It cannot see your workbook, so it invents a layout. Once you describe the layout precisely, the success rate jumps from "sometimes" to "almost always" for everyday formulas.

This guide is written for operations, finance and admin teams who live in Excel but do not want to memorise function syntax. It covers the prompt structure we use with clients, worked examples for the formulas people ask for most, and the checks that catch AI mistakes before they reach a report.

Why ChatGPT Gets Excel Formulas Wrong

When an AI-written formula fails, it is almost always one of five causes. Knowing them makes your prompts better immediately.

  • It guessed your layout

    Without column letters it assumes data starts in A1 with headers in row 1. If your headers are in row 4, every reference is off.

  • It used the wrong product's syntax

    Google Sheets functions such as QUERY, ARRAYFORMULA and IMPORTRANGE do not exist in Excel. Mention Excel explicitly.

  • Your Excel version lacks the function

    XLOOKUP, FILTER, UNIQUE, LET, LAMBDA, TEXTSPLIT, GROUPBY and the REGEX functions need Microsoft 365 or a recent perpetual version. Excel 2016 and 2019 will show #NAME?.

  • Your regional settings use semicolons

    In many European locales Excel separates arguments with ; instead of ,. Tell ChatGPT, or replace them yourself.

  • The data is messier than you described

    Numbers stored as text, trailing spaces, and dates typed as text make correct formulas return zero or #N/A.

The Prompt Structure That Works

Use this five-part structure. It takes thirty seconds longer to write and saves several rounds of back-and-forth.

Excel version: Microsoft 365 (Windows), comma separators.
Sheet: "Orders". Headers in row 1, data rows 2–4,800.
Columns: A = Order Date (real dates), B = Customer, C = Region,
         D = Product, E = Qty, F = Amount (currency).
Goal: In Summary!B2 return total Amount for the region typed in
      Summary!A2, only for orders in the month typed in Summary!A1
      (A1 holds a date like 1-Mar-2026).
Check: For Region = West and March 2026 the answer should be 18,450.
Please give the formula, explain each part in one line, and list
anything in my data that would make it return the wrong result.

The last line matters. Asking ChatGPT to list what would break the formula surfaces assumptions it would otherwise make silently, such as "this assumes Order Date contains real dates, not text."

Privacy tip: you do not need real data to get a formula. Describe the columns and invent three sample rows. Keep customer names, salaries and account numbers out of the chat entirely.

Worked Examples: Formulas People Ask For Most

1. Look up a value from another sheet (XLOOKUP)

Prompt: "Excel 365. In Orders!G2 return the account manager for the customer in Orders!B2. Lookup table is Customers!A:A (customer name) and Customers!D:D (account manager). If not found show 'Unassigned'."

=XLOOKUP(B2, Customers!A:A, Customers!D:D, "Unassigned", 0)

The fourth argument replaces the IFERROR wrapper that older VLOOKUP formulas needed, and the 0 forces an exact match. If you are on Excel 2016, ask for the INDEX/MATCH version instead.

2. Sum with several conditions (SUMIFS with a date range)

=SUMIFS(Orders!F:F,
        Orders!C:C, A2,
        Orders!A:A, ">="&EOMONTH(A1,-1)+1,
        Orders!A:A, "<="&EOMONTH(A1,0))

EOMONTH(A1,-1)+1 returns the first day of the month in A1 and EOMONTH(A1,0) the last day, so the formula works for any month typed in A1 without editing. This is the formula from the prompt above.

3. Return a filtered list that updates itself (FILTER)

=SORT(FILTER(Orders!A2:F4800,
     (Orders!C2:C4800="West") * (Orders!F2:F4800>1000),
     "No matching orders"), 1, -1)

Multiplying the two conditions means "and"; adding them with + means "or". Wrapping FILTER in SORT returns the newest orders first. Because the result spills, leave empty cells below and to the right or Excel shows #SPILL!.

4. Make long formulas readable (LET)

=LET(
  start, EOMONTH(A1,-1)+1,
  finish, EOMONTH(A1,0),
  sales, SUMIFS(Orders!F:F, Orders!C:C, A2,
                Orders!A:A, ">="&start, Orders!A:A, "<="&finish),
  target, XLOOKUP(A2, Targets!A:A, Targets!B:B, 0),
  IF(target=0, "No target", sales/target))

Ask ChatGPT to "rewrite this using LET with named steps" whenever a formula grows past one line. The next person to open the file, including you in six months, will thank you.

5. Split and clean text (TEXTSPLIT, TRIM, PROPER)

=PROPER(TRIM(TEXTSPLIT(B2, ",")))

Turns " smith, JOHN " into "Smith" and "John" across two cells. For more complex cleaning, such as standardising addresses or company names, formulas run out of road and an AI-assisted cleanup works better. See our guide to cleaning messy Excel data with AI for that workflow.

How to Check an AI-Written Formula Before You Trust It

A formula that returns a number is not the same as a formula that returns the right number. Run these five checks before the result goes into a report.

  • Test against a known answer

    Filter the source data by hand for one case and compare the total to the formula. If they differ, stop.

  • Test the edge cases

    Blank cells, a region with no sales, the first and last day of the month, and a customer missing from the lookup table.

  • Use Evaluate Formula

    Formulas → Evaluate Formula steps through the calculation so you can see which part produces an unexpected value.

  • Check the ranges stop where your data stops

    Hard-coded ranges like A2:A4800 silently miss row 4,801 next month. Convert the data to an Excel Table (Ctrl+T) and ask for structured references instead.

  • Ask ChatGPT to break its own formula

    Prompt: 'Give me five rows of data where this formula would return the wrong answer.' It is surprisingly good at this.

ChatGPT vs Copilot vs Writing It Yourself

FeatureChatGPTCopilot in ExcelManual / consultant
Sees your workbookNo, you describe itYes (tables work best)Yes
Excel versionsAnyMicrosoft 365 with Copilot licenceAny
Best forDesigning and explaining formulasQuick in-sheet answers and columnsBusiness-critical models
Main riskLayout guessingOver-trusting results on messy dataCost and time
Testing and documentationYou do itYou do itIncluded

For a deeper comparison see Copilot vs ChatGPT for Excel work.

Prompts Worth Saving

  • Explain this formula line by line as if I am new to Excel: [paste formula]

  • This formula returns #N/A for some rows. List the likely causes in order of probability and how to test each: [paste formula and sample rows]

  • Rewrite this nested IF formula using IFS or SWITCH so it is easier to maintain.

  • Convert this VLOOKUP to XLOOKUP and explain what changes.

  • Give me a version of this formula that works in Excel 2016 without dynamic arrays.

  • My data is an Excel Table named tblOrders. Rewrite the formula with structured references.

When Prompting Stops Being Enough

ChatGPT is excellent at single formulas. It is not a substitute for designing a workbook. Signs you have outgrown the prompt-and-paste approach:

  • The same copy, paste and refresh steps happen every week and take more than an hour.

  • Formulas reference several workbooks, and links break when files move.

  • Several people edit the file and nobody is sure which version is correct.

  • The workbook takes minutes to calculate or crashes when it opens.

  • A wrong number would affect invoices, payroll, pricing, or a lender's report.

At that point the job is automation and structure, not another formula. We build those systems as fixed-scope projects, from Excel automation that removes the weekly copy-paste to AI workflow automation that reads, classifies and summarises data for you.

FAQ

ChatGPT and Excel formulas FAQ

Short answers for teams using AI to build spreadsheets

Usually, for common functions like XLOOKUP, SUMIFS, FILTER and TEXTJOIN, provided you describe the exact column letters, the header row, and your Excel version. It gets formulas wrong most often when it guesses the layout, mixes Google Sheets syntax with Excel syntax, or uses functions your version does not have. Always test the formula on rows where you already know the answer.

State the Excel version, the sheet name, which columns hold which data, which row the headers are on, where the formula goes, and one worked example of the expected result. For example: 'Excel 365. Sheet Orders, headers in row 1. A = Order Date, C = Region, F = Amount. In I2 return total Amount for the region in H2 for March 2026. For H2 = West the answer should be 18,450.'

You rarely need to. To get a formula you only need the column layout and a few made-up sample rows, not customer names or financial data. If your company uses ChatGPT Enterprise, Team, or Microsoft 365 Copilot under a business agreement, check your admin's data policy before pasting real records.

Copilot in Excel can see the actual workbook, so it avoids layout guessing, but it needs a Microsoft 365 Copilot licence and works best on data formatted as an Excel table. ChatGPT works with any Excel version and is often better at explaining why a formula works. Many teams use both: Copilot for quick in-sheet answers, ChatGPT for designing and debugging complex formulas.

When the workbook drives payroll, billing, pricing or reporting that other people rely on, when formulas reference several sheets or external files, or when the same manual steps repeat every week. At that point the risk is not one wrong formula but an untested model. A consultant builds, tests and documents the logic once.