Hi Reader,

Welcome to the Google Sheets Tips newsletter #390, your Monday Tuesday* morning espresso, in spreadsheet form!

* public holiday in the US yesterday.

Speaking of coffee, I usually work from my local coffee shop twice a week. It makes for a nice change of scenery from home. And everytime I order the same thing: a regular cappuccino, no frills, no syrups, no fancy milks.

I think it says a lot about my personality and stage in life. As a working parent, neck deep in the world of AI, I'm feeling a lot of information overload and decision fatigue. So sticking to my usual order means one less decision I have to make each day.

But while consistency works at the cafe, the AI world is anything but consistent and routine. The tools and workflows are evolving so fast that we're forced to continually change to keep up.

Being adaptable isn't a nice-to-have anymore, it's an essential skill that we all have to develop.

So whilst I'm sipping my cappuccino, I'll do my best to keep on top of it all and pass those lessons on to you to save you time.

In today’s newsletter, we're turning formula prompting into a set of guidelines you can use to get the best possible results every time.

➜ News

I.
​During last week's Google I/O conference, the company introduced the agentic Gemini era: a major shift toward AI that doesn't just answer questions, but proactively takes action on your behalf.

Specifically, they introduced the new Gemini 3.5 Flash model, Gemini Omni (for video), deep integration of Gemini features into everyday products, Gemini Spark (a 24/7 personal agent), personalized Gemini agents in Search, and voice-powered Docs Live.

​Read more here >>​

II.
​Google also announced Antigravity 2.0, the new version of their agentic coding platform. It's a phenomenal tool that I've been using since January for most of my coding projects, from Apps Script add-ons to making games with my kids.

​Read more here >>​

It's one of the primary tools, along with AI Studio, that I'll be covering in my Gemini Vibe Coding course later this year (a follow up to the 28 Days to Gemini Mastery course).

➜ Sheets Tip #390: Tips for writing better AI prompts for creating Google Sheets Formulas

Now that we rely on Gemini to write our formulas instead of writing them ourselves (see: Is Formula Writing Dead?), intentional prompting is essential.

Give the AI vague inputs, you'll get vague outputs.

Use this handy guide to refine your prompts and generate better formulas. Just like delegating to a coworker, Gemini works best with clear, precise instructions.

🌟 Prompt Tips

Always include:

• Exact cells and ranges that contain your data.
• Desired output or sample expected result.
• If you're using an AI chatbot outside of Google Sheets, explicitly specify "Google Sheets" in your prompt.

Name inputs clearly:

• Use precise references like "A2:A10" or "column A".
• Example: "Start date in F1, end date in F2".
• Avoid vague names like "my data" or "this column".

State output behavior:

• Specify whether you want a single cell output or spilled array across a range.
• Example: "Output must autofill column B, rows 2 to 20".

Ask for specific functions (optional):

• Ask for specific functions if you know that you require them.
• Example: "Use ARRAYFORMULA".

Consider error handling (optional):

• Ask AI to explain error messages.
• Example: "Why does this formula give a #REF! error".
• Ask AI to handle errors deliberately.
• Example: "Show 'Value not found' instead of an error".

Add constraints (optional):

• Example: "Avoid using open column references".
• Example: "Optimize for performance".

📋 Copy-Paste Prompt Template

I’m working in Google Sheets.

Context: [what the sheet does] (optional).

Inputs / Data: [describe cells and ranges].

Task: [plain-English explanation of what you want to do].

Any Constraints: [ARRAYFORMULA / IFERROR / single cell / spill result].

Output: [format / location / example output].

🗂️ Prompt Examples

Simple Example

Create a Google Sheets formula to calculate the average value of data in the range D5:D20. The output should be a single value in a cell.

Debug Formula

What am I doing wrong with this Google Sheets formula? I get an error message "Wrong number of arguments to VLOOKUP." =VLOOKUP(A2,E1:F11,2,,false)

Spilled Array Calculation

Create a formula in Google Sheets that takes two columns from Table1 called "Revenue" and "Expenses" as inputs and subtracts them to output the profit. The single formula should output an array of values that spill down the column.

Translate BYROW to MAP

In Google Sheets, I've created a BYROW formula to figure out the minimum year associated with each ID number. The years are in column A and the IDs are in column B. The formula is:
​
=BYROW(B2:B11,LAMBDA(r,MIN(FILTER(A2:A11,B2:B11=r))))
​
Create an equivalent MAP formula that calculates the same output.

🙋 Any comments? Or anything you'd add to this mini prompt guide?

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. What would happen if you let AI cut your sandwich...​