Quick answer

Completion: =Progress/Goal formatted as percent. Remaining: =(Goal-Progress)/Goal formatted as percent. Guard with IF when Goal might be zero.

Formula

  • =A2/B2
  • =(B2-A2)/B2
  • =IF(B2>0,B2-A2,0) for amount left

Introduction

Analysts live in spreadsheets. Fundraising coordinators track weekly deposits. Sales ops stacks reps by row.

Spreadsheet calculations repeat the same percent-to-goal math without retyping on a calculator.

This guide covers layout, formulas, formatting, Google Sheets parity, and validation against the web tool.

For concept review, read How to Calculate Percent to Goal.

Sheet layout and safety

Column A: progress (current). Column B: goal (target). Column C: completion %. Column D: remaining %. Column E: amount left.

Row 1 holds headers. Row 2 onward holds goals. Freeze the header row for scrolling.

IFERROR or IF guards prevent #DIV/0! when goal is blank or zero during template setup.

Format C and D as Percentage with the decimal places your policy allows.

Keep dollars in Accounting format in A and B if needed; do not mix units down the column.

Example formulas (row 2)

  • C2: =IF(B2>0,A2/B2,"")
  • D2: =IF(B2>0,(B2-A2)/B2,"")
  • E2: =IF(B2>0,B2-A2,"")
  • Optional cap display: =IF(B2>0,MIN(A2/B2,1),"") for completion max 100%

Copy formulas down the column for all goals. Lock B2 references with $ only when you reference a single global goal cell.

Google Sheets uses the same syntax for these formulas.

Build a reusable sheet

  1. Create headers. Progress, Goal, Completion %, Remaining %, Amount Left.
  2. Enter first row of data. Real P and G from one KPI or savings goal.
  3. Type formulas in C2:E2. Use IF guards shown above.
  4. Format percents. Percent style; choose 0 or 1 decimal for reports.
  5. Fill down. Double-click fill handle or drag for all rows.
  6. Validate one row. Compare C2 and D2 to the calculator on the homepage for the same P and G.

Multi-goal quarterly sheet

Rows: Revenue, Leads, Customer renewals. Columns show each goal’s P and G weekly.

Completion column powers a chart; remaining column powers a risk table sorted descending.

When one goal changes mid-quarter, update B on that row and add a comment with the change date.

Amount left column sums to a simple “total gap dollars” only if units match; never sum percents across rows.