Use Gemini in Google Sheets to build an annual review tracker tab
For Paraplanners ·
What This Does
Every household has a review date, a document request, a meeting packet and a meeting. A tab that shows all of that at a glance keeps you from finding out on Monday that Friday's review has no packet. Gemini in Sheets can add the dropdowns, a days-until column and highlighting from plain-language prompts. This tab is also the one a weekly digest can read later.
Before You Start
- You are signed in to Google Sheets with your firm's Google Workspace account. Gemini in Sheets needs an eligible plan such as Business Standard, and your admin controls whether it is on. If you see no Ask Gemini button at the top right, ask your admin.
- The tab holds household codes and advisor initials only. No client names, account numbers, dates of birth or addresses. Check with your CCO that this sheet and Gemini are approved for even coded data.
- A blank spreadsheet with a tab named Reviews.
Steps
1. Type the headers
In row 1 of the Reviews tab, type these headers in columns A to G: Household, Advisor, Review Date, Docs Status, Packet Status, Meeting Status, Days Until.
2. Open Gemini
On your computer, open the sheet and click Ask Gemini at the top right. Google's help page for Gemini in Sheets says this opens the side panel where you enter a prompt. When you ask for an action that edits the sheet, Gemini shows an action preview card with an Apply button. After it finishes, an Undo button appears until you make further changes.
3. Ask for the dropdowns
Name the columns. Google's page notes that if you do not say where a dropdown goes, it lands in the rightmost empty column.
In the Reviews tab, create a dropdown in column D with the options Not requested, Requested, Partly received, All received. Create a dropdown in column E with the options Not started, Drafted, Advisor reviewed. Create a dropdown in column F with the options Scheduled, Held, Cancelled. Apply them to rows 2 to 200.
Read the action preview card. Then click Apply.
4. Ask for Days Until
In column G, rows 2 to 200, add a formula for days until the date in column C. If column C is blank, column G must be blank. Format column G as a plain number.
Gemini should produce something like =IF(C2="","",C2-TODAY()). Check the formula before you accept it. If it differs, compare what it does against what you asked.
5. Ask for the highlight
Add conditional formatting to rows 2 to 200 that highlights the whole row when column G is between 0 and 21. Leave blank values unhighlighted.
Google's page lists conditional formatting as an action Gemini can apply, with a Settings button for adjusting the rule afterward.
6. Test on answers you already know
Enter four test rows, labelled as tests so nobody mistakes them for real households:
- A review date 14 days in the past. Days Until should show -14 and the row should not be highlighted.
- A review date 10 days from today. Days Until should show 10 and the row should be highlighted.
- A household with no review date. Days Until should be blank and the row should not be highlighted.
- A fully blank row. Days Until should be blank.
If any result is wrong, do not use the tab yet. Fix the formula or rule with a follow-up prompt and test again. Then delete the test rows.
Real Example
Scenario: Your first real entry is Household 112, advisor initials JD (invented), review date three weeks out, Docs Status "Requested", Packet Status "Not started", Meeting Status "Scheduled".
What you get: Days Until reads 21 and the row is highlighted, because the rule includes 21. As documents arrive, you change Docs Status to "Partly received" and then "All received". The row stays highlighted until the meeting date has passed and is no longer in the range.
Tips
- Keep exactly these header names. A later digest automation reads them by name.
- Sort by Days Until only on a copy of the data, so you do not scramble rows by accident.
- Gemini's suggestions are not always right the first time. A test row costs a minute. A wrong highlight costs a missed review.
Tool interfaces change. If a button has moved, look for similar AI/magic/smart options in the same menu area. Source for the Gemini steps: support.google.com/docs/answer/14356410, read 2026-10-06.