You don’t have to memorize functions like XLOOKUP, SUMIFS or TEXTJOIN anymore. Describe what you want in plain English, let AI draft the formula, then test it. Here’s how to do it step by step, and how to avoid common mistakes.
Excel is powerful, but writing formulas can be intimidating, especially when you need nested functions, lookups or text manipulation. AI tools can now turn a plain-language request into a working formula, explain what an unfamiliar formula does and help fix errors. This guide shows you how to create Excel formulas using AI, which tools to consider and how to double-check the results.
AI features, licensing and availability change often, particularly for Microsoft’s built-in Copilot features. Check Microsoft’s current documentation for what your account includes.
Ways to Use AI to Create Excel Formulas
There are three main approaches, and you can mix them.
1. Use a general AI chatbot
You can describe your spreadsheet to an AI chatbot such as ChatGPT, Claude or Gemini and ask it to write a formula, then paste the formula into Excel. This works with any version of Excel and doesn’t require access to your file.
2. Use Copilot inside Excel
Microsoft Copilot is built into Excel for eligible Microsoft 365 users and can generate formulas, explain them, clean data and build summaries from within the workbook. According to current Microsoft-related guidance, Copilot in Excel generally requires a paid Microsoft 365 Copilot license (or an eligible individual plan), and it works best when your data is formatted as an Excel table and your file is saved to OneDrive or SharePoint with AutoSave on. Free Copilot Chat access to desktop Excel has reportedly been limited, so verify what your plan includes.
3. Use the new COPILOT function
Microsoft has also introduced a =COPILOT() worksheet function that lets you type a plain-language prompt directly in a cell, for example to summarize, classify or tag text. It’s a different tool from a standard formula, with important caveats:
- Microsoft has said it is not meant for tasks requiring accuracy or reproducibility, such as precise numerical calculations, financial reporting or other high-stakes work.
- Availability has been limited to certain programs, such as Frontier or Microsoft 365 Insider, and requires a qualifying Copilot subscription.
- It has usage limits.
Use it for language-style tasks like categorizing feedback, and use standard Excel formulas for math and reporting.
Other options
There are also AI add-ins and assistants for spreadsheets from various providers, including Anthropic’s Claude in Excel. Features and availability vary, so check each provider’s current documentation.
Step 1: Get Clear on What You Want
Before opening any AI tool, answer these questions:
- What result do you want? A total, a lookup, a label, a cleaned-up text string?
- Where is your data? Which columns and rows hold the information?
- What are the conditions? For example, only sales over $500, only the month of March, or only unpaid invoices.
- Where should the answer go? One cell, a column, a table?
Writing this down in one or two sentences makes the AI’s job much easier.
Step 2: Describe Your Spreadsheet Clearly
AI can’t see your file unless you share it, so describe the structure. Include:
- Column letters and headers, such as “Column A is Date, B is Region, C is Sales”
- The row where data starts and ends, or say you want the formula to handle added rows
- Your Excel version, such as Microsoft 365, Excel 2021 or Excel 2016, since newer functions like XLOOKUP, FILTER and TEXTAFTER aren’t available in older versions
- A few sample rows, using fake or anonymized values
Step 3: Write a Strong Prompt
A good formula prompt follows this pattern:
Context + Goal + Conditions + Output location + Excel version
Weak prompt:
Sum the sales.
Strong prompt:
I’m using Microsoft 365 Excel. Column B has the region (West, East, North, South), column C has the order date, and column D has the sales amount. Data runs from row 2 to row 500. Write a formula that totals sales for the West region in January 2026 and put the result in cell G2.
Step 4: Paste the Formula and Test It
- Copy the formula the AI gives you.
- Click the target cell in Excel.
- Paste it, ideally into the formula bar, and press Enter.
- Check whether the result looks right.
- Test with a few examples you can calculate by hand.
- Try edge cases, such as blank cells, zeros, text in number columns or duplicate values.
Never trust a formula just because it returns a number. Verify it against something you know to be true.
Step 5: Ask the AI to Explain or Fix the Formula
If the formula doesn’t work or you don’t understand it, ask:
- “Explain this formula step by step in plain English.”
- “I’m getting a #VALUE! error. Here is the formula and sample data. What’s wrong?”
- “Rewrite this formula so it works in Excel 2016.”
- “Make this formula shorter and easier to read.”
Learning why a formula works makes you better at spotting mistakes later.
Example Prompts and Formulas
These examples show the kinds of formulas AI can help you write. Replace the cell references with your own.
1. Add up sales for one region and date range
Prompt: “Column B is Region, column C is Date, column D is Sales. Sum Sales for ‘West’ in January 2026.”
=SUMIFS(D2:D500, B2:B500, "West", C2:C500, ">="&DATE(2026,1,1), C2:C500, "<="&DATE(2026,1,31))2. Look up a value from another table
Prompt: “Look up the product ID in F2 in column A and return the price from column C. If not found, show ‘Not found’. I use Microsoft 365.”
=XLOOKUP(F2, A:A, C:C, "Not found")3. Combine first and last names
Prompt: “Combine first name in B2 and last name in C2 into one cell, ignoring extra spaces.”
=TRIM(B2&" "&C2)4. Calculate percentage change safely
Prompt: “Calculate the percent change from column B to column C, and show blank if column B is zero or empty.”
=IFERROR((C2-B2)/B2, "")5. Flag overdue invoices
Prompt: “If the due date in column C is before today and the status in column D isn’t ‘Paid’, show ‘Overdue’, otherwise ‘OK’.”
=IF(AND(C2<TODAY(), D2<>"Paid"), "Overdue", "OK")6. Count rows that meet two conditions
Prompt: “Count rows where column B is ‘West’ and column D is greater than 500.”
=COUNTIFS(B2:B500, "West", D2:D500, ">500")7. Get a unique list
Prompt: “Give me a list of unique values from column A. I use Microsoft 365.”
=UNIQUE(A2:A500)8. Calculate age from a birth date
Prompt: “Calculate age in years from the birth date in A2.”
=DATEDIF(A2, TODAY(), "Y")9. Extract the domain from an email address
Prompt: “Return everything after the @ in the email in A2. I use Microsoft 365.”
=TEXTAFTER(A2, "@")Always test these on your own data, since regional settings, data types and Excel versions can change how formulas behave. In some regions, Excel uses semicolons instead of commas between formula arguments.
Step 6: Use AI for Common Excel Tasks Beyond Formulas
AI can also help with:
- Cleaning data: removing extra spaces, splitting columns, standardizing dates
- Building pivot tables and charts: ask for step-by-step instructions or use Copilot features if you have them
- Conditional formatting rules: describe the rule in words and ask how to set it up
- Data validation: drop-down lists and input restrictions
- Macros and scripts: AI can draft VBA or Office Scripts, but review them carefully and understand what they do before running them
Tips for Better Results
- Share your Excel version. It determines which functions you can use.
- Provide sample data. Even three or four fake rows help a lot.
- Ask for the simplest solution. Some AI answers are more complicated than needed.
- Request alternatives. Ask for a version that works in older Excel if you share files with others.
- Use tables. Structured table references make formulas easier to read and update.
- Name your headers clearly. Descriptive column names help both you and the AI.
- Save working prompts and formulas in a reference sheet.
Common Mistakes to Avoid
- Trusting the output without testing
- Skipping the Excel version, which can lead to formulas that return errors in older versions
- Using vague prompts that don’t name columns or conditions
- Using AI for exact financial numbers without verification
- Pasting sensitive data into an AI tool without checking its privacy policy
- Copying formulas that use the wrong range, such as missing the last rows
- Ignoring hidden data issues, such as numbers stored as text or extra spaces
Privacy and Safety Tips
- Replace real data with fake or anonymized samples when asking a chatbot for help.
- Don’t share confidential, financial, customer or personal information with AI tools unless your organization approves it and you understand how the tool handles data.
- Check your workplace policies on AI use.
- Review macros and scripts before running them, especially if you don’t fully understand them.
Frequently Asked Questions
Can AI write Excel formulas for me? Yes. You can describe what you want in plain language, and AI can generate a formula, explain it and help debug errors. Always test the result.
Is there an AI built into Excel? Microsoft Copilot is built into Excel for eligible users and plans. Features and licensing change, so check Microsoft’s current requirements.
What’s the difference between Copilot formulas and the COPILOT function? Copilot in the chat pane can help you create standard Excel formulas and analyses. The COPILOT function lets you use an AI prompt directly in a cell for text-focused tasks like summarizing or classifying, and Microsoft warns against using it where accuracy and reproducibility are essential.
Are AI-generated formulas always correct? No. They can contain errors, use the wrong range or assume an Excel version you don’t have. Test them with examples you can verify by hand.
Can I use a free AI chatbot to write Excel formulas? Yes. Many chatbots can write formulas without needing access to your file. Just describe your layout and paste in fake sample data.
What should I do if the formula returns an error? Copy the formula and error message into the AI tool, describe your data and ask it to troubleshoot. Common causes include wrong ranges, text-formatted numbers and functions not supported by your Excel version.
Final Thoughts
Learning how to create Excel formulas using AI is mostly about communicating clearly: describe your columns, your goal and your Excel version, then test everything. Use AI to draft, explain and debug, but keep your own judgment in the loop. With a little practice, you’ll solve spreadsheet problems in minutes that once took hours.
Disclaimer: This article is for general informational purposes only. Software features, licensing and availability change frequently, so verify current details with Microsoft and other providers. Always verify spreadsheet results, especially for financial, legal or other high-stakes use.
