How to Use Gemini in Google Sheets: 7 New Tricks (2026)
Gemini in Google Sheets changed a lot in 2026. It no longer just chats in a side panel: it makes a plan and edits your sheet for you. Here are seven tricks, from simple to seriously useful, all on one messy sales export. Every prompt is below, word for word.
Follow along with the same file. The practice file has the messy export, a clean copy to check your answers, and all 7 prompts on the "Try it" tab. Upload it to Google Drive and open it with Google Sheets.
What you need
You need Gemini in Sheets, so a Google AI Pro plan or a Google Workspace business plan (Business Standard and up). It works in Google Sheets on the web, and for now in English only. Open the Gemini panel with the spark icon at the top right of your sheet.
Trick 1: Clean up a messy export with one prompt
The starting point is what a sales system often gives you: everything lands in one column, the spacing is all over the place, and the regions are typed in three different ways. Write the prompt in three parts: what to do (split column A into columns), the names of the new columns, and the clean-up.
Split column A into separate columns: Date, Region, Rep, Product, Units, Revenue. Fix the capitalization in Region and format Revenue as dollars
No menus and no formulas: Gemini splits the column and edits the sheet straight away. The result is six real columns, every region spelled the same, and revenue in dollars.
Tip: always give Gemini the column names. Then you get exactly the headers you want.
Trick 2: Ask your data a question
Before you build a single formula, ask who and what drives the revenue. Ask for three insights, so the answer stays short.
Which rep and which product drive the most revenue? Give me 3 insights
In the video, Gemini found that Dave is the top rep with $6,750, about 40% of revenue. Laptops bring in $12,000, almost three quarters of everything. And the interesting one: mice and keyboards are most of the units, but only about ten percent of the money.
I checked every number with my own formulas, and they were right. Still, check them yourself the first time: it's AI, so trust, but verify. These are the check formulas from the practice file (on the "Clean data" tab):
=SUMIF(C2:C13, "Dave", F2:F13) Dave's revenue: 6750 (the top rep) =SUMIF(D2:D13, "Laptop", F2:F13) laptop revenue: 12000 =SUM(F2:F13) total revenue: 16685 =COUNTIF(F2:F13, ">1000") sales over 1,000: 6
Trick 3: Let Gemini fix a broken formula and explain why
Here is a lookup that breaks on purpose. First what to look for: the name Dave, in quotes. Then where to look: the rep column C through revenue in F. Then which column to return: 5, which is wrong. FALSE means an exact match.
=VLOOKUP("Dave", C2:F13, 5, FALSE)
The cell shows a #REF! error. Click the cell, and next to it you get a Gemini button to fix it. Gemini explains the problem in plain English: C to F is only four columns wide, so there is no fifth column. It offers the fixed formula with a 4:
=VLOOKUP("Dave", C2:F13, 4, FALSE)
Click Insert, and the cell shows Dave's first sale: $3,600. You fixed it, and you know why it broke.
Trick 4: The AI function puts Gemini inside a cell
Say you want a category for every product. Type a header in G1: Category. Then in G2, type the AI function. The first part is the instruction, in quotes. The second part is the cell to read: D2, the product in this row.
=AI("Is this product a Computer, Screen or Accessory? Answer with one word", D2)
Laptop becomes Computer. Now fill it down to the last row (G13), and every product gets its category. If one cell stays striped, that's a copied answer, not a new one: click it and choose Generate and fill.
In the video, I read all twelve rows: monitor is Screen, mice and keyboards are Accessory. The difference with the panel: this is a formula, so it lives in the cell.
Trick 5: One prompt, a plan, three jobs done
Dropdowns, highlights and a pivot table normally mean three different menus. Now it's one prompt, built part by part: a dropdown for the Region column, revenue over 1,000 in green and bold, and a pivot table of revenue by region on a new sheet.
Add a dropdown to the Region column, highlight Revenue over 1000 in green and bold, and create a pivot table of revenue by region on a new sheet
Here's the new part: Gemini doesn't do it straight away. It shows a plan first, with three steps, exactly the three things you asked for. You can still change the prompt at this point. If the plan looks right, click Approve.
The Region column now has dropdown chips, so nobody can type "south" in lowercase again. Every sale over 1,000 is green and bold (six of them). And on a new tab there's the pivot table: East $3,900, North $3,600, South $3,135, and West on top with $6,050. Three menus in one sentence.
Trick 6: A chart without the chart editor
Don't fight the chart editor. Tell Gemini the chart type and what to show. Ask for the chart on its own, in a separate prompt: mixed into a bigger question, it skipped the chart in my tests.
Create a column chart of total revenue by rep. Only the chart, no extra text.
The chart shows up in the panel first: Dave far ahead, then Carla, Anna and Ben almost level. Click Insert to put it in your file.
Good to know: Insert puts the chart on a new tab, with its own small copy of the totals. So it's a snapshot. If your data changes, ask again, or build the chart from the pivot table.
Trick 7: A canvas app with a button per category
The last trick puts it all together. In the Gemini panel, open Tools and choose Create canvas. That builds a small app on top of your sheet. Ask for a dashboard with total revenue, a chart by rep, and a button for every category (the categories the AI function made in trick 4):
Make a sales dashboard from Sheet1 with total revenue, a chart of revenue by rep, and a filter button for each Category
In the video, the app shows $16,685 in total, revenue by rep, and the category buttons. Click Accessory: only $1,685. That's the insight from trick 2 again, now as a button anyone can click.
Recap: which trick first?
- Clean up a messy export with one prompt (give it the column names).
- Ask your data a question, and check the numbers.
- Let Gemini fix a #REF! error and explain why.
- Use the AI function to categorize every row.
- One prompt, a plan, Approve: dropdown, highlight and pivot table.
- A chart from a prompt (a snapshot on a new tab).
- A canvas app with filter buttons.
Want every 2026 function on one page? The free Formula Pack is a 3-page cheat sheet plus all practice files from the videos.