Spreadsheets power budgets, sales tracking, inventory and reporting for millions of people. Writing complex formulas can be confusing, but AI assistants can explain functions, write formulas from plain-language descriptions and suggest ways to organise data. Always test formulas on sample data before using them in important files.
Writing Formulas
- In Google Sheets, column A has dates and column B has sales. Write a formula to total sales for [month].
- Write an Excel formula to calculate percentage growth between B2 and C2.
- Write a formula to look up the price of a product in another sheet using its product ID.
- Write a formula that returns “Pass” if a score is 40 or above, otherwise “Fail”.
- Count how many rows in column C contain the word “Completed”.
Explaining Functions
- Explain how XLOOKUP works with an example.
- What is the difference between VLOOKUP and INDEX-MATCH?
- Explain SUMIFS with three conditions using a simple example.
- Explain how ARRAYFORMULA works in Google Sheets.
Data Cleaning
- Write a formula to remove extra spaces from names in column A.
- Split full names in column A into first and last names.
- Convert text dates like “02-10-2026” into date format.
- Highlight duplicate values in column B using conditional formatting rules.
- Extract email domains from email addresses in column C.
Analysis and Reporting
- Explain how to create a pivot table summarising sales by region and month.
- Suggest charts to visualise monthly expenses by category.
- Write a formula to calculate a running total in column D.
- Calculate the average of the last 7 days of values in column B.
Troubleshooting
- Why does this formula return #N/A? [formula and description].
- Fix this formula that gives a #VALUE! error: [formula].
- Why is my SUM formula not adding numbers stored as text?
Templates and Automation
- Suggest a monthly budget spreadsheet structure with categories and formulas.
- Create an inventory tracker layout with reorder alerts.
- Suggest a content calendar spreadsheet template for a blog.
Example Prompt Structure
| — | — |
|---|---|
| Data layout | “Column A: Date, Column B: Amount” |
| Desired result | “Total amount for each month” |
| Constraints | “Ignore blank cells” |
How to Describe Your Spreadsheet So AI Gets the Formula Right
Most formula mistakes from AI tools happen because the assistant cannot see your sheet. The fix is to describe the layout clearly. A good formula request includes:
- Which tool you use: Excel, Google Sheets or both, since a few functions differ.
- Where your data is: “Names are in column A, sales amounts in column B, dates in column C, from row 2 to row 200.”
- What result you want: “I want the total sales for March only.”
- Where the result goes: “The formula will be in cell F2.”
- Any special cases: Blank cells, text instead of numbers, or duplicate entries.
Example prompt: “In Google Sheets, column A has product names and column B has quantities sold, rows 2 to 100. Write a formula for cell E2 that adds up quantities only for the product typed in cell D2, and explain each part.”
Worked Example: A Small Sales Sheet
Imagine a simple sheet that tracks orders for a home business. The data below is illustrative.
| Column | Contents | Example value |
|---|---|---|
| A | Order date | 05/03/2026 |
| B | Customer region | North |
| C | Product | Candle |
| D | Quantity | 3 |
| E | Price per unit | 12 |
Here are common questions and the type of formula the AI might suggest:
- Total value per row: Multiply quantity by price, for example =D2*E2, then copy it down.
- Total sales for one region: Use SUMIF, for example =SUMIF(B2:B200,”North”,F2:F200), where column F holds the row totals.
- Count orders for one product: Use COUNTIF, for example =COUNTIF(C2:C200,”Candle”).
- Sales for one region and one product: Use SUMIFS with two conditions.
- Look up a price from another table: Use XLOOKUP in newer versions of Excel and Google Sheets, or VLOOKUP if you need wider compatibility.
After receiving any formula, ask: “Explain this formula piece by piece as if I am new to spreadsheets.” Understanding it means you can adjust it later without help.
Testing a Formula Before You Trust It
Even correct-looking formulas can give wrong results if ranges or conditions are slightly off. Use these quick checks:
- Test on a small sample: Pick five rows and calculate the answer manually, then compare.
- Check the ranges: Make sure the formula covers all your data rows, including new ones you will add later.
- Watch for text numbers: Numbers stored as text are often ignored by SUM functions.
- Try an edge case: What happens if a cell is blank or a lookup value does not exist? Ask the AI how to handle errors neatly with IFERROR.
- Lock references when needed: Ask the AI when to use dollar signs, such as $E$1, so references do not shift when copied.
Common Spreadsheet Mistakes When Using AI
- Not mentioning the tool. Some functions exist only in newer versions or in Google Sheets.
- Mixing up separators. Some regional settings use semicolons instead of commas between formula arguments.
- Messy data. Extra spaces, merged cells and inconsistent date formats cause many formula errors. Ask the AI how to clean data first.
- Hard-coding values. Putting numbers directly into formulas makes updates harder. Reference cells instead.
- Sharing sensitive data. Use made-up sample rows when asking for help, rather than real customer or financial records.
Tip: Keep a “formula notes” tab in your workbook explaining what each important formula does. Ask the AI to write these explanations in plain language for anyone who uses the sheet after you.
Conclusion
AI prompts make spreadsheet work faster and less intimidating. Describe your data clearly, ask for formulas with explanations, test on sample data and protect sensitive information. Over time, you will learn the functions yourself.
