Generating Google Sheets Formulas with AI

The number one use case for AI with Google Sheets is writing formulas. Instead of memorizing syntax, you describe what you want and AI writes the formula for you.

The Prompt Template

Use this template every time you need a Google Sheets formula:

Notice the key phrase "Google Sheets formula (not Excel)"—this ensures AI uses Google Sheets syntax, which differs from Excel in important ways.

Common Formula Scenarios

Lookups

Describe the relationship between your sheets:

"Sheet1 column A has product IDs. Sheet2 has product IDs in column A and prices in column B. I need to show the price for each product in Sheet1 column B."

AI will suggest XLOOKUP or VLOOKUP with the correct cross-sheet references like Sheet2!A:B.

Conditional Sums and Counts

Tell AI your conditions:

"Column A has department names. Column B has salaries. I want the total salary for the 'Marketing' department only."

AI will give you: =SUMIF(A:A, "Marketing", B:B)

The ARRAYFORMULA Advantage

In Google Sheets, ARRAYFORMULA lets you apply a formula to an entire column from a single cell. Always ask AI for the ARRAYFORMULA version:

"Give me an ARRAYFORMULA version so I don't have to copy it down to every row."

For example, instead of writing =B2*C2 in every row, AI will give you: =ARRAYFORMULA(IF(B2:B="", "", B2:B*C2:C))

The QUERY Function

Google Sheets has a QUERY function that works like a database query. It's extremely powerful and AI is great at writing these:

"I have sales data with Date in A, Region in B, Product in C, Amount in D. Write a QUERY formula that shows total sales by region, sorted highest to lowest."

AI will produce: =QUERY(A:D, "SELECT B, SUM(D) WHERE B IS NOT NULL GROUP BY B ORDER BY SUM(D) DESC LABEL SUM(D) 'Total Sales'")

Bad vs Good Prompts

Bad

"How do I add numbers in Google Sheets?"

Too vague—AI doesn't know your column layout or conditions.

Good

"Column A has product categories (Electronics, Clothing, Food). Column B has sale amounts. I want to sum only the Electronics sales. The formula goes in cell D2."

Specific columns, data examples, clear goal, and target cell.

Key Takeaway

Always describe your column layout, provide examples of your data, and specify that you want a Google Sheets formula. The more context you give, the more accurate the formula will be on the first try.