How to Use Google Gemini for Excel Formulas and Data

Most Excel users I train have heard of ChatGPT and Microsoft Copilot, but when I bring up Google Gemini for spreadsheet work in my workshops, I usually get a blank stare. That surprises me every time — because Gemini has consistently produced some of the clearest, most usable formula outputs I've seen from any AI tool, and it's free to start with.

Gemini, Google's flagship AI model, is genuinely excellent at understanding Excel problems described in plain English. In this guide I'll walk you through exactly how to use it — from simple formula generation to multi-condition data analysis queries — with real prompts and outputs you can adapt immediately in your own spreadsheets.

Why Gemini Is Worth Your Attention for Excel Work

Gemini's core strength is contextual reasoning. Unlike a search engine that returns generic results, Gemini understands the relationship between your columns, your intent, and the formula structure needed to achieve your goal. It also gets noticeably better the more context you give it — which makes it a natural fit for the kind of messy, real-world spreadsheet problems that generic tutorials never quite address.

Here's what makes Gemini particularly effective for Excel specifically:

• Deep language understanding — You can describe what you want in plain business terms without knowing the function name or even the general approach
• Strong multi-step reasoning — Complex nested formulas combining IF, SUMIFS, INDEX-MATCH, and text functions are handled confidently
• Accurate explanations — Gemini doesn't just give you the formula; it breaks down each component so you understand what you're deploying, not just copying
• Effective iteration — Paste in a broken formula, describe the error, and Gemini diagnoses and corrects it efficiently in the same conversation
• Version awareness — Tell it you're on Excel 2016 and it won't recommend XLOOKUP or dynamic array functions that aren't available in that version

After running Excel workshops across industries, I've found Gemini particularly strong at text manipulation formulas and date calculations — the two areas where clients most frequently get stuck and most frequently turn to me for help.

How to Access Gemini for Excel Work

You don't need a Google Workspace subscription to use Gemini for Excel. Here are your main options:

1. Gemini web app — Free tier available with access to capable models, and more powerful models available on paid plans. This is the right starting point for most users.
2. Gemini Advanced — Google's most capable models, better suited to long, complex, multi-step spreadsheet problems where you're building something substantial.
3. Google Workspace Gemini — If your organisation uses Google Workspace, Gemini is integrated directly into the suite. However, this integration is primarily optimised for Google Sheets. For Excel files specifically, the standalone web app remains your best route regardless of your Workspace tier.

Unlike Microsoft Copilot, which requires a Microsoft 365 subscription and operates inside Excel itself (I cover that workflow in detail in my guide to getting started with Microsoft Copilot in Excel), Gemini works as an external assistant. You describe your problem in the browser, get your formula, paste it into Excel. One extra step — but zero subscription cost on the free tier, and no dependency on your organisation's Microsoft licensing.

My practical recommendation: Bookmark the Gemini web app and keep it open in a browser tab while you work in Excel. Once you build the habit of describing your formula problems instead of Googling them, the time savings compound quickly.

Writing Excel Formulas with Gemini — Real Examples

The single most effective habit you can build is describing your spreadsheet layout before asking for a formula. Here's the prompt structure I share in every workshop — it works reliably across every type of formula request:

"I have an Excel spreadsheet. Column A has [data type], Column B has [data type], Column C has [data type]. I need a formula in Column D that [exact desired output]. I'm using [Excel version]."

Let me show you how this plays out with real examples.

Example 1: SUMIFS with Multiple Conditions

Prompt: "Column A has dates, Column B has region (North/South/East/West), Column C has sales amounts. I need a formula that sums sales for the North region in January 2026. Using Microsoft 365."

=SUMIFS(C:C, B:B, "North", A:A, ">="&DATE(2026,1,1), A:A, "<="&DATE(2026,1,31))

Gemini explains that SUMIFS applies all criteria simultaneously and evaluates efficiently on large data sets. It will typically follow up by suggesting you replace the hard-coded dates with cell references — which instantly turns a one-off formula into a reusable reporting tool.

Example 2: Extracting Text Between Delimiters

Prompt: "Column A has email addresses like 'john.smith@company.com'. I need Column B to show only the domain name — everything after the @ symbol."

=MID(A2, FIND("@", A2) + 1, LEN(A2) - FIND("@", A2))

For Microsoft 365 users, Gemini also offers the cleaner TEXTAFTER function:

=TEXTAFTER(A2, "@")

Critically, Gemini tells you which Excel versions support each approach. This is exactly the kind of detail that prevents a #NAME? error when you share the file with a colleague on Excel 2016.

Example 3: Running Total That Resets Each Month

Prompt: "Column A has dates, Column B has daily sales figures. I need Column C to show a cumulative running total that resets to zero at the start of each new calendar month."

=SUMIFS(B$2:B2, A$2:A2, ">="&DATE(YEAR(A2),MONTH(A2),1), A$2:A2, "<="&DATE(YEAR(A2),MONTH(A2)+1,0))

This is a formula most intermediate Excel users would spend 30–45 minutes building and testing from scratch. Gemini produces it in seconds, with a clear walkthrough of how the expanding range reference B$2:B2 and the DATE boundary logic interact. For more formula patterns like this, see my post on 15 advanced Excel formulas every professional should know.

Example 4: Nested IF Classification Logic

Prompt: "Column A has numeric scores out of 100. I want Column B to show 'Excellent' if the score is above 90, 'Good' if above 75, 'Average' if above 60, and 'Needs Work' for anything lower."

=IF(A2>90, "Excellent", IF(A2>75, "Good", IF(A2>60, "Average", "Needs Work")))

Gemini will also offer the IFS function as a cleaner alternative for Microsoft 365, which is significantly easier to read and extend when you need to add more tiers later:

=IFS(A2>90, "Excellent", A2>75, "Good", A2>60, "Average", TRUE, "Needs Work")

Using Gemini for Data Analysis Questions

Beyond formula generation, Gemini is excellent at answering strategic Excel questions — the kind you'd previously have had to search Stack Overflow or ask a senior colleague for. These are often the questions that don't have a clean Google answer because they depend on your specific data structure and goals.

Analysis prompts that consistently produce useful responses:

• "I have 10,000 rows of sales data with date, product, region, and amount columns. What's the most efficient way to find the top 5 products by revenue in each region?" — Gemini typically recommends a Pivot Table with a Top 10 filter (adjusted to 5), explains why that's more maintainable than a formula-based approach at that data volume, and notes when a SUMIFS-into-helper-table approach is preferable for dynamic dashboards.
• "My XLOOKUP is returning the wrong value when there are duplicate entries in the lookup column. Why is this happening and how do I fix it?" — Gemini accurately identifies XLOOKUP's default first-match behaviour, explains that this is by design, and suggests whether you need deduplication upstream or a different formula strategy depending on your actual requirement.
• "I need to consolidate data from 12 monthly sheets into one summary sheet automatically. What are my options?" — Gemini gives you a ranked list: Power Query (best for automated refresh on schedule), 3D cell references (simplest if structure is identical across sheets), INDIRECT with dynamic sheet names (flexible but volatile), and manual consolidation — with honest notes on complexity and maintenance overhead for each.

For building the full dashboard layer on top of your formula work, the techniques I cover in the dynamic dashboards in Excel guide pair naturally with the formula foundation you build using Gemini.

Asking Gemini to review your existing formulas is another high-value but underused workflow. Paste in a formula that isn't behaving as expected, describe what it should return, and Gemini will identify the issue — a mismatched bracket, a wrong range anchor, an incorrect argument order — faster than manual inspection almost every time.

Gemini vs Other AI Tools for Excel

I use all four major AI tools regularly in my own work and in my workshops, so I have a reasonably clear picture of where each one shines. Here's an honest comparison based on real formula tasks:

Tool	Best For	Access	Key Limitation
Google Gemini	Formula generation, text manipulation, multi-step data reasoning	Free tier available	External to Excel — copy-paste workflow
ChatGPT	Broad formula help, VBA scripts, explaining functions in plain terms	Free tier available	Occasional errors on complex nested logic
Claude AI	Complex nested formulas, detailed formula explanations, long context	Free tier available	External — copy-paste workflow
Microsoft Copilot	In-Excel automation, natural language queries on live data	Microsoft 365 required	Subscription cost; less flexible prompting outside Excel

My honest take after comparing them in live workshop settings: if you have a Microsoft 365 subscription and work primarily inside Excel all day, Copilot wins for in-context automation because it operates directly on your data without context-switching. If you're on a budget or want the strongest pure formula generation quality, Gemini and Claude are both excellent. Gemini tends to be marginally stronger on multi-condition data reasoning; Claude tends to be marginally stronger on explaining complex formula logic in depth. For a detailed look at ChatGPT's specific strengths for Excel work, see my dedicated ChatGPT Excel guide.

Prompting Techniques That Make Gemini Dramatically Better

This is where most of the real productivity gains live. After watching hundreds of workshop participants use Gemini live, I've seen clearly what separates people who get great results from people who get frustrated. It almost always comes down to prompting habits — not the tool itself.

1. Paste actual sample data

Instead of describing your layout abstractly, paste 3–5 rows directly into the prompt:

Here's a sample of my data:
Date | Region | Product | Amount
2026-01-05 | North | Laptop | 85000
2026-01-07 | South | Tablet | 42000
2026-01-09 | North | Monitor | 31000

I need a formula to sum Amount where Region is North and the date falls in January 2026.

Gemini maps your actual column structure and produces directly usable formulas with the right cell references instead of generic placeholders. This alone eliminates most of the manual adjustment work.

2. Always specify your Excel version

Include this in every prompt: "I'm using Excel 2019" or "I'm on Microsoft 365". Gemini uses this information to choose between XLOOKUP vs VLOOKUP, TEXTAFTER vs MID, LET vs repeated calculations, and dynamic arrays vs legacy Ctrl+Shift+Enter array formulas. Without it, you may receive a formula that looks correct but throws a #NAME? error in your actual file.

3. Request error handling upfront

Build robustness into your formula from the start by asking for it explicitly:

"Give me the formula, and also a version that returns an empty string instead of an error if the lookup value isn't found in the source data."

4. Ask for alternatives before committing

After receiving a working formula, ask: "Is there a simpler version?" or "Can you do this without using a helper column?" Gemini typically has two or three valid approaches and can explain the readability, performance, and compatibility trade-offs for each.

5. Iterate inside the same conversation

Never start a new chat session when a formula needs adjustment. Describe what's wrong in the same thread — Gemini retains the full context and adjusts precisely rather than producing a generic response. This is significantly faster than re-explaining your spreadsheet structure from scratch, especially for complex multi-sheet scenarios.

6. Ask what approach to use before asking for the formula

For genuinely complex tasks, try: "What Excel functions or overall approach would you recommend for [task]? Don't write the formula yet." Gemini's recommendation at this stage often reveals a simpler or more maintainable approach than the one you had in mind, saving you from over-engineering a formula that a Pivot Table or Power Query step would handle more cleanly.

Wrapping Up

Google Gemini is one of the most capable AI tools available for Excel work today, and it's genuinely underused by the broader Excel community. The Microsoft ecosystem naturally pulls users towards Copilot — which makes sense if you have the subscription — but Gemini is free to start with, requires no special integration, and produces formula quality that competes with any tool I've tested.

Start with your most time-consuming, most frequently recurring formula type — multi-condition lookups, date range calculations, text parsing, classification logic — and give Gemini a structured prompt using the pattern in this guide. The first time it delivers exactly the formula you needed in under 30 seconds, you'll understand why I've added it to every Excel workshop I run.

The real productivity shift comes from changing how you relate to formula problems. Instead of searching, guessing, and debugging in isolation, you describe the problem clearly to an AI that can reason about it precisely. That skill — knowing how to describe your spreadsheet problem well — is worth developing intentionally. Once you have it, every AI tool in this space gets dramatically more useful.

Related Posts

• How to Use ChatGPT for Excel Formulas and Analysis
• How to Use Claude AI to Write Excel Formulas Instantly
• Excel Copilot for Data Analysis — A Practical Guide