Details checked against Google and Microsoft help pages on October 13, 2026. Always test formulas on a copy of your data first.
Spreadsheet formulas can be confusing. Is it VLOOKUP or XLOOKUP? Where do the brackets go? AI can write and explain formulas for you in seconds, and you don't need a paid plan to get that help. This guide shows the free options for Google Sheets and Excel, with prompts you can copy.
What's free and what's paid?
- Built-in AI assistants, such as Gemini inside Google Sheets or Copilot inside the Excel desktop app, generally need a paid plan. Microsoft says Copilot in apps like Word, Excel and Outlook needs a Microsoft 365 Personal, Family or Premium subscription.
- Free alternatives: use a free AI chatbot to write and explain formulas, and Google Sheets' free built-in features such as Smart Fill.
1. Use a free AI chatbot to write formulas
ChatGPT, Gemini, Claude and Copilot can all write formulas on their free plans. The key is to describe your sheet clearly: which columns hold what, and which program you use.
Prompt template:
I'm using [Google Sheets / Excel]. Column A has [product names], column B has [prices], column C has [quantities sold]. Write a formula for D2 that [calculates total sales], and explain how it works.
More useful prompts:
- "Write a formula that finds the price for the product name in F2 by looking it up in columns A and B."
- "Count how many rows in column C say 'Paid'."
- "This formula returns #N/A: [paste formula]. What's wrong and how do I fix it?"
- "Explain this formula step by step like I'm a beginner: [paste formula]."
Compare chatbots in Best Free AI Chatbots in 2026.
2. Google Sheets Smart Fill (free)
Google's help describes Smart Fill as a feature that "will detect patterns and make suggestions to help automate data entry," for tasks like pulling the first name out of a list of full names. Start typing the pattern in a new column, and Sheets suggests filling in the rest, often by writing the formula for you.
Official page: Use Smart Fill in Sheets
Common formulas to ask AI about
| Task | Formula to ask about |
|---|---|
| Add up a column | SUM |
| Add up only rows that match a condition | SUMIF / SUMIFS |
| Count rows that match a condition | COUNTIF / COUNTIFS |
| Find a value in another table | XLOOKUP (or VLOOKUP in older Excel) |
| Show a result based on a condition | IF / IFS |
| Hide errors neatly | IFERROR |
| Join text from two cells | TEXTJOIN or the & operator |
Tips for getting correct formulas
- Say which program you use. Some functions work differently, or only exist, in Excel versus Google Sheets.
- Give example data. Paste a few rows (with private details removed) so the AI understands your layout.
- Test before trusting. Check the result on a few rows you can calculate by hand.
- Ask for an explanation. Understanding the formula means you can fix it later.
- Protect sensitive data. Don't paste salaries, customer lists or personal information into a chatbot. Use made-up sample data instead.
Want to learn spreadsheets properly? Free courses are listed in Free Online Courses with Free Certificates.
Have a formula question? Ask through our Contact page.
Blogger Comment
Facebook Comment