Gemini in Google Sheets for Small Businesses: 10 Practical Uses

Open a sheet, click Ask Gemini at the top right and describe what you want. Gemini can build trackers, write and explain formulas, create pivot tables and charts, add warning colours and pull in data from Drive files and emails; the =AI() function fills a column row by row. You need a Workspace plan that includes it, typically Business Standard or above.

Two behaviours catch people out. Charts Gemini creates don't update when the data changes, and =AI() results don't refresh on their own either. And the AI function sees only the cells you point it at, generating up to 350 at a time, so it suits tidy row-by-row jobs; questions about the whole sheet belong in the side panel. Plan around those limits and the ten uses below save real hours.

Follow me on Instagram@sagnikteaches

Before the Ask Gemini button appears

Gemini across Sheets comes with Business Standard (about $14 a user a month on an annual plan) and higher plans; Business Starter gets Gemini in Gmail and the Gemini app rather than the full Sheets side panel. The detail for each plan is in what Gemini in Google Workspace includes on each business plan. If you're on an eligible plan and still don't see the button, ask whoever manages your Workspace account whether Gemini has been enabled for your user.

Connect on LinkedInSagnik Bhattacharya

Three habits make every use below safer. Work on a copy of any sheet that matters until you trust the result. Keep data in a tidy block with one header row. And check sharing before you start, because Google warns that content Gemini adds may be visible to people outside your company if the file is shared with them.

Subscribe on YouTube@codingliquids

Building and fixing sheets

1. Build a tracker from a plain description

Since the build-and-edit rollout that began in April 2026, you can describe a whole spreadsheet and Gemini proposes a plan and an outline before creating it. An illustrative courier firm with 12 vans had vehicle checks on paper and service dates in someone's head. The prompt:

Build a vehicle tracker for 12 vans. One tab listing each van: registration,
make/model, next service date, next inspection date, mileage, assigned driver.
A second tab for daily walk-round checks: date, registration (dropdown from
tab 1), driver, tyres/lights/brakes OK (checkboxes), defect notes.
Highlight any service or inspection date within 21 days in amber, past in red.

Gemini replies with a plan: two tabs, the column list, dropdown and checkbox settings, and the formatting rules. You review it, adjust anything ("add a 'defect fixed on' date column"), then let it build. The result is a working skeleton in minutes rather than an hour of fiddling with data validation menus. Watch-out: check that dropdowns point at the right range. In an illustrative first build, the registration dropdown referenced rows 2 to 12, so the twelfth van was missing. Fill in the real data yourself; don't ask Gemini to invent sample rows you'll forget to delete.

The same feature edits sheets you already have. Once the vans were loaded, the office manager asked: "Add a summary block above the van list showing vans due a service in the next 30 days, open defects from the checks tab, and a bar chart of defects by van." Gemini proposed the formulas (a COUNTIFS on dates, a FILTER for open defects) before inserting anything. Read that plan properly; it's the moment to catch a wrong assumption, such as counting every defect ever logged rather than only the unfixed ones.

2. Write, explain and fix formulas

Describe the result you want in words and Gemini writes the formula; paste a formula you've inherited and it explains it line by line. An illustrative wholesaler wanted margin per line and the right price-break for each order quantity:

In column H, calculate margin % as (sell price in F minus cost in G)
divided by F. In column I, look up the unit price for the quantity in D
from the Price Breaks tab, where column A is the minimum quantity and
column B the price, choosing the highest break not above the quantity.

An illustrative answer: =IFERROR((F2-G2)/F2,"") for margin, and =XLOOKUP(D2,'Price Breaks'!A:A,'Price Breaks'!B:B,,-1) for the price break, with a note that the -1 match mode finds the next smaller quantity. That's correct, and the explanation is what makes it maintainable. Watch-out: test formulas on awkward rows, such as a quantity exactly on a break, a quantity below the smallest break, and a blank row. In an illustrative test, a quantity of 5 below the first break of 10 returned a blank rather than the list price; the fix was a default value in the lookup, which Gemini added when asked. For a bank of tested prompts, see our Google Sheets AI prompts.

Explaining inherited formulas is just as valuable. Paste something like =IF(E2="",,IF(E2>500,F2*0.9,IF(E2>100,F2*0.95,F2))) and ask "explain this in plain words and suggest a simpler version". An illustrative reply: "If the quantity in E is blank, show nothing. Over 500 units, apply a 10% discount to the price in F; over 100, 5%; otherwise the full price." It then offered an IFS version that's easier to edit. The explanation revealed something the owner had forgotten: exactly 500 units got only 5% off, which a customer had queried twice. That's the kind of fix people don't make because nobody understands the formula any more.

Summarising and showing the numbers

3. Pivot tables for sales by customer and month

Pivot tables are the most useful spreadsheet feature many owners never use. Gemini creates them from a sentence, in a new sheet. An illustrative packaging supplier's invoice-line export:

Create a pivot table of net value by customer (rows) and month (columns)
for 2026, with a total column, sorted by total descending.

In seconds there's a new tab showing, say, 48 customers across nine months with totals. Then ask a follow-up in the side panel: "Which five customers' monthly totals fell most between the first quarter and the third?" An illustrative reply names the five with both quarters' figures and the percentage change, which you can check against the pivot in a minute because the numbers are right there. Gemini can also analyse across several tables, a capability Google added in October 2025, so a separate customer-group table can be brought in. Watch-out: check the grand total against your accounts. If the export has subtotal rows or credit notes stored as positive numbers, the pivot will be wrong in a perfectly formatted way. The file-preparation checklist in what to upload and check when AI analyses a sales spreadsheet applies here too.

4. Charts for a weekly report

An illustrative testing laboratory tracks each sample's received date, reported date and the turnaround it promised the client. The manager wanted a chart for the Monday meeting:

Add a column for actual turnaround in working days (reported minus received,
excluding weekends). Then chart the weekly average actual turnaround against
the average promised turnaround for the last 12 weeks, as two lines.

Gemini adds the column (using NETWORKDAYS), builds the summary and puts the chart on a new tab with its underlying data. The chart makes the point immediately: turnaround drifted above promise in the three weeks a technician was on leave. Watch-out: Google's help page is explicit that generated charts don't update automatically when the source data changes. For a weekly report, either regenerate each week or, better, point a normal chart at the summary table so it updates itself. Also check NETWORKDAYS against a couple of samples received on a Friday, because a missing holidays range makes turnaround look shorter than clients experienced.

5. Conditional formatting and dropdowns that flag problems

Formatting rules turn a list into a warning system. An illustrative import-export business keeps a shipment tracker with order number, supplier, ETA, status and customer due date:

Add a Status dropdown with: Ordered, Shipped, At port, Cleared, Delivered.
Colour a row red if the ETA is after the customer due date and status isn't
Delivered. Colour it amber if the ETA is within 5 days of the due date.
Freeze the header row and sort by customer due date.

Gemini applies the rules and explains each one, so you can edit them later through Format, then Conditional formatting. The owner's morning check shrinks to looking for red. Watch-out: rules based on text break when someone types "shipped" instead of choosing "Shipped" from the dropdown, so make the dropdown reject other input. And check the date logic on one red and one amber row by hand; "within 5 days" is easy to implement as 5 days either side when you meant before.

A laboratory can use the same approach for instrument calibration: a list of instruments with last calibration date and interval, a formula for the next due date, amber at 14 days, red when overdue, and a checkbox for "calibration certificate filed". Five minutes of prompting replaces the wall calendar that nobody updates.

Working with text inside cells

6. The =AI() function for categorising free text

The AI function runs a prompt on each row. Its syntax is AI("prompt", [range]), and =Gemini() works the same way, per Google's help page for the AI function. An illustrative courier firm exports 600 failed-delivery notes a month, typed by drivers on a handheld: "no answer, card left", "gate locked code wrong", "customer refused, damaged box", "address not found, rang no reply".

=AI("Classify this delivery note into exactly one of: No one home, Access
problem, Address problem, Refused - damaged, Refused - other, Other.
Reply with the category only.", C2)

Fill it down, select the cells and click Generate and insert. The output is a clean category column you can pivot: an illustrative month showed access problems at 22% of failures, concentrated on three business parks with gate codes that had changed. Watch-outs: only the first 350 selected cells generate at once, so work in batches; there are daily limits beyond that; and the function can't be nested inside other formulas. Results don't refresh when the note changes, so once they're right, copy and paste them as values.

The quick sum: reading and tagging 600 notes by hand at about 20 seconds each is over three hours a month, usually never done at all. Generating the column in two batches and reading the "Other" rows took the office about 20 minutes. Check a random 30 rows the first time; if more than two or three are wrong, tighten the category definitions in the prompt, for example "Access problem means gates, codes, locked buildings or no parking".

7. The =AI() function for drafting short text per row

The same function writes short text from a row's details. An illustrative spare-parts manufacturer needed one-line catalogue descriptions for 300 parts from its specification columns (part type, material, dimensions, compatible machine):

=AI("Write a catalogue description under 25 words from these specs. Plain,
factual, no marketing adjectives. Include material and key dimension.", B2:E2)

A typical result: "Stainless steel drive shaft, 25 mm diameter, 310 mm long, for 400-series carton erectors." That's usable as it stands. What to fix across the batch: occasional invented details, such as "corrosion-resistant" added to a mild-steel part, or a compatibility the specs didn't state. Sort by part type and read each group; errors cluster. The function can also use real-time information from Google Search, which is useful for some jobs but risky for catalogue data, so tell it to use only the given cells. At 350 cells per run, 300 descriptions fit in one generation.

Another use of the same pattern: a wholesaler's account manager drafting the first line of a check-in email per lapsed customer from their last order date and usual products ("It's been a couple of months since your last order of 40mm clamps; are you still stocked up for the season?"). Keep this to drafts a person edits and sends. A column of machine-written emails going straight to customers is how businesses end up apologising for the same odd phrase 80 times.

8. Enhanced Smart Fill for cleaning messy columns

Smart Fill watches what you type and offers to fill the rest of the column. The Gemini-enhanced version handles fuzzier jobs, like extracting phone numbers from a notes field, putting addresses into a consistent format or tagging feedback by theme. It needs at least three example rows and works only on text columns, not numbers or dates.

An illustrative import-export business had a 900-row contact list with phone numbers buried mid-sentence in a notes column, among call-time preferences and reminders. Typing the clean number for three rows in a new column prompted Smart Fill to suggest the rest; accepting took one click. Watch-outs: Google's help page says enhanced Smart Fill detects and supports English values only, so a mixed-language list may get patchy suggestions. Scroll through the suggestions before accepting, looking for rows where two numbers appeared in the notes and it picked the wrong one.

The same trick standardises formats. Three examples of a supplier column typed three ways ("ACME LTD", "Acme Ltd.", "acme limited") all rewritten as "Acme Ltd" are enough for Smart Fill to suggest the same clean-up down the column, which makes the supplier pivot in use 3 finally add up. It's also a good first job for someone new to Gemini, because the before and after sit side by side and mistakes are obvious.

Pulling in other files, and planning

9. Summarise Drive files and emails into a table

In the side panel you can refer to Drive files and Gmail messages with @, and ask Gemini to summarise them into the sheet. An illustrative wholesaler asked four suppliers for prices on the same 20 lines and received two PDFs, a spreadsheet and an email:

Using @Supplier A quote.pdf, @Supplier B prices.xlsx, @Supplier C quote.pdf
and the email from Supplier D on 18 Sept, build a table: product, unit price
per supplier, pack size, minimum order, lead time, payment terms. Leave a
cell blank and mark it "not stated" if a supplier didn't give a value.

Gemini builds the comparison in the sheet. The "not stated" instruction matters: without it, models fill gaps with plausible values. An illustrative row of the result: "Hose clamp 40mm stainless | A: 0.42 | B: 0.39 (box of 100) | C: not stated | D: 0.45 | lead time A 10 days, B 21 days". Notice that supplier B quoted per box, which Gemini recorded because the prompt asked for pack size. Spot-check five prices against the source documents before deciding anything, and watch pack sizes, since a price per box of 100 against a price per unit is the most common comparison error. Comparing supplier quotes side by side with AI covers the full method, including delivery terms.

10. Solve a scheduling or allocation problem

Gemini in Sheets includes an optimisation solver that Google says handles operations and logistics tasks such as allocating budgets and scheduling staff. You describe the goal and the rules in the side panel. An illustrative laboratory with five technicians and three instruments needed a weekly allocation:

Tab "Staff" lists technicians, the instruments each is trained on and days
available. Tab "Demand" lists samples per instrument per day. Assign
technicians to instruments for each day of next week so every instrument's
demand is covered, no one works more than 4 days, and trainees are never
alone on the ICP instrument. Show the schedule and any demand not covered.

The result is a grid of assignments plus a note of any shortfall, such as "Thursday: ICP demand exceeds trained capacity by 20 samples". That shortfall line is often the most valuable output, because it tells you before the week starts. Watch-out: the solver only knows the rules you write. The first illustrative schedule put the same technician on the early instrument start four days running, which was legal under the stated rules but unpopular; adding "no more than two early starts per person" fixed it. Treat the output as a draft rota that the manager approves.

Allocation problems that aren't rotas work too. A courier firm with 14 vans of two sizes and 30 regular contract collections can ask for an assignment that keeps each van under its payload, keeps the large van for the two furniture clients, and balances stops across drivers. It won't replace route-planning software for the drops themselves, but for the weekly "who takes which contract" puzzle it's a fast first draft.

Side panel prompts that get better results

The same request phrased two ways gets very different results. Before: "Can you look at my data and make it better?" Gemini guesses: it might sort, reformat or summarise, and you can't predict which. After: "On the tab 'Orders', using A1:H640, add a column I with days between order date (B) and dispatch date (F). Then tell me the average by month. Don't change any existing columns." That version names the tab, the range, the output location and a boundary. Four habits help every time:

• Name the tab and range, especially when a workbook has several tables.
• Say where the result should go: a new column, a new tab or just the side panel.
• Set a boundary: "don't change existing data" prevents surprise edits.
• Ask for the check: "and show me three example rows so I can verify the calculation".

A month with Gemini in Sheets at one courier firm

To see how the uses add up, take an illustrative courier firm with 20 drivers and two office staff on Business Standard. In its first month it used four of the ten: the vehicle tracker (use 1), warning colours on the tracker and the invoice list (use 5), =AI() tagging of failed-delivery notes (use 6) and a monthly pivot of revenue by customer (use 3).

The time saved was roughly five hours a month, but the owner valued the failed-delivery tags most: adding the correct gate codes for three business parks to the job notes cut repeat attempts there to almost none. That's typical. The biggest wins from AI in spreadsheets often come from analyses nobody had time to do, not from doing existing work faster. The main cost was the Business Standard seats the firm already needed for email and Docs.

Where Gemini in Sheets falls short

• Nothing updates itself. Generated charts and =AI() results stay as they were when created. Rebuild or refresh deliberately.
• Side panel conversations don't persist. Reloading the page loses the chat, so paste prompts you'll reuse into a notes tab.
• Big, messy sheets confuse it. Merged cells, several tables on one tab and subtotal rows cause wrong answers. Tidy first.
• It can be confidently wrong. Always check totals and a few rows by hand before sharing results.
• Some features don't work with third-party storage integrations, such as files stored through Box or Dropbox connectors, according to Google's help pages.

If a tracker has grown to thousands of rows with several people editing and linked records between tabs, a database-style tool may serve you better; Airtable versus Google Sheets for business data AI can use compares the two. And before switching Gemini on across a team that handles sensitive files, read whether Gemini is safe for confidential business data in Workspace.

Which three to try first

If you're new to it, start with uses 2, 3 and 5: formulas, pivot tables and formatting rules. They work on sheets you already have, they're easy to check, and the results keep working after Gemini is closed. Once those feel routine, try the =AI() function on one column of free text (use 6), because categorised text is where Sheets and AI together do something neither did well before. Save the prompts that worked in a tab called "Prompts" in each sheet, so the next person doesn't start from scratch.

To check it's working after a month, look for three things: whether the sheets Gemini built are still being filled in (a tracker nobody updates saved nothing), whether any figure that left the building came from a generated chart or =AI() column that was never refreshed, and whether the time you expected to save shows up in who is doing what. If a use failed on any of those, drop it or fix the habit around it rather than adding the next one.

Gemini in Sheets: questions from small teams

Which Google Workspace plan includes Gemini in Sheets?

Gemini across Gmail, Docs, Sheets and the other apps comes with Business Standard and above; Business Starter includes Gemini in Gmail and the Gemini app but not the full Sheets experience. Business Standard is about $14 a user a month on an annual plan. Plans and limits change, so check the Workspace pricing page for your account before relying on a feature.

Does Gemini in Sheets work on Excel files?

It works best on native Google Sheets. If you open an Excel file in Drive, go to File and choose Save as Google Sheets to make a converted copy, then use Gemini on that copy. Keep the original Excel file if others still need it, and remember the two versions won't stay in sync after conversion.

Can I undo what the =AI() function writes?

Not with undo: Google's help page says you can't undo or redo the AI function. Before generating a large batch, duplicate the sheet or work in a new column, so you can delete the output if it's wrong. For anything you'll keep, copy the results and paste them as values, so a later refresh doesn't change them unexpectedly.

Is Gemini in Sheets safe for customer data?

Gemini on Workspace business plans doesn't train on your business content by default, and it can only reach files the person using it already has access to. The practical risks are the usual ones: sheets shared too widely, and Gemini-added content being visible to anyone the file is shared with, including people outside your company. Tidy sharing settings before switching Gemini on for everyone.

Further reads

• How to Use Gemini in Gmail to Clear Your Inbox Faster — The same assistant in your inbox, where many sheet rows start.
• How to Use Gemini in Google Docs to Draft and Edit Faster — Turn a Sheets summary into a written report in Docs.
• How to Build Gemini Gems for Repeat Business Tasks — Save repeat Sheets instructions as a Gem for the team.
• Gemini or ChatGPT for a Google Workspace Business? — Whether Gemini alone covers a Workspace business.
• How to Build a KPI Dashboard With AI When You Have No Data Team — Grow uses 3 and 4 into a proper KPI dashboard.
• Outgrowing Spreadsheets: When to Replace Manual Excel With AI — Signs your trackers need a proper system instead.
• How Bakeries Can Use AI to Predict Demand and Cut Unsold Stock — A five-stage method for forecasting each product by weekday, correcting for sell-outs, and setting bake quantities that match each item's margin.
• How Event Planners Use AI to Build Budgets and Run Sheets — Turn an event brief into a line-item budget and a timed run sheet with AI, while supplier quotes and spreadsheet formulas keep the numbers honest.
• How Tutors Can Plan a Week of Lessons With AI in an Hour — A timed one-hour routine: a student roster, one batch prompt for every lesson skeleton, materials only where needed, and a check on every answer key.
• How Driving Schools Use AI to Keep Instructor Diaries Full — Fill cancelled slots in an hour, replace pupils before they pass, cut dead travel between lessons and spot quiet weeks early, with the data each step needs.
• How to Get a Weekly Business Summary Emailed to You by AI — Get a Monday email with your five key numbers and a short AI commentary. Covers the numbers tab, Zapier and Make builds, assistant scheduled tasks and checks.
• Best AI Business Intelligence Tools for Small Businesses (2026) — Ten reporting and analytics tools compared on what their AI really does, list prices, the tier each AI feature needs and which small businesses each suits.
• Copilot in Excel: What It Can and Can't Do With Your Numbers — The Copilot pane's three modes tested on a pet shop's sales file, the margin formula it got wrong, and what to do with old COPILOT() cells.
• How to Use Claude With Excel and Google Sheets Safely — The three ways Claude reaches a spreadsheet, what each sends, the prompt-injection warning in Anthropic's own docs, and a formula check routine.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Google Docs Editors Help pages 'Collaborate with Gemini in Google Sheets', 'Use the AI function in Google Sheets', 'Use enhanced Smart Fill with Gemini' and 'Build or edit entire spreadsheets with Gemini in Sheets'; Google Workspace Updates posts (April 2026 build and edit rollout, multi-table analysis); facts sheet for Workspace plan prices.