Google Sheets: Replace Complex Formulas with Plain English

Jeff -

(you can easily filter previous issues by application!)

Hey friends - If you've explored my latest Google Gemini tutorial, you're likely aware of the powerful "=AI()" formula. This feature allows you to prompt for information and actions without relying on predetermined formulas.

In the gif above, you can see that I'm asking Google Sheets to categorize the feedback from Column D using just natural language; no more wrestling with nested IF statements or complex SWITCH formulas.

💡
The exact formula I used: =AI("Categorize this feedback under Price, UI/UX, Privacy, or Others",D2)

The Real Magic: Referencing Multiple Cells

Now that we've covered the basics, what if we wanted to reference multiple cells within a single =AI() formula?

Here's a real scenario: I have a table that tracks my sales calls with clients, and I'd like to quickly and easily draft a follow-up email based on my notes. Previously, this would require switching between applications or manually copying data.

0:00
/0:40

As you can see in the screen capture above, I can simply use the "&" sign to reference specific cells and "chain" them together to achieve my goal. Here's what each cell contains in my example:

  • Cell B9: [contact name]
  • Cell C9: [company]
  • Cell D9: [last contacted date]
  • Cell E9: [notes]
💡
The exact formula I used: =AI("Draft a short, friendly follow-up email to "&B9&" from "&C9&". The last topic we discussed was about the "&E9&". Keep it under 50 words.")

Notice how I'm concatenating the prompt text with cell references using the ampersand (&) operator. This allows the AI to understand the context from different parts of your spreadsheet and generate personalized content based on your specific data.

This is extremely useful when your output requires input from different cells and yet you need to apply the "same prompt" across multiple rows. Imagine generating hundreds of personalized emails, each pulling relevant information from different columns - all with a single formula that you can drag down!

Understanding the Limitations

Before you go wild with =AI(), keep these constraints in mind:

  • Currently supports text-based responses only (no numerical calculations or date manipulations)
  • Works best for text generation, summarization, categorization, and sentiment analysis
  • You can select up to 200 cells with AI functions to generate at once
  • When you update a referenced cell or prompt, the formula shows an "out-of-sync" state but keeps the previous output for reference

I know this might all look overwhelming but my advice is to start small: try categorizing a list or summarizing some feedback first. Once you see the power firsthand, you'll find yourself discovering new applications every day.

Whenever you're ready, here are some other ways I can help you:

✅ Build Your Command Center in Notion: Create a single-dashboard system and organize everything in one place. Save yourself 5 years of trial-and-error.

💻 The Workspace Academy: Learn the workflow I taught to over 10,000 Googlers. Made for busy professionals using Google Workspace.