A Practical Gemini in Google Sheets Course for Busy Consultants and Analysts
Table of Contents
Part I — Getting Oriented: What Gemini Can Do in Sheets
Lesson 1. What Gemini in Google Sheets Is — and Is Not
Understand Gemini as an assistant for spreadsheet creation, analysis, formulas, cleanup, charts, and workflow acceleration
Lesson 2. The Three Main Ways to Use Gemini in Sheets
Compare the Gemini side panel, formula generation, and AI functions such as =AI() / =Gemini().
Lesson 3. Setting Up Your Workbook for Better AI Results
Learn how to name tabs, clean headers, freeze rows, format tables, and structure data so Gemini can interpret the file.
Lesson 4. The Consultant Prompting Formula
Use a reusable prompt pattern: role, objective, data range, desired output, assumptions, and quality checks.
Lesson 5. Scoping Gemini to the Right Sheet, Tab, or Range
Practice focusing Gemini on the correct data instead of letting it infer too broadly.
Lesson 6. Asking Gemini to Explain an Unfamiliar Workbook
Use Gemini to summarize tabs, identify key columns, describe business logic, and flag data-quality concerns.
Part II — Building Useful Business Spreadsheets from Scratch
Lesson 7. Create a Project Workplan from a Natural-Language Brief
Build a consulting-style workplan with workstreams, owners, dates, dependencies, and status fields.
Lesson 8. Create a Market Research Tracker
Generate a tracker for companies, competitors, market segments, sources, notes, and confidence ratings.
Lesson 9. Create a Stakeholder Interview Tracker
Build an interview tracker with interviewee details, topic coverage, follow-ups, and synthesis tags.
Lesson 10. Create a Cost-Reduction Initiative Tracker
Generate a tracker for savings ideas, estimated impact, effort, owner, status, and next step.
Lesson 11. Add Dropdowns, Checkboxes, and Status Fields with Gemini
Use Gemini to add priority, status, owner, risk level, and approval fields.
Lesson 12. Improve a Poorly Designed Spreadsheet
Ask Gemini to recommend better headers, calculated columns, formats, and layout.
Part III — Data Cleaning, Classification, and Enrichment
Lesson 13. Standardize Messy Customer or Supplier Names
Clean inconsistent names such as “IBM,” “I.B.M.,” and “International Business Machines.”
Lesson 14. Split and Normalize Contact Fields
Extract first name, last name, company, email domain, country, and job title from messy data.
Lesson 15. Categorize Free-Text Comments by Theme
Classify employee or customer comments into themes such as workload, compensation, product quality, or service issues.
Lesson 16. Perform Sentiment Classification on Survey Responses
Use AI functions to classify comments as positive, neutral, negative, or mixed.
Lesson 17. Extract Key Facts from Unstructured Notes
Turn call notes into structured fields: issue, stakeholder, decision, action item, owner, and date.
Lesson 18. Create a Product or Spend Taxonomy
Classify SKUs or purchase-order lines into categories and subcategories.
Lesson 19. Identify Missing Data, Duplicates, and Outliers
Ask Gemini to inspect a dataset and create a data-quality issue log.
Lesson 20. Create a Repeatable AI Column
Use an AI column or AI function to classify many rows using the same prompt.
Part IV — Formula Generation and Troubleshooting
Lesson 21. Ask Gemini for the Right Formula from a Business Question
Translate plain-English business questions into Sheets formulas.
Lesson 22. Build Lookup Formulas for Client, Product, or Supplier Data
Generate and explain formulas using VLOOKUP, INDEX/MATCH, or equivalent approaches.
Lesson 23. Use Conditional Logic for Business Rules
Create IF, IFS, and SWITCH formulas for segmenting accounts, flagging risks, or assigning priority levels.
Lesson 24. Build Segment-Level Metrics with SUMIFS, COUNTIFS, and AVERAGEIFS
Calculate revenue, spend, margin, volume, or headcount by segment.
Lesson 25. Create Date-Based Formulas
Build fiscal quarter, month, week, tenure, aging, and cohort fields.
Lesson 26. Generate Text Formulas for Cleanup and Labeling
Combine fields, extract domains, standardize labels, and create readable IDs.
Lesson 27. Use Dynamic Array Formulas
Ask Gemini to create formulas using FILTER, UNIQUE, SORT, and related functions.
Lesson 28. Use the QUERY Function for Consultant-Style Analysis
Have Gemini write SQL-like queries to group, filter, and summarize data.
Lesson 29. Debug a Broken Formula
Paste a formula error into Gemini and ask for diagnosis, correction, and explanation.
Lesson 30. Ask Gemini to Explain a Formula in Plain English
Use Gemini to document complex formulas for a client-ready workbook.
Part V — Business Analysis Patterns
Lesson 31. Generate a First-Pass Data Profile
Summarize rows, columns, missing values, ranges, trends, and possible analyses.
Lesson 32. Create an Issue Tree from Spreadsheet Data
Use Gemini to convert raw metrics into a structured hypothesis tree.
Lesson 33. Analyze Revenue Growth Drivers
Decompose revenue by price, volume, mix, geography, customer segment, or product category.
Lesson 34. Analyze Margin Variance
Use a P&L dataset to identify drivers of gross margin, contribution margin, or EBITDA change.
Lesson 35. Analyze Store or Location Performance
Rank locations by sales, labor productivity, conversion, NPS, and margin.
Lesson 36. Analyze Procurement Spend
Classify spend, identify supplier concentration, compare unit prices, and find savings opportunities.
Lesson 37. Analyze Supply Chain Service Levels
Review fill rate, stockouts, lead time, inventory turns, and shipment performance.
Lesson 38. Analyze Sales Pipeline Health
Assess stage conversion, pipeline aging, win rate, forecast quality, and coverage ratio.
Lesson 39. Analyze Marketing Campaign ROI
Compare campaign spend, leads, conversion, CAC, pipeline, and revenue impact.
Lesson 40. Analyze Organization Survey Results
Combine quantitative survey scores with AI-classified comment themes.
Part VI — Pivots, Charts, Dashboards, and Storytelling
Lesson 41. Ask Gemini to Create a Pivot Table
Generate a pivot table for sales, spend, headcount, margin, or customer analysis.
Lesson 42. Choose the Right Chart for the Business Question
Ask whether to use a line chart, bar chart, scatter plot, waterfall, heatmap, or table.
Lesson 43. Generate and Refine Charts with Prompts
Create charts, then refine titles, axes, filters, and series.
Lesson 44. Build a Simple Executive Dashboard
Create a one-page dashboard with KPIs, trends, variance, top issues, and action items.
Lesson 45. Use Conditional Formatting to Focus Attention
Highlight risks, outliers, top performers, overdue items, and variance thresholds.
Lesson 46. Create Insight Headlines from Data
Turn analysis into consulting-style headlines such as “Revenue growth is concentrated in three regions.”
Lesson 47. Create a Client-Ready Summary Tab
Generate a concise executive summary tab with findings, implications, and recommended next steps.
Part VII — External Data, Web Lookup, and Business Enrichment
Lesson 48. When to Use Gemini vs. GOOGLEFINANCE vs. an API
Learn the decision rule: Gemini for research and enrichment, deterministic functions for repeatable data feeds, APIs for production-grade workflows.
Lesson 49. Pull Current Market Data with GOOGLEFINANCE
Use GOOGLEFINANCE to retrieve current stock price, market cap, P/E, 52-week high/low, volume, and related fields.
Lesson 50. Pull Historical Stock Prices
Create historical price series for public companies and calculate returns over selected periods.
Lesson 51. Pull Current and Historical Exchange Rates
Use Sheets formulas to retrieve current and historical FX rates for currency conversion analysis.
Lesson 52. Normalize Financial Data Across Currencies
Convert revenue, cost, EBITDA, and market data into a common currency using dated FX rates.
Lesson 53. Enrich a Company List with Gemini
Starting with a company name only, use Gemini to add company description, industry, headquarters, website, ownership type, and likely business model.
Lesson 54. Create a Company Research Table for Market Mapping
Enrich a list of competitors or acquisition targets with segment, geography, customer type, product category, and notes.
Lesson 55. Pull Current Market Capitalization for a Company List
Compare when to use ticker-based GOOGLEFINANCE, Gemini lookup, or a third-party financial-data provider.
Lesson 56. Identify a Company’s Likely Ticker Symbol
Use Gemini to infer the likely public-company ticker from a company name, then validate it manually before using GOOGLEFINANCE.
Lesson 57. Enrich Person Data from Name, Title, and Company
Use Gemini to generate a probable bio, public profile summary, and likely LinkedIn URL when the person can be identified from public information.
Lesson 58. Build a Prospecting Sheet for Sales or Business Development
Use company and person enrichment to create a target-account list with account description, buyer persona, possible needs, and outreach angle.
Lesson 59. Pull Weather or Location Data for Business Analysis
Use Gemini or structured APIs to enrich rows with weather, city, country, region, population, or location context.
Lesson 60. Use IMPORTDATA, IMPORTHTML, IMPORTXML, and IMPORTFEED
Bring structured web data into Sheets and understand when import functions are too fragile for serious analysis.
Lesson 61. Create a Source, Timestamp, and Confidence Column
For every AI-enriched field, capture source, lookup date, confidence level, and “needs human verification” status.
Lesson 62. Design a Human Review Workflow for AI-Enriched Data
Create a QA process for sampling AI outputs, validating web-sourced facts, and separating “draft research” from “client-ready fact base.”
Part VIII — Advanced Consultant Use Cases
Lesson 63. Build a Scenario Analysis Model
Use Gemini to create base, upside, and downside scenarios for revenue, margin, hiring, or demand.
Lesson 64. Use Gemini for Optimization Problems
Ask Gemini to solve constrained business problems, such as allocating budget across campaigns or assigning staff to projects.
Lesson 65. Generate a Workplan from Analytical Findings
Ask Gemini to convert spreadsheet findings into a project plan with owners, milestones, and next steps.
Lesson 66. Create a Client-Ready Issue Log
Turn risks, gaps, and open questions into a structured issue log with priority and recommended action.
Lesson 67. Create a Board-Ready Metrics Pack
Use Gemini to help assemble KPIs, variance explanations, charts, and short commentary.
Lesson 68. Create a Quality-Control Checklist for AI-Assisted Analysis
Build a repeatable review process: check formulas, validate outputs, sample rows, compare totals, document assumptions, and identify AI-generated fields.
