The most powerful formula in Google Sheets and Microsoft Excel isn't VLOOKUP anymore

Welcome to Prompt, Tinker, Innovate, the newsletter that treats AI as a tool, not just a topic. Every edition delivers one powerful experiment—a prompt you can use right away to solve a real problem, sharpen your strategy, or unlock a new idea.

This week’s playground: Automating knowledge work with the "=AI()" and "=COPILOT()" formulas in Sheets and Excel

Spreadsheets are the undisputed champions of numbers and structured data. But for anything creative or qualitative—like summarizing text, categorizing feedback, or brainstorming ideas—we've always had to leave the grid. We copy our data, paste it into a separate AI tool, get our answer, and paste it back. It’s a clunky, multi-step process that breaks our workflow.

The introduction of a native =AI() function in Google Sheets and =COPILOT() in Microsoft Excel changes everything. It brings the power of generative AI directly into the cell, right alongside your data. This simple formula collapses entire workflows, automating tedious tasks and unlocking creative possibilities on a massive scale. It's the biggest evolution for spreadsheets since the pivot table.

Analysis and creation at the speed of thought

Imagine this. A product marketing manager has a spreadsheet with 200 pieces of raw customer feedback for a new feature. The old way: Spend half a day reading each comment, manually creating a new "Sentiment" column, and typing "Positive," "Negative," or "Neutral."

The new way: In the column next to the feedback, they type the formula =AI("Analyze the sentiment of this feedback", A2). They hit enter, and in seconds, the cell populates with "Positive." Then comes the magic moment: they drag the formula down the entire column. The entire half-day task is done in less than a minute. They then add another column: =AI("Extract the core feature request from this feedback", A2) and drag it down again, instantly creating a categorized feature request list.

Your AI experiment: Try this prompt

It’s time to turn one of your own spreadsheets from a static grid into a dynamic analysis tool. Note: this feature is not available in Microsoft Excel.

👉 Time to tinker: Open an existing Google Sheet you're already working with. This could be a project plan, a list of customer feedback, a content calendar—anything with a column of text you want to analyze or build upon. Click on an empty column next to your data. Let's assume the text you want to work with is in column B.

📝 Prompt: In the first empty cell of your new column (e.g., C2), try one of the following formulas. Pick the one that best fits your data. Note: these prompts below are for Google Sheets. To convert them to Microsoft Excel, replace the "=AI()" with "=COPILOT()".

• To summarize text: =AI("Summarize this in 10 words or less", B2)

• To categorize information: =AI("Categorize this into one of the following: 'Marketing', 'Sales', or 'Operations'", B2)

• To analyze sentiment: =AI("Analyze the sentiment of this text as 'Positive', 'Negative', or 'Neutral'", B2)

• To extract action items: =AI("Extract the main action item from this text. If there isn't one, write 'N/A'", B2)

To build your own formula, follow this basic structure.

• For Google Sheets: =AI("Your instruction in quotes", [cell reference/range])

• For Microsoft Excel: =COPILOT("Your instruction in quotes", [cell reference/range])

For more advanced prompts, you can combine text with multiple cells using the "&". This lets you build a prompt from different pieces of data. For example, if your product name was in cell A2 and its main benefit was in C2, your formula could look like this: =AI("Write a tweet about "&A2&" that highlights its main benefit of "&C2).

This is the magic part: Once you get your first result, click the cell. Drag the small blue square at the corner (the fill handle) down the column. Watch as the AI performs the task on all your rows in seconds. The key is to adapt the prompt to fit your specific goal and data.

💡 Pro tip:

• Chain your prompts: The real power comes from using the output of one =AI() cell as the input for another. If you generated a tagline in cell D2, you could add another column with the formula: =AI("Translate this tagline into Spanish", D2). You can build entire workflows this way.

• Control the output: Be specific in your prompt to get clean, usable data. Add phrases like "...answer in 3 words or less," "...use a professional tone," or "...format the output as a comma-separated list" to ensure the AI returns data that fits neatly into your spreadsheet.

What did you discover?

How does having a creative partner inside your spreadsheet change your perspective?

• What tedious, manual task could this formula completely eliminate from your workday?

• What's the most interesting workflow you can imagine by chaining multiple =AI() or =COPILOT() prompts together?

• Did this spark any ideas for using spreadsheets in a completely new way?

Share your thoughts and experiments in the comments!

Until next time—keep tinkering, keep prompting, keep innovating.