Use Google Sheets AI to Build a Coaching Activity Tracker

What This Does

Google Sheets now includes a Gemini AI sidebar that can write formulas, create pivot tables, and analyze your data on demand. For instructional coaches, this means building a coaching activity tracker — with auto-tallies, color coding, and weekly summaries — without needing to know a single formula.

Before You Start

• You have a Google account (free)
• Google Sheets is open in your browser (sheets.google.com)
• You have a general idea of what you want to track (e.g., teacher names, observation dates, coaching cycle status)

Steps

1. Set Up Your Tracker Sheet

1. Go to sheets.google.com and click Blank spreadsheet
2. In row 1, type column headers that match what you track:

   • Column A: Teacher Name
   • Column B: Grade/Subject
   • Column C: Last Observation Date
   • Column D: Coaching Cycle Status (Active / Complete / Not Started)
   • Column E: Number of Observations This Year
   • Column F: Next Meeting Date
3. Enter a few rows of sample teacher data

What you should see: A simple table with your headers and a few rows of data.

2. Open the Gemini AI Sidebar

1. In the menu bar, click Extensions → look for Gemini or click the Ask Gemini button (sparkle icon in the top right of the sheet)
2. A sidebar panel opens on the right side of your screen
3. If you don't see Gemini, click Tools → Gemini in Sheets (requires a Google Workspace account or personal Google account with Gemini enabled)

What you should see: A chat-style sidebar where you can type questions about your spreadsheet.

3. Ask Gemini to Write a Formula

1. Click in cell E2 (Number of Observations)
2. In the Gemini sidebar, type: "Write a formula that counts how many times the teacher name in A2 appears in column A of a sheet named 'Observations Log'"
3. Gemini writes the formula — click Insert to add it to your cell
4. Alternatively, try: "Write a formula that shows a green checkmark if column D says 'Active' and a red X if it says 'Not Started'"

What you should see: A working formula in your selected cell. Troubleshooting: If Gemini isn't available in your account, use the regular formula bar and type =COUNTIF(A:A,A2) manually.

4. Ask for a Summary Analysis

1. Once your tracker has some data, click in an empty cell below your table
2. In the Gemini sidebar, type: "Summarize the coaching activity in this sheet — how many teachers are in each status, which teachers haven't been observed yet, and how many observations have been completed total"
3. Gemini reads your data and writes a plain-language summary you can paste into your weekly report

5. Add Conditional Formatting for Quick Visual Status

1. Select the Coaching Cycle Status column (D)
2. Click Format → Conditional formatting
3. Set: Format cells if text is exactly "Active" → fill color green; "Not Started" → fill color red
4. Now your tracker is color-coded at a glance

Real Example

Scenario: You're managing 15 teachers across two grade bands and need a quick Friday check-in on who you still need to observe before the end of the quarter.

What you do: Open your tracker → ask Gemini: "Which teachers in column A have a 'Not Started' status and a blank observation date? List them." → Gemini returns the list in 10 seconds.

What you get: An instant list of teachers to prioritize next week — no scrolling or filtering needed.

Tips

• Ask Gemini to "create a summary table at the bottom showing coaching cycle status counts" for an automatic dashboard view.
• You can ask Gemini to explain what a formula does — paste a complex formula from someone else's sheet and type "explain this formula in plain English."
• Save your tracker as a template at the start of each year — just clear the data rows and it's ready for the new cohort.

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