Use Gemini in Google Sheets to Build the Broker RFP Scoring Sheet

What This Does

Comparing three or four broker RFP responses usually means building a scoring spreadsheet from scratch each renewal cycle, with criteria as rows and a weighted formula nobody quite remembers how to write. Gemini in Google Sheets builds the table structure and the weighted-total formula from a plain description, so the team can start scoring instead of building.

Before You Start

• You have a Google account with access to Google Sheets
• Your organization's Google Workspace plan includes Gemini. This generally requires Business Standard or higher (starting at $14/user/month). Check the sparkle icon in the top right corner of a sheet, and if it's missing, confirm with your Workspace admin whether Gemini is enabled for your account. See workspace.google.com for current plan details
• You have the RFP evaluation criteria and weights decided before you start, even roughly

Steps

1. Find the AI feature

Open a new Google Sheet. Click the small table icon in the toolbar labeled Help me organize to build the sheet structure, or the sparkle "Ask Gemini" icon in the top right corner, next to Share, for a chat-style side panel.

2. Tell it what you need

In Help me organize, describe the table: "Create a broker RFP scoring sheet with columns for Broker Name, Criterion, Weight, Score, and Weighted Total." Gemini builds a formatted table with those headers. Then open the Ask Gemini side panel and describe the formula: "Create a formula that multiplies Score by Weight for each row and sums the Weighted Total by broker."

3. Review and use the result

Gemini proposes a formula and, if you ask, explains it in plain language. Click the cell where you want it, then Insert from the side panel to add it to the sheet. Test the formula with a couple of sample scores before sharing the sheet with the rest of the review team.

Real Example

Scenario: Three brokers responded to the medical and dental RFP, and the team needs a shared sheet where each reviewer scores service model, technology, fees and references, weighted differently by category.

What you type/do: You use Help me organize to build a table with Broker, Criterion, Weight, Score and Weighted Total columns, then ask Gemini: "Create a formula that calculates the weighted total for each broker and highlights the highest-scoring broker."

What you get: A working weighted-total formula plus a conditional highlight on the top score, ready to share with the review team as each person fills in their section.

Tips

• Ask for one thing at a time. A single clear request, like the formula prompt above, works better than asking Gemini to build the whole sheet, the formula and the formatting in one go.
• If a formula errors when you drop in real numbers, ask Gemini to explain the error and fix it in a follow-up prompt rather than rebuilding the formula from scratch.
• Keep reviewer notes limited to the broker's proposed service model and fees rather than pasting excerpts of your current group's claims experience that the broker used to quote. That data doesn't need to sit in a sheet the whole review team can edit.

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