OKRs live and die by how easy they are to update. If checking in on your OKRs takes 20 minutes of spreadsheet work, people stop doing it. This guide shows you how to build an OKR tracker in Google Sheets that takes under 5 minutes to update each week and gives leadership a clear view of company progress at a glance.
The Structure
Two levels: Objectives (parent rows) and Key Results (child rows nested under each objective). Keep them in the same sheet — use indentation or a Type column (Objective vs. Key Result) to distinguish them.
Columns for each row: Type, Description, Owner, Target, Current Value, Progress %, Status (On Track, At Risk, Off Track), and Notes.
Progress % for a Key Result:
=IF(D2=0, 0, E2/D2)
Where D2 is target and E2 is current value. Format as percentage.
Objective Score
An objective's progress is the average of its key results' progress percentages. If the three KRs under Objective 1 are at 80%, 60%, and 40%, the objective score is 60%. Use AVERAGEIFS to calculate this automatically:
=AVERAGEIFS(F:F, B:B, A2, C:C, "Key Result")
Where F is progress %, B is the parent objective name, A2 is this objective's name, and C is the Type column.
RAG Status
Add conditional formatting to the Status column: green for On Track (progress > 70% of the period elapsed), amber for At Risk (50-70%), red for Off Track (below 50%).
Or calculate it automatically based on progress vs. time elapsed in the quarter:
=IF(F2>=(DAYS(DATE(2026,6,30),DATE(2026,4,1))-DAYS(DATE(2026,6,30),TODAY()))/DAYS(DATE(2026,6,30),DATE(2026,4,1))*0.7, "On Track", IF(F2>=0.5, "At Risk", "Off Track"))
Company Summary View
Add a summary section showing: total objectives, on track count, at risk count, off track count, and overall company OKR score (average of all objective scores). This is the view you share in your weekly leadership meeting.
The Easy Way: Using SheetXAI in Google Sheets
Example 1: You have your objectives and key results already listed.
"I have a list of OKRs on Sheet 1 with objectives, key results, owners, targets, and current values. Build an OKR tracker with automatic progress calculations, RAG status based on current progress vs. time elapsed in Q2, and a company summary showing overall score."
SheetXAI reads your data, calculates progress, applies the RAG logic, and builds the summary.
Example 2: Your OKRs are tracked in a project management tool.
"Pull our Q2 OKRs from Linear and build a tracker showing progress for each key result, objective scores, and a company-level summary with RAG status."
SheetXAI connects to Linear and builds the OKR tracker.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Project Status Report in Google Sheets, How to Build a KPI Dashboard in Google Sheets, and Google Sheets AI Guide.
