How to Use Free AI to Write Excel and Google Sheets Formulas

By Austin Little

Free AI chatbots can write spreadsheet formulas for you, if you describe your data clearly, test the answer on a copy, and never paste in anything private. Here's the plain-English method that works.

AI disclosure. This page was drafted with AI assistance and edited for Apiary. We don't invent quotes, stats, people, or events. If something looks off, tell Austin — that's the point of a living hive.

Here's the short answer:

Yes, free AI chatbots can write Excel and Google Sheets formulas, and they're often good at it. You describe what you want in normal words, and they hand you a formula to paste in.
The trick is how you describe your sheet. Tell the AI which columns hold what, which row your data starts on, what the answer should look like, and whether you're in Excel or Google Sheets.
Always test on a copy. AI formulas can look right and be quietly wrong. Check the answer against a few rows you can work out by hand.
Don't paste private data. Describe the shape of your data with fake example rows instead of dumping real names, account numbers, or medical info into a chatbot.

The rest of this guide walks you through it step by step, from your first "please just make this sum work" to cleaning up messy lists, with copy-and-paste prompt templates and a checklist for catching mistakes.

Why spreadsheets feel hard (and why AI helps)

Spreadsheets aren't hard because you're bad at math. They're hard because formulas are a tiny programming language with fussy punctuation. One missing comma, one wrong dollar sign, one quote mark in the wrong place, and the whole thing throws an error that reads like a riddle.

Most people know exactly what they want. "Add up everything in column C where column B says 'Groceries.'" "Tell me which invoices are more than 30 days old." "Pull the phone number for this customer from the other tab." The gap isn't the idea. It's translating the idea into syntax.

That translation is something AI chatbots are pretty good at. They've seen a huge amount of spreadsheet help text, forum answers, and documentation during training, so they can usually turn a clear sentence into a working formula. They can also explain a formula someone else wrote, which is gold when you inherit a monster spreadsheet from a coworker who left.

What AI is not good at is knowing your spreadsheet. It can't see your file unless you give it one, and even then it guesses about things you didn't spell out. Most bad AI formulas come from fuzzy descriptions, not from the AI being dumb. So the skill you're building here is mostly describing, not coding.

What you need before you start

You don't need to pay for anything to do this. You need:

A spreadsheet program. Microsoft Excel (desktop or the free web version), or Google Sheets (free with a Google account). Apple Numbers and LibreOffice Calc work too, but their formulas differ in small ways, so say which one you're using.
A free AI chatbot. Several big ones offer free tiers that handle formulas well.
A copy of your spreadsheet (or a test tab) where you can try formulas without wrecking the real thing.
About ten minutes of patience the first time. It gets fast once you know the routine.

Built-in AI vs. a separate chatbot

Both Microsoft and Google have been adding AI helpers directly inside Excel and Sheets. These can be handy because they can "see" your sheet.

The method in this guide works with any free chatbot in a separate browser tab. You describe the sheet, it writes the formula, you paste it in. That's the most universal, no-cost approach, and it keeps you in control of what data leaves your computer.

Step 1: Figure out exactly what you want

Before you type anything into a chatbot, answer these on a sticky note:

What's the question? "Total spent on groceries in March," not "help with my budget."
Where does the answer go? One cell? A whole new column, one result per row?
What should the answer look like? A number, a dollar amount, a date, a yes/no, a word like "Late"?
What should happen with blanks or weird rows? Show nothing? Show zero? Skip them?

That last one matters more than people expect. Real spreadsheets have empty cells, typos, and the occasional "N/A" typed into a number column. If you don't tell the AI what to do with those, it'll make a choice for you, and you may not like it.

Step 2: Describe your sheet like you're explaining it over the phone

Picture calling a friend who's good at Excel. They can't see your screen. What would you tell them? That's your prompt.

The details that matter most:

Excel or Google Sheets (and if Excel, roughly which version, like "Excel 365" or "Excel 2016" if you know). Newer Excel has functions older versions don't.
Column letters and what's in them. "Column A is the date, column B is the category, column C is the amount."
Where the data starts. "Headers are in row 1, data starts in row 2 and goes down to about row 500."
Sheet (tab) names, if the formula needs to reach another tab.
A few example rows, made up but realistic.
The exact result you expect for those examples.

A prompt template you can copy

Here's a template. Fill in the brackets:

I'm using [Excel 365 / Google Sheets]. My data is on a tab called [Sheet1]. Row 1 has headers. Data starts in row 2 and goes to about row [500]. Column A: [what it holds, e.g., date in MM/DD/YYYY] Column B: [e.g., category, text like "Groceries" or "Gas"] Column C: [e.g., amount in dollars, numbers only] Here are three example rows (fake data): [paste or type 3 to 5 rows] I want a formula in cell [F2] that [does exactly what]. For the example rows above, the answer should be [expected result]. If a cell is blank, [what should happen]. Please give me the formula, then explain in plain English what each part does.

That last line, "explain what each part does," is your best friend. It forces the AI to show its reasoning, and it teaches you a little every time.

Why fake example rows beat real data

You might be tempted to copy fifty real rows and paste them in. Don't, for two reasons.

First, privacy. Anything you paste into a chatbot may be stored and, depending on the service and your settings, used to improve the product. Customer names, home addresses, Social Security numbers, account numbers, salaries, medical details: none of that belongs in a chatbot. If your job has rules about AI tools, follow them.

Second, fake rows are clearer. Three tidy examples with a known right answer tell the AI more than fifty messy real ones.

Swap "Maria Gonzalez, 412 Elm St" for "Customer A, Address 1." The formula doesn't care about the real names.

Step 3: Paste the formula into a copy and test it

The AI gives you something like this:

=SUMIFS(C2:C500, B2:B500, "Groceries")

Now:

Click the cell where you want the answer.
Paste the formula (starting with the equals sign).
Press Enter.
Check it against something you can do by hand. Filter the column, or pick five rows and add them up on your phone's calculator. Does it match?

If it matches on a small test, try it on a case that might break it: a blank row, a category typed with a trailing space, a date at the edge of the month.

When it gives an error

Spreadsheet errors look scary but each one means something specific:

#NAME? The program doesn't recognize a function name. Often this means the AI used a function your version doesn't have (for example, a newer Excel function in an older Excel), or there's a typo, or quotes got turned into "curly" quotes when you copied.
#VALUE! Something is the wrong type, like text where a number should be.
#REF! The formula points at a cell or range that doesn't exist (often after deleting rows or columns).
#N/A A lookup didn't find a match.
#DIV/0! You're dividing by zero or by a blank cell.
#SPILL! (newer Excel) The formula wants to fill several cells, but something is in the way.

When you hit one, go back to the chat and paste the exact error and the formula. Say: "I got #NAME? with this formula in Excel 2016. What's wrong?" The AI is usually good at fixing its own mistakes once you tell it what happened.

The curly quote trap

This one bites a lot of people. Chatbots and word processors sometimes turn straight quotes (") into curly "smart" quotes. Spreadsheets only understand straight quotes. If a formula with text in quotes errors out for no clear reason, delete each quote mark and retype it directly in the formula bar.

The formulas people ask AI for most

You don't need to memorize these. But knowing roughly what they do helps you spot when the AI picked the wrong tool.

Adding things up with conditions: SUMIF and SUMIFS

"Add up column C, but only where column B says Groceries."

=SUMIF(B2:B500, "Groceries", C2:C500)

"Add up column C where B is Groceries AND the date in A is in March 2026."

=SUMIFS(C2:C500, B2:B500, "Groceries", A2:A500, ">="&DATE(2026,3,1), A2:A500, "<"&DATE(2026,4,1))

Notice the order flips between SUMIF and SUMIFS. In SUMIF, the "what to add" range comes last. In SUMIFS, it comes first. That's a classic place for mistakes, and a good reason to ask the AI to explain each part.

Counting: COUNTIF and COUNTIFS

"How many orders are marked Shipped?"

=COUNTIF(D2:D500, "Shipped")

Same idea as SUMIF, just counting instead of adding.

Looking things up: VLOOKUP, XLOOKUP, INDEX/MATCH

This is the big one. "I have a customer ID in this tab. Go find their phone number on the Customers tab."

Older, very common:

=VLOOKUP(A2, Customers!A:D, 3, FALSE)

That says: look for the value in A2, in the first column of Customers!A:D, and return the 3rd column of that range. The FALSE means "exact match only." Forgetting FALSE is one of the most common spreadsheet mistakes on Earth; without it, VLOOKUP may return a close-but-wrong match.

Newer Excel and Google Sheets have XLOOKUP, which is easier to read:

=XLOOKUP(A2, Customers!A:A, Customers!C:C, "Not found")

Look for A2 in column A of Customers, return what's in column C on the same row, and say "Not found" if there's no match.

If you're on an older Excel, ask the AI for INDEX/MATCH instead. It works almost everywhere:

=INDEX(Customers!C:C, MATCH(A2, Customers!A:A, 0))

Making decisions: IF, IFS, and friends

"If the amount is over 100, say 'Big', otherwise 'Small'."

=IF(C2>100, "Big", "Small")

"If the due date has passed and the status isn't Paid, say 'Late'."

=IF(AND(E2<TODAY(), F2<>"Paid"), "Late", "")

The empty quotes at the end mean "show nothing." That's often nicer than a wall of "No."

Hiding ugly errors: IFERROR

Wrap a formula in IFERROR to show something friendlier when it fails:

=IFERROR(VLOOKUP(A2, Customers!A:D, 3, FALSE), "Not found")

Use this carefully. IFERROR hides every error, including ones that mean your formula is broken. Get the formula working first, then add IFERROR for the expected "no match" cases.

Cleaning text: TRIM, PROPER, LEFT, RIGHT, MID, TEXTSPLIT

Messy lists are where AI really earns its keep.

TRIM removes extra spaces (the invisible reason "Groceries " doesn't match "Groceries").
PROPER turns "jOHN smITH" into "John Smith."
LEFT/RIGHT/MID grab part of a text value, like the first five digits of a ZIP+4.
SPLIT (Google Sheets) or TEXTSPLIT (newer Excel) breaks "Smith, John" into two cells.

A prompt like "Column A has full names like 'Smith, John'. I want first name in B and last name in C. Some names have middle initials" will usually get you a solid answer, and the AI will often ask (or should be asked) how to handle edge cases like "de la Cruz."

Dates: DATEDIF, NETWORKDAYS, EOMONTH, TEXT

Dates are stored as numbers under the hood, which confuses everyone. AI is helpful here:

"How many days between A2 and B2?" Usually just =B2-A2.
"How many workdays between them, skipping weekends?" =NETWORKDAYS(A2, B2).
"What's the last day of the month for the date in A2?" =EOMONTH(A2, 0).
"Show the month name from a date." =TEXT(A2, "mmmm").

Date formats differ by country (is 03/04 March 4th or April 3rd?). Tell the AI which format your sheet uses.

Pulling unique values and filtering: UNIQUE, FILTER, SORT

Google Sheets and newer Excel have functions that return whole lists:

=UNIQUE(B2:B500)
=FILTER(A2:C500, B2:B500="Groceries")
=SORT(A2:C500, 3, FALSE)

These "spill" into the cells below. Leave empty room under them, or you'll get #SPILL! in Excel or a #REF! in Sheets.

Excel vs. Google Sheets: the differences that trip up AI

Most everyday formulas work the same in both. But some things differ, and if you don't say which program you're in, the AI may mix them up.

Also: in some countries' settings, formulas use semicolons instead of commas between arguments (=SUM(A1;A2)). If you're outside the US and commas keep failing, tell the AI your locale.

Step 4: Ask the AI to explain, not just to answer

Here's where you go from "copy-paster" to "person who actually understands their spreadsheet."

After you get a working formula, ask:

Explain this formula like I'm new to spreadsheets. Go piece by piece. What would happen if column B had a blank cell?

You'll get a breakdown. Read it once. Next time you need something similar, you'll recognize the pattern. Within a few weeks, you'll be writing simple ones yourself and only calling the AI for the gnarly stuff.

You can also flip it around and paste in a formula you inherited:

I found this formula in a spreadsheet at work. What does it do, in plain English? Is there anything risky or fragile about it?

AI is genuinely good at untangling a nested IF from 2011 that nobody dares touch.

Step 5: Check the answer like a skeptic

AI formulas fail in sneaky ways. They don't always error out. Sometimes they return a number that looks reasonable and is wrong. Here's your checklist:

Hand-check a few rows. Pick three to five rows and work out the right answer yourself. Does the formula match?
Test an edge case. A blank. A zero. A date on the first or last day of a month. Text with a stray space.
Check the ranges. Does C2:C500 actually cover all your data, or does your data go to row 812? Ranges that stop short are a silent killer.
Check the dollar signs. When you drag a formula down, $A$1 stays pinned and A1 moves. If the AI's formula needs to be dragged, ask: "Will this still work if I drag it down column F?"
Check exact vs. approximate match in lookups. Ask: "Is this an exact match?"
Compare totals. If you split something into categories, do the category totals add up to the grand total?

If anything's off, go back to the chat with specifics: "Row 14 should be 42.50 but your formula shows 0. Row 14's category cell says 'groceries' in lowercase."

Common AI mistakes to watch for

Using a function your version doesn't have. Fix: say your version up front.
Assuming headers aren't there (or are). Fix: say "row 1 is headers."
Mixing up Excel and Sheets syntax. Fix: say which one, every time.
Making up a function. It happens. If you get #NAME? and the function name looks unfamiliar, ask: "Is that a real Excel function? Please use only built-in functions."
Overcomplicating it. Sometimes you'll get a 200-character monster when a simple SUMIF would do. Ask: "Is there a simpler way?"

Beyond single formulas: what else free AI can help with

Conditional formatting rules

"Highlight the whole row red if column F says Late." Conditional formatting uses formulas too, and AI can write them. In both programs, you'd pick "custom formula" and use something like =$F2="Late". The dollar sign before F matters: it locks the column so the whole row lights up.

Data validation (dropdowns)

"Make column B a dropdown with Groceries, Gas, Rent, Other." AI can walk you through the menu clicks, which differ slightly between Excel and Sheets.

Pivot tables

AI can't click for you, but it can explain how to set one up: "I want a summary of total amount per category per month. Walk me through making a pivot table in Google Sheets."

Macros and scripts (proceed carefully)

AI can write Excel VBA macros or Google Apps Script code. This is powerful but riskier. Code can delete data, send emails, or touch other files. Rules of thumb:

Only run code on a copy.
Ask the AI to explain what the code does line by line before you run it.
Never run a macro or script someone sent you (or that a chatbot wrote) if you don't understand what it touches.
At work, check whether macros are even allowed. Many companies block them for security reasons.

Privacy and work rules: the boring part that matters

It bears repeating, because this is where people get in real trouble:

Don't paste confidential data into any chatbot unless your organization has approved that tool for that data.
Strip identifying details. Use fake names and numbers.
Check your chatbot's settings. Many services let you turn off chat history or opt out of having your chats used for training.
Uploading a file is still sharing it. Some free chatbots let you upload a spreadsheet. That's convenient, but it's the same privacy decision as pasting it.
Health, financial, and HR data often have legal rules attached. If you work with that kind of data, ask your IT or compliance people before using any AI tool.

A worked example from start to finish

Let's say you run a small side business and track orders in Google Sheets.

Your sheet:

Column A: Order date
Column B: Customer (you'll use "Customer A," "Customer B" in the prompt)
Column C: Amount
Column D: Paid? (Yes/No)

What you want: In column E, show "Overdue" if the order is more than 30 days old and not paid. Otherwise blank. Then, in a cell at the top, total up how much money is overdue.

Your prompt:

I'm using Google Sheets. Row 1 is headers. Data starts in row 2 and goes to about row 300. A: order date (MM/DD/YYYY). B: customer name. C: amount in dollars. D: "Yes" or "No" for paid. Example rows: 08/01/2026, Customer A, 120, No 09/20/2026, Customer B, 45, No 07/15/2026, Customer C, 300, Yes In E2 (and dragged down), I want "Overdue" if the order is more than 30 days before today and D is "No". Otherwise leave it blank. Blank rows should stay blank. Then, in cell G1, I want the total amount of all overdue orders. Please explain each formula.

What a good answer looks like:

E2: =IF(A2="", "", IF(AND(TODAY()-A2>30, D2="No"), "Overdue", ""))
G1: =SUMIFS(C2:C300, E2:E300, "Overdue")

Plus an explanation: the first IF handles blank rows; TODAY()-A2 is the number of days since the order; AND requires both conditions; SUMIFS adds column C only where E says Overdue.

Your check: Today matters here, so pick a row and count the days on a calendar. Mark one order "Yes" and confirm "Overdue" disappears. Add a blank row and confirm nothing shows.

A follow-up you might ask: "Some people typed 'no' in lowercase or 'N'. Can you make it handle those?" The AI might suggest wrapping D2 in LOWER() or using a dropdown so people can't type variations. The dropdown is the better long-term fix, and it's worth asking the AI which it recommends and why.

Frequently asked questions

Is it cheating to use AI for spreadsheet formulas?

No more than it's cheating to look something up in the help docs or ask a coworker. The point of a spreadsheet is to get the right answer. What matters is that you check the answer and understand it well enough to stand behind it. If you're a student, follow your class rules, since some instructors want you to write formulas yourself to learn.

Which free AI is best for Excel formulas?

Several of the big free chatbots handle common formulas well, and they leapfrog each other often. The best one is usually the one you'll actually use carefully.

Can AI read my whole spreadsheet?

Some chatbots accept file uploads, and the built-in helpers in Excel and Sheets can read your open file. But for most formula help, you don't need to share the whole thing. A clear description plus a few fake rows is safer and usually gets a better answer.

Why does the AI's formula work in its example but not in my sheet?

Usually one of these: your data starts on a different row, your ranges are shorter than your data, your cells contain text that looks like numbers (or extra spaces), your version lacks a function, or you're in Sheets and it wrote Excel (or vice versa). Paste the error and the details back into the chat.

What if my numbers are stored as text?

You'll often see a small green triangle in Excel, or numbers aligned left instead of right. Formulas like SUM may skip them. Ask the AI: "My numbers in column C seem to be stored as text. How do I convert them?" Common fixes include VALUE(), multiplying by 1, or using the built-in "convert to number" option.

Can AI build me a whole budget spreadsheet?

It can suggest a layout, the column headers, and the formulas for each part. You'd still build it in your spreadsheet program. Ask it to go one section at a time so you can test as you go, rather than pasting a giant plan all at once.

Should I trust a formula I don't understand?

Not for anything important. Ask the AI to explain it until you could describe it to someone else in one or two sentences. If it's for money, taxes, payroll, or anything someone else will rely on, double-check with a person who knows spreadsheets, too.

The takeaway

Free AI is a great formula-writing buddy. It turns "I know what I want but not how to type it" into a working formula in seconds. The people who get good results do three simple things: they describe their sheet clearly (program, columns, starting row, example rows, expected answer), they test on a copy against answers they can check by hand, and they keep private data out of the chat.

Start small. Next time you're stuck on a SUMIF or a lookup, open a free chatbot, use the template above, and ask it to explain its answer. Do that a dozen times and you'll notice something: you're starting to write the easy ones yourself. That's the real win. The AI isn't replacing your skill. It's a patient tutor that never gets tired of "wait, why is there a dollar sign there?"

If something in this guide looks off, or a function has changed since we wrote it, tell Austin. Spreadsheets change, and so does AI. We'd rather fix it than pretend.