Writing Excel formulas with AI is the fastest way to build complex calculations without memorising function names. For questions such as “how do I write a VLOOKUP?” or “how do I sum by two conditions?”, describing what you want in plain sentences is enough: the AI builds the formula and you check it.
How should you ask AI for an Excel formula?
For a good result, the AI needs to “see” your table. Give these four pieces of information:
- Columns: What is in each column? (For example “A: date, B: product, C: quantity, D: amount”)
- Row range: Is the header in row 1 and the data in rows 2–500?
- The result you want: What should be calculated, and in which cell.
- Excel language: If you use Excel in another language, say so. In Turkish Excel, for example, VLOOKUP becomes DÜŞEYARA and IF becomes EĞER, and arguments are separated by semicolons (;) instead of commas.
Ready-made prompt examples
- Conditional sum: “Sum the amounts in column D for rows where column B says ‘Pen’.” →
=SUMIF(B2:B500,"Pen",D2:D500) - Sum with two conditions: “Total for product ‘Pen’ in November.” → the AI will usually suggest
SUMIFS. - Look up a value from another table: “Bring the price from the product list on Sheet2.” →
VLOOKUP, orXLOOKUPin newer versions. - Hide errors: “Leave it blank if not found.” → the formula is wrapped in
IFERROR(...,""). - Join text: “Combine first and last name with a space.” →
=A2&" "&B2
What if the formula does not work?
Paste the error message exactly and ask: “This formula returns #VALUE!, why?” The AI often spots causes such as numbers stored as text, a wrong range or the wrong separator. Before applying a formula to the whole table, check it on a few rows where you know the answer; AI can occasionally invent a function that does not exist or pick the wrong range.
Privacy and security
A formula needs the structure of your data, not the data itself. Share made-up sample rows such as “Name1, Name2, 1000, 2000” instead of customer names, salaries or ID numbers. On a work computer, also check your organisation’s rules on using AI tools.
The most-used Excel formulas and how to ask AI for them
- VLOOKUP and XLOOKUP: For finding a value in a table. Example prompt: “Write an XLOOKUP formula that brings the price from the Prices sheet based on the product code in column B.”
- SUMIFS: Adding up by more than one condition. Example: “Sum the sales where the region is Aegean and the date is November 2026.”
- IF and nested conditions: Returning different results depending on the case. Example: “If the score is 85 or above show Excellent, 70 to 84 Good, below that Needs work.”
- TEXTJOIN: Combining text from several cells into one cell.
- UNIQUE and FILTER: Removing duplicate records and listing rows that meet a condition; available in current versions such as Microsoft 365.
When asking AI, it is important to state the language and version of your Excel. In Turkish Excel, function names are in Turkish and arguments are usually separated by semicolons; in English versions function names are in English and the separator is usually a comma. Adding “for Turkish Excel, with semicolons” (or the equivalent for your language) to your prompt makes the formula work straight away.
Common mistakes when writing formulas with AI
- Not giving the cell range: Instead of “find the total”, give a clear range such as “sum the sales in B2:B200”.
- Mixing up absolute and relative references: When copying a formula down, cells that must stay fixed need a $ sign (for example $F$1).
- Skipping date and number formats: In Türkiye the decimal separator is a comma and dates are written day.month.year, so stating this in the prompt prevents errors.
- Not giving sample data: Writing a few rows from your table as an example, without personal information, makes it easier for the AI to find the right formula.
Does it work in Google Sheets and other programs?
The same method works for Google Sheets and LibreOffice Calc, although some functions may have different names or usage. In Google Sheets, function names are usually used in English and the separator depends on your account’s locale setting. So tell the AI which program you use and try the formula on a small sample table first. For important reports, checking the AI-generated formula by hand on a few rows is the easiest way to catch mistakes. Asking the AI to explain the formula’s logic step by step also makes it easier to write similar formulas yourself later.
Frequently asked questions
Can AI write Excel macros too?
Yes, it can write VBA macros; run them only when you understand what they do, and test them on a copy of the file.
Does Excel have its own AI?
Some Microsoft 365 plans include Copilot, which offers formula suggestions and table summaries; availability depends on your subscription.
Why does the formula come in English?
AI tools assume English Excel by default. Adding your Excel language and separator to the question fixes this.
Can AI build a pivot table?
AI can explain step by step how to create a pivot table; in Excel versions with built-in AI features, summary table suggestions may be offered directly in the program.
Do I need a paid AI tool for this?
No. Free chat assistants are enough for most formulas, although some features for analysing a whole file may only be available in paid versions.
Sources and further reading
- Microsoft Support, “Excel functions (by category)” and “XLOOKUP function” (support.microsoft.com)
- Google Docs Editors Help, “Google Sheets function list” (support.google.com)
Leave a comment
Your email address will not be published. Required fields are marked *