Use Gemini in Google Sheets for Scenario Tables and Survey Coding

What This Does

When a client works in Google Sheets instead of Excel, Gemini's "Ask Gemini" panel handles two recurring jobs on this kind of engagement: building the what-if scenario tables that clients ask for the moment they see a base case, and sorting open-text survey or interview responses into themes without a manual read-through of every row. Same panel, same plain-language prompting, two different problems solved.

Before You Start

• Access to {{tool:Google Sheets.plan}} through a Google Workspace account (yours or the client's), with Gemini enabled on the plan
• Your base-case model or raw response data already sitting in a Sheet, with clear column headers
• If the data includes client names, employee IDs, or other identifying details, confirm whether your firm's data policy allows pasting it into the AI panel before you start (see the caution below)

Steps

1. Find the AI feature

With your sheet open, click the Ask Gemini icon near the top right of the toolbar, or use the keyboard shortcut Ctrl+Alt+G (Cmd+Option+G on Mac). A prompt bar or side panel opens for you to type into.

2. Tell it what you need, one job at a time

For a scenario table, describe the variable to scale and the columns that should recalculate: "Add three columns that scale monthly demand by 10%, 20%, and 30%, and update the total cost formula in column F to recalculate for each." For survey coding, point at the specific column and the category list: "Read column D and assign each response to one of these five themes: staffing, scheduling, communication, workload, or other."

Ask for one kind of change per request. A single broad prompt covering both scenario math and text categorization is harder to check afterward than two focused ones.

3. Review and use the result

Gemini either applies the change directly or shows a suggestion to accept. For scenario tables, spot-check that the recalculated columns actually respond when you change the base input, since a static value that looks right but does not update defeats the purpose. For survey coding, scroll through a sample of the assigned categories and correct any response that landed in the wrong theme. Gemini is fast at pattern-matching typical phrasing, not at catching sarcasm or ambiguous wording, so plan for a review pass either way.

Real Example

Scenario A, scenario table: A client just saw your base-case network cost model and immediately asked "what if demand comes in 20% higher than forecast." You type: "Add columns showing total network cost at 100%, 115%, and 130% of baseline demand, with facility capacity held constant, and summarize the pattern in one sentence." Gemini returns three recalculated columns and a one-line summary you can paste straight into your findings.

Scenario B, survey coding: You collected 85 open-text responses from a stakeholder survey about a proposed scheduling change. You type: "Assign each response in column C to one of these themes: cost concern, staff pushback, technical feasibility, or no concern." Gemini codes all 85 rows in under a minute. You spend 15 minutes checking the ones it flagged as uncertain instead of two hours coding by hand.

Tips

• Strip client names and employee identifiers from survey data before it goes into the AI panel, and replace them with respondent numbers instead. Confirm your firm's policy on this class of data before uploading anything from a client engagement.
• Save a scenario-table prompt that works well. Network and staffing models tend to need the same three or four sensitivity ranges across engagements, so a reusable prompt beats rebuilding the request from scratch each time.
• For survey coding, give Gemini a fixed list of theme names rather than asking it to invent categories. A fixed list keeps the output consistent enough to cross-tabulate against other data later.

Tool interfaces change. If a button has moved, look for similar AI/magic/smart options in the same menu area.