Practical AI Guides
How to Use AI for Excel Formulas: 7 Practical Examples
AI can turn a plain-English request into an Excel formula, explain a formula you inherited, and help you work out why a result looks wrong. It does not know your data or your business rules unless you tell it, so every formula still needs testing. These seven examples show how to ask clearly, what to expect back, and how to check it.
Introduction
Most Excel problems start the same way: you know the result you want, but not the exact formula. AI assistants are useful for closing that gap. You describe the task in everyday language, and the assistant suggests a formula, explains each part and points out the assumptions it made.
- WriteTranslate a plain-English requirement into a working formula.
- UnderstandGet a step-by-step explanation of a formula you did not write.
- TroubleshootFind out why a formula shows an error or an unexpected result.
AI can still sound confident and be wrong. It may assume a different worksheet layout from yours, use a function your version of Excel does not have, or miss a rule you never mentioned. Treat every suggestion as a draft: test it against your real requirements and your real data before you rely on it. Each example below ends with a way to check the result.
Before You Start: Give AI the Right Context
The quality of a formula depends on the quality of your description. A good request covers four things:
- The worksheet. What the sheet or table is about, in a sentence.
- The columns. Each column’s letter or heading and what it contains, such as “column A: salesperson name”.
- The result you want. What the formula should return, and in which cell it will go.
- The conditions. Thresholds, exceptions, blanks, date rules and the version of Excel you use.
Protect confidential data
Do not upload or paste confidential company or customer information into an external AI service. You rarely need real data: describe the columns in words, or use a few invented rows with made-up names and numbers that have the same layout as your real sheet.
Here is a reusable prompt you can adapt for almost any formula request:
COPY-READY AI PROMPT
I use Microsoft Excel. My data has these columns: [describe column names and meanings]. I want to [describe the required result]. Please write an Excel formula, explain how it works, identify any assumptions, and provide a small example to test it. Tell me if the formula requires a newer Excel version.
Click the prompt to select it, then copy it. Replace the text in square brackets.
Asking for the assumptions and a test example is what makes the rest of this guide work: it shows you what the AI believed about your data, so you can spot a mismatch before it costs you time.
Example 1: Calculate a Total Based on a Condition
Scenario. Salesperson names are in A2:A100 and sales amounts are in B2:B100. You want the total sales for one salesperson, Priya.
=SUMIF(A2:A100,"Priya",B2:B100)A2:A100Criteria range- The cells Excel checks, here the salesperson names.
"Priya"Criterion- The value to look for in the criteria range.
B2:B100Sum range- The amounts Excel adds up, taken from the same rows where the criterion matched.
Excel reads down column A and, every time a cell equals “Priya”, adds the amount from the same row in column B. SUMIF is not case-sensitive, and the criteria range and sum range should be the same size. Typing a name inside the formula works, but it means editing the formula every time you want a different person. That is where a better prompt helps.
COPY-READY AI PROMPT
I use Microsoft Excel. Salesperson names are in A2:A100 and sales amounts are in B2:B100. Write a formula that totals the sales for the salesperson whose name is typed in cell E2, so I can change the name without editing the formula. Explain each argument and give a small test example.
Click the prompt to select it, then copy it.
A good answer will replace the typed name with a cell reference, like =SUMIF(A2:A100,E2,B2:B100). Note that the reference has no quotation marks around it.
How to check it
Type a name in E2, then compare the result with a total you work out another way, such as filtering column A to that name or building a quick PivotTable.
Example 2: Apply Multiple Conditions
Scenario. You now have three columns: salesperson in A2:A100, region in B2:B100 and sales amount in C2:C100. You want the total for Priya in the South region only.
=SUMIFS(C2:C100,A2:A100,"Priya",B2:B100,"South")C2:C100Sum range- In SUMIFS the range to add up comes first, the opposite order from SUMIF.
A2:A100,"Priya"First pair- The first range to check and the value it must match.
B2:B100,"South"Second pair- The second range to check and the value it must match.
SUMIFS adds an amount only when every condition is true for that row. A sale counts if the salesperson is Priya and the region is South. All the ranges must be the same size, otherwise Excel returns a #VALUE! error.
COPY-READY AI PROMPT
I use Microsoft Excel. Salesperson names are in A2:A100, regions are in B2:B100 and sales amounts are in C2:C100. Write a SUMIFS formula that totals sales where the salesperson in cell F2 and the region in cell G2 both match. Explain how the conditions are combined, and tell me what happens if F2 or G2 is blank.
Click the prompt to select it, then copy it.
The expected shape is =SUMIFS(C2:C100,A2:A100,F2,B2:B100,G2). The blank-cell question matters: when a criterion points to an empty cell, Excel treats it as zero, so you will usually get 0 rather than a total for everyone. If you want blanks to mean “include all”, ask the AI for a formula that handles that case.
How to check it
Pick one salesperson and region, add up their rows by hand from a few visible lines, and make sure SUMIFS agrees. Then change F2 and G2 and check again.
Example 3: Return a Value from a Lookup Table
Scenario. Product codes are in A2:A100 and their prices are in B2:B100. You type a code in E2 and want the matching price.
=XLOOKUP(E2,A2:A100,B2:B100,"Not found")E2Lookup value- The code you are searching for.
A2:A100Lookup range- Where Excel searches for that code.
B2:B100Return range- Where the answer comes from, taken from the same position as the match.
"Not found"Fallback value- What to show when no match exists, instead of an #N/A error.
By default XLOOKUP looks for an exact match, which is usually what you want for codes and IDs.
Excel version
XLOOKUP is available in Microsoft 365 and in Excel 2021 and later. It is not available in some older versions, such as Excel 2019 and earlier, where it will show a #NAME? error. If colleagues use an older version, ask the AI for an alternative.
COPY-READY AI PROMPT
I use Microsoft Excel. Product codes are in A2:A100 and prices are in B2:B100. Write a formula that returns the price for the code typed in E2 and shows "Not found" if there is no match. Then give me an alternative that works in older versions of Excel without XLOOKUP, and explain the differences.
Click the prompt to select it, then copy it.
A typical older-version answer combines INDEX and MATCH:
=IFNA(INDEX(B2:B100,MATCH(E2,A2:A100,0)),"Not found")MATCH finds the position of the code, INDEX returns the price from that position, and IFNA shows “Not found” only for the #N/A error that means no match. The 0 asks for an exact match. IFNA needs Excel 2013 or later; in an even older version, ask the AI for an IF and ISNA combination instead.
How to check it
Test a code that exists, one that does not, and one with a trailing space. If a code you can see in the list returns “Not found”, check whether one side is stored as text and the other as a number.
Example 4: Handle Errors Gracefully
Scenario. Column A holds a quantity and column B holds a divisor. Some rows are incomplete, and the division shows #DIV/0! errors that make the sheet look broken.
=IFERROR(A2/B2,"Check input")IFERROR takes two arguments: the calculation to try, and what to show if the calculation returns an error. If A2/B2 works, you see the result. If it fails, you see “Check input”.
Caution
IFERROR catches every kind of error: #DIV/0!, #N/A, #REF!, #NAME? and #VALUE!. A mistyped function name or a deleted column would also show “Check input”, hiding a genuine problem. Use it when you know which errors to expect, and handle those cases specifically where you can.
COPY-READY AI PROMPT
I use Microsoft Excel. In row 2, A2 is a quantity and B2 is a divisor. Write a formula for A2 divided by B2 that shows "Enter a divisor" if B2 is blank and "Cannot divide by zero" if B2 is 0, but still lets other errors show so I can find real problems. Explain the order of the conditions.
Click the prompt to select it, then copy it.
A suitable answer looks like this:
=IF(B2="","Enter a divisor",IF(B2=0,"Cannot divide by zero",A2/B2))The order matters. Excel treats a blank cell as equal to zero, so the blank test has to come first; otherwise a blank divisor would be reported as division by zero. Because the formula does not use IFERROR, text typed into B2 still produces a visible #VALUE! error, which is useful when you are cleaning data.
How to check it
Try four divisors in B2: a normal number, a blank cell, zero and a word. You should see a result, the blank message, the zero message and a visible error.
Example 5: Calculate Working Days
Scenario. A2 holds a start date and B2 holds an end date. You want the number of working days between them.
=NETWORKDAYS(A2,B2)NETWORKDAYS counts the days from the start date to the end date, including both, and excludes Saturdays and Sundays by default. Both cells must contain real dates, not text that only looks like dates. Public holidays and company closures are not excluded unless you list them.
COPY-READY AI PROMPT
I use Microsoft Excel. A2 is a project start date and B2 is an end date. Holiday dates are listed in H2:H20. Write a formula that counts working days between A2 and B2, excluding weekends and the holidays in H2:H20. Explain whether the start and end dates are included, and give me a small test example.
Click the prompt to select it, then copy it.
The answer should add the holiday range as a third argument:
=NETWORKDAYS(A2,B2,H2:H20)If your organisation’s weekend is not Saturday and Sunday, ask the AI for a version using NETWORKDAYS.INTL, which lets you choose the weekend days.
How to check it
Use a Monday as the start date and the Friday of the same week as the end date: the answer should be 5, because both days are included. Then add one of those days to your holiday list and confirm the result drops to 4. Also try swapping the two dates: NETWORKDAYS returns a negative number when the end date is before the start date. The holiday cells must contain real dates, not text.
Example 6: Categorize Values Using Business Rules
Scenario. Column A holds scores. Your rule is: 80 or above is High, 50 to below 80 is Medium, and anything lower is Low.
=IF(A2>=80,"High",IF(A2>=50,"Medium","Low"))Excel tests the conditions in order and stops at the first one that is true. A score of 80 or more returns High. If it is not, Excel asks whether it is at least 50 and returns Medium. Everything else returns Low. The thresholds sit exactly on the boundary values, which is where mistakes usually hide.
COPY-READY AI PROMPT
I use Microsoft Excel. Scores are in column A. Write a formula that returns "High" for 80 or more, "Medium" for 50 up to but not including 80, and "Low" for anything below 50. Then list test values at the boundaries, especially 49, 50, 79 and 80, with the expected result for each, and tell me how the formula treats blank cells and text.
Click the prompt to select it, then copy it.
Use this table to test the formula. The last three rows surprise many people.
| Value in A2 | Result | Why |
|---|---|---|
| 49 | Low | Below 50 |
| 50 | Medium | Exactly on the lower boundary |
| 79 | Medium | Below 80 |
| 79.9 | Medium | Still below 80, so decimals just under the boundary stay Medium |
| 80 | High | Exactly on the upper boundary |
| (blank) | Low | Excel treats a blank cell as zero |
| "N/A" (text) | High | Excel ranks text above any number in comparisons |
| "20" stored as text | High | A number stored as text is still text, so it ranks above every number |
If blanks or text can appear in your data, ask the AI to add a check first, for example one that returns “No score” unless the cell contains a number. If you have many categories, a small lookup table is often easier to maintain than a long chain of nested IF functions.
Example 7: Troubleshoot a Formula That Is Not Working
Scenario. A formula returns an error or a result you did not expect, and you cannot see why. The most useful thing you can give an AI assistant is the evidence: the formula, what you expected, what you got and a sample of the input.
COPY-READY AI PROMPT
I use Microsoft Excel [state your version]. This formula is not working: [paste the formula]. I expected [describe the expected result], but I get [describe the actual result or paste the exact error message]. Here is a small sample of the input, with any confidential values replaced: [paste 3 to 5 rows or describe them]. Please list the most likely causes in order, tell me how to check each one in Excel, and suggest a corrected formula. Do not change the logic unless you explain why.
Click the prompt to select it, then copy it. Replace the text in square brackets.
Causes an AI assistant often finds
- Numbers stored as text. Values look like numbers but are left-aligned or have a small green triangle, so sums and comparisons ignore them.
- Extra spaces or hidden characters. A trailing space makes “Priya” and “Priya ” different values.
- Ranges of different sizes. In SUMIFS, every range must have the same number of rows.
- References that shift when copied. Fixed ranges usually need dollar signs, such as $A$2:$A$100.
- Manual calculation mode. Excel is set not to recalculate automatically, so results look stale.
- Dates stored as text. Functions such as NETWORKDAYS cannot use them.
- A function your version does not have. This often appears as #NAME?.
Apply any fix to a copy of your workbook first. If the result is still wrong, go back to the assistant with the new result and say what changed. Each round gives it more evidence.
A Checklist for Any AI-Written Formula
AI-generated formulas are not guaranteed to be correct. Run through these six checks before you trust one:
- Syntax and references. Make sure the formula pastes without an error, and that every cell reference and range points at the columns you meant, not the ones in the AI’s example.
- Manual test cases. Try a few rows where you already know the answer and compare them with what the formula returns.
- Edge cases. Test blanks, zeros, text, duplicates, and values that sit exactly on a boundary.
- Excel-version compatibility. Confirm that everyone who will open the file has a version that supports every function used, such as XLOOKUP.
- Cross-check. Confirm the result a second way, for example with a filter, a PivotTable, or a manual total.
- Backup and sensitive data. Work on a copy of the workbook, and replace confidential values before pasting data into any AI tool.
AI is a fast first draft and a patient explainer. You remain responsible for the result, so the habit of testing is the real skill.