How to Use AI to Write Excel and Google Sheets Formulas

You know what you want the spreadsheet to do. You just don't know whether it's SUMIF, SUMIFS or something involving INDEX and MATCH that you saw once in 2019. This is where AI chatbots are genuinely brilliant.

The basic method

Tell the AI three things:

1. Which app (Excel or Google Sheets, since a few functions differ)
2. What your columns contain, with the column letters
3. What result you want, in plain English

Example prompt:

In Google Sheets, column A has dates, column B has customer names, column C has amounts and column D says "Paid" or "Unpaid". Write a formula that adds up all unpaid amounts for October 2026.

You'll get something like:

=SUMIFS(C:C, D:D, "Unpaid", A:A, ">="&DATE(2026,10,1), A:A, "<"&DATE(2026,11,1))

Plus an explanation of each part, which is the bit that helps you learn.

Five formulas people ask for constantly

Look up a value from another table

"Sheet2 has product codes in column A and prices in column B. In Sheet1, column A has product codes. Get the matching price."

Modern Excel and Google Sheets both support XLOOKUP, which is much friendlier than the old VLOOKUP.

Count things that match a condition

"Count how many rows in column D say 'Late' and have a value over 100 in column C."

Expect a COUNTIFS.

Work with dates

"Column A has due dates. In column E, show 'Overdue' if the date is before today and column D isn't 'Paid'."

Clean messy text

"Column B has names like ' john SMITH '. Make them 'John Smith' with no extra spaces."

A combination of TRIM and PROPER handles it.

Split or combine columns

"Column A has full names. Put first names in B and last names in C."

Built-in AI in your spreadsheet

Both Google Sheets (with Gemini, on eligible plans) and Excel (with Copilot, on eligible Microsoft 365 plans) now have AI built in. They can see your actual sheet, which saves describing the columns. The chatbot method above works for everyone, though, on any plan.

How to check the formula is right

AI-written formulas are usually right, but "usually" isn't good enough for invoices or payroll.

1. Test on a few rows you can check by hand. If you can add three numbers in your head and the formula agrees, good sign.
2. Try an edge case: a blank cell, a text value where a number should be, a date at the very end of the month.
3. Ask the AI "What situations would make this formula give a wrong answer?" It's often good at spotting its own weak points.
4. Watch out for the region trap. Some countries use semicolons instead of commas in formulas. If you get an error, that's often why.

When you get an error

Paste the formula and the error message back into the chat, along with an example of the data. "This gives #N/A for row 5, where A5 is 'SKU-0041'." It'll usually find the problem in one try. Often it's a stray space, a number stored as text or a missing $ sign.

Don't paste sensitive data

Describe your columns instead of pasting real customer names, salaries or financial data. See how to protect your chats for more.