Phase 4 · Gemini · Level 2 · Practitioner
Gemini in Sheets and Drive
By the end, you'll be able to…
- Use the Ask Gemini side panel in Sheets to build a formula and get a plain-English read of your data
- Ask Gemini in Drive a question that spans several of your files at once
- Check a formula or a cross-file answer against the source before you rely on it
Why it matters
Sheets and Drive are where Gemini stops being a writing helper and starts being an analyst. In Sheets it can write the formula you can't remember and summarise what a table is telling you. In Drive it can answer a question that lives across several documents without you opening them one by one. Both are powerful, and both are exactly where a confident wrong answer does the most damage, so checking matters most here.
Two jobs: reading data, and reading across files
This lesson covers two related in-app helpers. In Google Sheets, the Ask Gemini side panel helps you write formulas and explains what your numbers say. In Google Drive, Gemini answers questions that span several files at once ("what did we decide about X across all these documents?") instead of you opening each one.
The same honest caveat as the last lesson applies, and it matters even more here because these are among the newer features: the Gemini side panel in Sheets and Drive depends on your account type and usually a paid plan (an eligible Google Workspace edition or a Google AI plan), and it rolls out gradually. If you don't see it, you're not doing anything wrong. Google's Using Google Workspace with Gemini page lists what your account includes. Where the in-app panel isn't available, you can still upload a file to the free app at gemini.google.com and ask the same questions. You just do it one file at a time.
We're using Fernway's sales spreadsheet: a month of deliberately messy sales rows (reps, regions, products, units and totals) with a few typos and blanks baked in, exactly like real data. You're Tom Elliott, trying to make sense of the quarter.
Gemini in Sheets: formulas and a read of the data
Open the sheet, click Ask Gemini (top right), and a panel opens. Two things it's good at:
- Writing formulas from a description. You describe the calculation in words and Gemini gives you the formula to paste, so you don't have to remember
SUMIFsyntax. - Reading the data back to you. Ask what stands out, and it summarises patterns (top rep, weakest region, an odd-looking row) in plain English.
Start with a formula. Tom wants each rep's total sales, but the data is messy: "Sotuh" appears where "South" was meant, and a couple of Total cells are blank.
Give me a formula for a cell that adds up the Total column (column G) for one rep, where the rep's name is in the cell to the left. The rep names are in column C and totals in column G. Explain what each part does in one line.
Why this works: Describing the calculation and the exact column layout lets Gemini return a formula you can paste and reuse, instead of you recalling SUMIF syntax. Naming the columns stops it guessing which is which.
Paste the formula, check one rep's figure by eye against a couple of rows, and you have a reusable calculation. Now ask Gemini to read the table, but notice what it can and can't be trusted on.
Look at this sales data and tell me, in plain English: which rep sold the most overall, which region looks weakest, and any rows that look like data-entry errors: blanks, misspellings or totals that don't match units times price. Don't fix anything; just list what you'd check.
Why this works: Asking Gemini to flag data-quality problems as well as the headline pattern turns it into a useful first pass: it surfaces the typos and blanks you'd otherwise miss, while you stay the one who decides what they mean.
A good answer both names a top performer and flags the real problems: the "Sotuh"/"Midlnads" typos that would split a region's total, the blank Units and Total cells, and at least one row where units times unit price doesn't equal the stated total (row where a Starter Plan of 10 units at 49.00 shows a total of 49.00, not 490.00). That last point is the lesson in miniature: Gemini is useful for spotting candidates to check, but you decide what's really wrong. If you'd asked it to silently "clean" the data, a misread typo could quietly merge or drop sales.
Fill with Gemini: extend a column from a pattern
Broadly rolling out from July 2026, two related features let Gemini generate the cell values themselves, not just hand you a formula to paste. Fill with Gemini is the AI cousin of Smart Fill: you type a couple of example cells, select down the column, and Gemini infers the pattern and fills the rest. The AI function, typed as =AI(...) in a cell, does the same job from an explicit instruction, and it recalculates like any other formula. Both depend on your account and plan and are still spreading, so a missing option is the usual "not included yet" story, not a fault.
These earn their place on messy, judgement-based columns a plain formula can't express. On the Fernway sales data, say Tom wants a tidied region column so that "Sotuh" and "Midlnads" stop splitting a region's total, something SUMIF can't fix on its own.
In a new column beside the region column, use the AI function to map each region name to its correctly spelled version from this set only: North, South, East, West, Midlands. For example, "Sotuh" becomes "South" and "Midlnads" becomes "Midlands". If a value doesn't clearly match one of those, leave it blank rather than guessing. Leave the original column unchanged.
Why this works: A formula can't reason about free-text typos, but a written instruction can. Spelling out the rule and the allowed set of answers gives Gemini a target it can fill down, while pointing it at a new column keeps the raw data untouched so you can always check its work against the original.
The catch is the one running through this whole lesson: Gemini is guessing each fill, confidently. It may map an ambiguous value to the wrong region, or invent a category that wasn't in your list. Treat a Fill with Gemini or AI-function column as a draft to check against the raw data, never a silent overwrite of it, and keep the original column so you can always see what changed.
Gemini in Drive: ask across your files
Drive's Ask Gemini answers questions that span several documents. Instead of opening the sales sheet, the meeting notes and the project brief separately, you ask one question and Gemini reads across them. This is grounding, where the answer is built from your actual files rather than the model's general knowledge.
Imagine Tom's Drive holds the Fernway sales sheet, the meeting notes and the project brief. He wants the through-line without a reading marathon.
Across my Fernway files, what's the current plan for the Harlow renewal and who owns it? Give me three bullet points, and for each one tell me which file it came from so I can check it.
Why this works: A cross-file question that names what you want (the decision, the owner, the source file) lets Gemini pull the thread together for you, and asking which file each point came from gives you a way to verify each claim.
The "which file it came from" clause is doing real work: it turns an unverifiable summary into something you can spot-check in seconds. Open the named file, confirm the point, and you can trust the rest more. If a bullet cites no file, treat it as the model filling a gap, the same invented-detail risk you met in Phase 0. Drive can also help tidy files (it will suggest grouping loose documents into folders), but the analysis question above is where it saves the most time.
Example prompts
Give me the formulas to build a small summary table: total sales per region, and average units per sale per region. Tell me which cells to put them in and warn me if the region names aren't consistent.
Why this works: Asking for the formula rather than a pasted result means the table updates itself when the data changes, and you can see exactly how each number is calculated instead of trusting an opaque figure.
Before I analyse this, list every data-quality problem you can see: blank cells, inconsistent spellings, dates in different formats, or totals that don't add up. Just the problems, so I can decide what to fix.
Why this works: Turning Gemini loose on data quality first, before any analysis, catches the errors that would otherwise poison every downstream number, a habit that saves you from confidently reporting a figure built on a typo.
Using my Fernway files, draft a three-sentence status update on the Harlow renewal for a manager. After each sentence, note which file the fact came from. Don't include anything not in the files.
Why this works: Asking Gemini to draft from your files and cite each source gives you a first draft grounded in real material, with a built-in trail so you can verify each claim before it goes in front of anyone.
Try it now
Common mistakes
- Trusting a formula you haven't tested. Gemini's formula can reference the wrong column or quietly miss the blank cells. Paste it, then check one row by hand before you build a report on it.
- Letting Gemini "clean" data unsupervised. Ask it to list problems, not silently fix them. A misread typo turned into an auto-correction can merge two regions or delete a sale without you noticing.
- Believing a cross-file answer with no source. If a Drive answer doesn't say which file a fact came from, you can't check it, and unsourced points are exactly where the model tends to fill gaps. Always ask for the source file.
- Over-trusting a confident number. A total stated to the penny feels authoritative, but it can be built on the wrong rows or a formula that skipped blanks. Fluency and precision are not accuracy, so spot-check anything that will be reported or acted on.
- Assuming the panel is available everywhere. Sheets and Drive Gemini features depend on account type and plan and are still rolling out. A missing panel means "not included", not "broken".
Keeping current
The Sheets and Drive helpers are among the fastest-changing parts of Gemini, and features, names and availability shift regularly. Fill with Gemini and the AI function in Sheets began broad rollout in July 2026 and are still reaching accounts, so what you see may differ from a colleague's. Trust your screen over this lesson, and check Google's official pages: Collaborate with Gemini in Google Sheets and Use Gemini in Drive for research and analysis. Accurate as of 14 July 2026.