Use Gemini in Google Sheets to build a close and valuation task tracker

What This Does

Close and valuation seasons come with a long list of small deadlines: data pulls, reconciliations, exhibits, reviewer hand-offs. This guide builds a personal task tracker in Google Sheets with a status dropdown, a Days Left column and color for rows that are late or close to due. Gemini in Sheets proposes the dropdown, the formula and the formatting. You test each one on rows whose answer you already know before you trust it.

The tracker holds task codes, owner codes and dates only. It never holds results, figures or policyholder details.

Before You Start

• You have a Google Sheets spreadsheet open in a browser on a computer. Google's page says Gemini in Sheets works best with native Google Sheets files. For an Excel (.xlsx) file, choose File, then Save as Google Sheets first.
• Your plan includes it. Google's page says the feature requires an eligible Google Workspace or Google AI plan. The registry lists Business Standard ($14/user/month) as a Google Workspace starting point, but eligibility is Google's call, so check the Ask Gemini button and ask IT if it is missing.
• You know your company's AI policy for work files. This tracker needs no company figures. Use task codes such as "T-104", owner codes such as "Analyst A" and short cycle labels such as "Q1 close". Keep descriptions of the work, model results and any person's details out of the sheet. If your company has not approved Gemini for work files, build the tracker in a personal account with the same invented codes.
• Gemini's suggestions are proposals. Gemini makes no changes until you click Insert or Apply.

Steps

1. Set up the Tasks tab

Rename the first tab Tasks. Put these headers in row 1, in this order:

Format column D as a date. A later guide in this series, the weekly close and valuation digest, reads this same tab and adds a Summary tab to this same sheet. Keep the five input column names exactly as written.

2. Open Gemini

At the top right of the spreadsheet, click Ask Gemini. A side panel opens with suggested prompts and a prompt box at the bottom. Type your prompts in the box and press Enter.

3. Ask for the Status dropdown

Google's page gives a dropdown prompt example, so word yours the same way:

Create a dropdown in column E with options 'Not started', 'In progress', 'Sent for review', 'Done'.

Gemini generates an action preview card. Read it. Then click Apply. If the dropdown lands in the wrong place or misses an option, click Undo and rephrase. The page says new dropdowns land in the rightmost empty column when you do not give a location, so always name the column.

4. Ask for the Days Left formula

Prompt:

In column F, create a formula for row 2 that subtracts today's date from the Due Date in column D.
Leave it blank when Task in column A or Due Date in column D is blank.

Google's page says Gemini creates formulas from generic cell names or references, and lets you insert the formula into a cell you select with Insert. The result you want looks like this:

=IF(OR(A2="",D2=""),"",D2-TODAY())

If Gemini's version differs, compare the logic, not the wording. If column F shows dates instead of numbers, change its format to a plain number. Fill the formula down to row 200 or so.

5. Ask for the conditional formatting

Prompt:

Apply conditional formatting to rows 2 to 200 in columns A to F.
Color a row red when Days Left in column F is below 0 and Status is not Done.
Color a row yellow when Days Left is 0 to 7 and Status is not Done.

The page says Gemini shows an action preview card for actions like this, and a Settings button after applying lets you adjust the rule where configurable. If the rule is wrong, Undo is available until you make another change. You can also review the rules in the Format menu under conditional formatting.

6. Test each piece on rows whose answers you know

Enter these four test rows. Type the formula =TODAY()+3 in D2 and =TODAY()-2 in D3 and =TODAY()+2 in D4, so the answers hold on any day.

Check each cell against the expected column. If one differs, fix the rule or formula before you add real tasks. Then delete the test rows and replace the date formulas with real dates.

Real Example

Scenario: It is Monday 2027-02-22 and you are starting the Q1 close. You run the test rows above.

What you type: The prompts from steps 3 to 5, then the four test rows.

What you get: On that Monday, row 2 shows a due date of 2027-02-25 and Days Left 3 (25 minus 22). Row 3 shows 2027-02-20 and Days Left -2. Row 4 shows 2027-02-24 and Days Left 2, but no color because Status is Done. Row 5 shows nothing. Rows 2 and 3 are colored yellow and red. When all four match, you delete them and enter the real close tasks.

Tips

• Gemini's history disappears when you reload the page or reopen the sheet, per Google's page. Insert what you want to keep, or save your working prompts in a note.
• Keep the tracker to codes and dates. If a task needs detail, put the detail in your own notes or your team's tracker.
• The page lists "Fix" on formula errors: hover over a cell with an error message and click Fix to let Gemini review the formula.

Tool interfaces change. If a button has moved, look for similar Gemini or Ask Gemini options in the same menu area.