How to Build an OKR Template Sheet in Google Sheets

Before touching a cell, nail the tab architecture. A well-designed OKR template separates inputs from calculations from outputs - the same principle that makes a financial model auditable.

Config is the master control panel. Every assumption that might change - the period, the scoring threshold, the weight scheme - lives here and nowhere else.

Set up the following named ranges in Config (Formulas > Named ranges):

This is where the model lives. KR_Detail has one row per key result, with columns for the objective it belongs to, the owner, the target, the actual, the weight within its objective, and the computed score.

Set up columns A through H:

The MIN(...,1) cap is intentional. A 112% NRR against a 100% target earns full weight credit but doesn't inflate the parent objective score above 1.0. If your team wants to reward stretch performance, change the cap to 1.2 and document it in Config.

With KR_Detail structured, the scoring formula in column H is straightforward. The objective-level score is where it gets interesting - it requires a SUMPRODUCT across a filtered range.

In Rollup!C2 (assuming A2 holds the Objective_ID), the objective score formula is:

=SUMPRODUCT(
  (KR_Detail!$A$2:$A$200=A2)*      // filter to this objective
  KR_Detail!$H$2:$H$200            // sum the weighted KR scores
)

This returns a number between 0 and 1. A score of 0.46 means the objective is running at 46% of target - below the 0.70 threshold you set in Config, so it shows red on Dashboard.

For a company-level score, weight the objectives themselves. Add an Obj_Weight column to Rollup and compute:

=SUMPRODUCT(Rollup!$B$2:$B$10, Rollup!$C$2:$C$10)

Where column B is objective weight and column C is the score above. If you have 4 objectives weighted 30/30/20/20, a 0.8 average score on the two 30% objectives and 0.5 on the 20% objectives gives you a company score of (0.8*0.3 + 0.8*0.3 + 0.5*0.2 + 0.5*0.2) = 0.68 - one point below on-track.

The Rollup tab does two things: aggregates by objective (Step 4) and slices by owner for performance conversations.

Owner summary in Rollup (columns E through G):

// Average weighted score for all KRs owned by a given person
=IFERROR(
  AVERAGEIF(KR_Detail!$C$2:$C$200, E2, KR_Detail!$H$2:$H$200),
  0
)

For period-over-period comparison, add a second KR_Detail tab named KR_Detail_Prior with the same structure. The delta formula in Rollup:

=SUMPRODUCT(
  (KR_Detail!$A$2:$A$200=A2)*KR_Detail!$H$2:$H$200
) -
SUMPRODUCT(
  (KR_Detail_Prior!$A$2:$A$200=A2)*KR_Detail_Prior!$H$2:$H$200
)

Positive delta means the objective improved quarter-over-quarter. This feeds directly into the board narrative without any manual calculation.

This is the step most OKR templates skip, and it's why OKR reviews and financial reviews happen in separate meetings. Wire them together.

If your financial model has an Assumptions tab with ARR targets, gross margin thresholds, and headcount, reference them directly in KR_Detail rather than hardcoding:

// KR_Detail E2: ARR target for "Grow ARR to $18.5M" key result
='Assumptions'!$B$12

// KR_Detail F2: ARR actual from the actuals tab
=SUMIFS('P&L'!$C:$C,'P&L'!$B:$B,">="&Assumptions!$B$3,'P&L'!$A:$A,"ARR")

Now when the CFO bumps the ARR target from $18.5M to $21M in Assumptions, the OKR score for that KR recalculates automatically. The 72% gross margin target and 70.1% actual that showed green last quarter shows yellow this quarter if the target moved up.

Targets worth linking directly from financial model assumptions:

The Dashboard tab is read-only output. Every cell pulls from Rollup or Config. No one types here.

Structure it in three zones:

Zone 1 - Header row (rows 1-3): Period label from =Config!B2, company score from Rollup composite, a RAG indicator using =IF(Rollup!composite>=cfg_ontrack_threshold,"ON TRACK","AT RISK").

Zone 2 - Objective summary table (rows 5-15): One row per objective, pulling from Rollup:

=IFERROR(
  VLOOKUP(A6, Rollup!$A:$C, 3, FALSE),
  "-"
)

Zone 3 - Owner heat map (rows 17-30): Owner names across columns, objectives down rows, cell values from Rollup owner slice. Color via conditional formatting: >=0.70 green, 0.50-0.69 yellow, <0.50 red.

For the quarterly board pack, this tab is the only one that gets shared. Lock it, hide the formula bar for board viewers using protected ranges, and name the file [CompanyName]_OKR_[Quarter]_BOARD.xlsx before exporting to PDF.