How to Use AI to Write Excel Formulas (and Check Them Before You Trust Them)

Short answer: describe your sheet to the AI precisely (which program and version, the column letters, the row range and the result you expect), ask for the formula plus a plain-English explanation, then test it on a few rows where you already know the right answer. AI chatbots are good at formulas like SUMIFS, XLOOKUP and IF, but they regularly get ranges, $ signs and version support wrong, so a formula is not finished until you have checked it. This guide shows how, with a prompt you can copy.

Privacy first: you rarely need to paste real data to get a formula. Describe the columns, or use a few made-up rows. Keep customer names, payroll, health or financial records out of general chatbots unless your employer has approved the tool. See is it safe to share personal info with AI chatbots.

Which AI tool to use

• Any general chatbot (ChatGPT, Claude, Gemini, Microsoft Copilot chat) can write and explain formulas from a description. You copy the formula into your sheet yourself. Our guide to choosing between them applies here too.
• Copilot inside Excel works on the workbook itself. Microsoft's help pages say it can add formula columns and rows, suggest single-cell formulas, build lookups and explain an existing formula, in Excel for Microsoft 365, Excel 2024, Excel for iPad and Excel for the web, on plans that include it. Microsoft's own advice: review, edit and verify anything Copilot creates (Microsoft Support: Copilot in Excel). For what the different Copilot versions include, see Microsoft Copilot explained.
• Google Sheets uses mostly the same function names as Excel, but not all. Always tell the AI which program you are using.

Step 1: Describe your sheet precisely

Most bad formulas come from vague requests. "Write a formula to total sales by region" leaves the AI guessing your layout. Give it:

• The program and version: "Excel for Microsoft 365", "Excel 2019" or "Google Sheets". Some functions only exist in newer versions.
• The exact columns and rows: "Column A is Region, column B is Date, column C is Sale Amount. Data is in rows 2 to 500 with headers in row 1."
• Where the formula goes and what it should return: "In cell G2, total the Sale Amount for the region typed in F2."
• Edge cases: what should happen with blanks, text in number columns, or a value that is not found.
• Whether you will copy it down or across. This decides which references need $ signs.

This is the same skill as any good prompt: context, task and format. Our guide to writing better AI prompts covers it in more depth.

Step 2: Ask for the formula and an explanation

Ask the AI to explain each part in plain words. The explanation is how you catch mistakes: if it says "this adds up column D" and your amounts are in column C, you know before you paste anything. For the request above, a correct answer would be something like:

=SUMIFS($C$2:$C$500, $A$2:$A$500, F2)

SUMIFS adds the values in the first range (C2:C500) on rows where the second range (A2:A500) matches the condition (the region in F2). The $ signs keep the data ranges fixed when you copy the formula down to G3, G4 and so on, while F2 changes to F3, F4.

A copy-paste prompt

I use [EXCEL FOR MICROSOFT 365 / EXCEL 2019 / GOOGLE SHEETS]. My sheet has headers in row 1 and data in rows 2 to [LAST ROW]. Columns: A = [NAME], B = [NAME], C = [NAME]. In cell [CELL], I want a formula that [WHAT IT SHOULD CALCULATE]. If [EDGE CASE, e.g. the value is not found / a cell is blank], it should [WHAT TO SHOW]. I will copy the formula [DOWN / ACROSS / NOT AT ALL].
Please give me: 1) the formula, 2) a plain-English explanation of each part, 3) any function that might not work in my version and an alternative, and 4) three test rows with made-up numbers and the result I should expect, so I can check it.

Step 3: Test it on numbers you already know

Never judge a formula by whether it returns a number. Check that it returns the right number:

1. Make a few test rows where you can work out the answer in your head (for example, three West sales of 10, 20 and 30 should total 60).
2. Include an awkward case: a blank cell, a region that is not in the list, a zero.
3. Copy the formula down a few rows and click into a lower cell to confirm the references moved (or stayed fixed) the way you intended.
4. For long formulas in Excel, select the cell and use Formulas > Formula Auditing > Evaluate Formula to watch Excel calculate it one step at a time (Microsoft Support: evaluate a nested formula).
5. Turn on Excel's background error checking, which marks suspicious cells such as formulas that skip adjacent cells or numbers stored as text (Microsoft Support: detect formula errors).

Mistakes AI often makes with formulas

• Wrong or shifted ranges: C1:C500 instead of C2:C500 (including the header), or ranges of different sizes in the same formula.
• Missing or extra $ signs: the formula works in the first cell and breaks when copied.
• Functions your version does not have: for example, Microsoft says XLOOKUP is not available in Excel 2016 or Excel 2019 (Microsoft Support: XLOOKUP). In those versions you will see a #NAME? error. Ask for an INDEX and MATCH version instead.
• Arguments in the wrong order: SUMIF and SUMIFS put the sum range in different positions, which is an easy mix-up.
• Comma or semicolon separators: depending on your location settings, Excel may expect ; instead of , between arguments.
• Numbers or dates stored as text: the formula is right, but the data is not. Imported data often causes this.
• Made-up functions or features: occasionally a chatbot invents a function name that does not exist. If Excel does not recognize it, ask the AI to use only built-in functions available in your version.
• Hard-coded numbers: a tax rate or threshold typed into the formula instead of a cell, so it silently goes out of date. Ask for it to reference a cell instead.

Three formulas worth asking for

These cover many everyday jobs. Adjust the ranges to your sheet:

• Look up a value with a friendly "not found" message (Excel 2021 or later, Microsoft 365, Google Sheets): =XLOOKUP(F2, A2:A100, C2:C100, "Not found")
• The same lookup for older Excel versions: =IFERROR(INDEX(C2:C100, MATCH(F2, A2:A100, 0)), "Not found")
• A difference that never goes below zero (useful for reorder amounts): =MAX(0, F2-E2)

For a full worked sheet, see inventory and par-level spreadsheets with AI. Trades and construction owners building quote sheets can use the same checks in quote and estimate templates and construction estimate drafts.

Use AI to understand formulas you inherited

Paste a long formula (not the data) into a chatbot and ask: "Explain what this Excel formula does, step by step, in plain English, and tell me what would break it." In Excel with Copilot, you can select the cell and ask it to explain the formula. Then confirm the explanation with Evaluate Formula. If you do this kind of job every week, our guide to building a simple AI workflow for weekly tasks shows how to turn it into a routine.

Key takeaways

• Tell the AI your program and version, exact columns and rows, the target cell and the edge cases.
• Always ask for a plain-English explanation and test rows with known answers.
• Check ranges, $ signs, function availability and number formats before you rely on a formula.
• Describe your data rather than pasting sensitive records into a chatbot.

Frequently asked questions

Can ChatGPT write Excel formulas?

Yes. ChatGPT and other general chatbots can write and explain Excel and Google Sheets formulas from a clear description of your sheet. They make mistakes, especially with ranges and version-specific functions, so test every formula on rows where you know the answer.

Do I need Copilot in Excel to get AI help with formulas?

No. You can describe your sheet to any chatbot and paste the formula in yourself. Copilot in Excel adds the convenience of working directly on your workbook, where your plan includes it.

Why does the formula from AI show #NAME? in my spreadsheet?

Usually because the function does not exist in your version (for example, XLOOKUP in Excel 2019), the function name is misspelled or invented, or text is missing quotation marks. Tell the AI your exact version and ask for an alternative using older functions.

Is it safe to paste my spreadsheet into an AI chatbot?

Often you do not need to. Describe the columns or use made-up sample rows. Do not paste personal, customer, payroll, health or confidential business data into a tool your employer has not approved.

Will the same formula work in Google Sheets?

Many common formulas, such as SUMIFS, IF and XLOOKUP, work in both, but some functions and features differ. Tell the AI which program you use and test the result there.